5.8 Reporting Durations5.8.1 ProblemYou have durations stored in terms of seconds, and you want to report those durations in a human-readable form. For example, you want to report a value such as 12345678 seconds in terms of days, hours, minutes, and seconds. 5.8.2 SolutionUse a little bit of math and some string formatting to report the result: SELECT 12345678/86400/30 months, (12345678/86400)%30 days, (1234567%86400)/3600 hours, (12345678%3600)/60 minutes, 12345678%60 seconds months days hours minutes seconds ----------- ----------- ----------- ----------- ----------- 4 22 6 21 18 5.8.3 DiscussionObviously, this is a very simple mathematical problem. To calculate hours from seconds, for example, you only need to divide the number of seconds by 3600 (the number of seconds in one hour ). If you need a cascading report, such as the one shown in our solution, you first divide the number of seconds by the number of seconds in a month. Then, you take the remainder of that value and divide by the number of seconds in a day to get the number of days. Continue the process until you work your way down to seconds. The following is a walkthrough of our solution query:
|