5.8 Reporting Durations

5.8.1 Problem

You 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 Solution

Use 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 Discussion

Obviously, 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:

12345678/86400/30 months

Divides the duration by the number of seconds in a day (86400) to get days and divides that result by 30 to get a value for months. Since we don't know exactly which months we are talking about, we use an arbitrary 30-day month.

(12345678/86400)%30 days

Divides the duration by the number of seconds in a day to get the number of days and then uses the remainder operator (%) to get the number of days left over after dividing by 30 to get months.

(1234567%86400)/3600 hours

Uses the remainder operator to determine the number of seconds left over after dividing by days and divides that value by the number of seconds in an hour (3600) to get hours.

(12345678%3600)/60 minutes

Uses the remainder operator to determine the number of seconds left over after dividing by hours and divides that value by 60 to get minutes.

12345678%60 seconds

Computes seconds remaining after removing all the full minutes from the input value.

Calculating the number of months in a duration can be tricky, since you really need dates to make such a calculation accurate. In our solution, we assume that each month has 30 days.