Recipe8.5.Determining the Number of Seconds, Minutes, or Hours Between Two Dates

Recipe 8.5. Determining the Number of Seconds, Minutes, or Hours Between Two Dates

Problem

You want to return the difference in seconds between two dates. For example, you want to return the difference between the HIREDATEs of ALLEN and WARD in seconds, minutes, and hours.

Solution

If you can find the number of days between two dates, you can find seconds, minutes, and hours as they are the units of time that make up a day.

DB2

Use the function DAYS to find the difference between ALLEN_HD and WARD_HD in days. Then multiply to find each unit of time:

  1 select dy*24 hr, dy*24*60 min, dy*24*60*60 sec  2   from (  3 select ( days(max(case when ename = 'WARD'  4                   then hiredate  5              end)) -  6          days(max(case when ename = 'ALLEN'  7                   then hiredate  8              end))  9        ) as dy 10   from emp 11        ) x 

MySQL and SQL Server

Use the DATEDIFF function to return the number of days between ALLEN_HD and WARD_HD. Then multiply to find each unit of time:

  1 select datediff(day,allen_hd,ward_hd)*24 hr,  2        datediff(day,allen_hd,ward_hd)*24*60 min,  3        datediff(day,allen_hd,ward_hd)*24*60*60 sec  4   from (  5 select max(case when ename = 'WARD'  6                  then hiredate  7            end) as ward_hd,  8        max(case when ename = 'ALLEN'  9                 then hiredate 10            end) as allen_hd 11   from emp 12        ) x 

Oracle and PostgreSQL

Use subtraction to return the number of days between ALLEN_HD and WARD_ HD. Then multiply to find each unit of time:

  1 select dy*24 as hr, dy*24*60 as min, dy*24*60*60 as sec  2   from (  3 select (max(case when ename = 'WARD'  4                 then hiredate  5            end) -  6        max(case when ename = 'ALLEN'  7                 then hiredate  8            end)) as dy  9   from emp 10        ) x 

Discussion

Inline view X for all solutions returns the HIREDATEs for WARD and ALLEN, as can be seen below:

  select max(case when ename = 'WARD'                  then hiredate            end) as ward_hd,        max(case when ename = 'ALLEN'                 then hiredate            end) as allen_hd   from emp WARD_HD     ALLEN_HD ----------- ----------- 22-FEB-1981 20-FEB-1981 

Multiply the number of days between WARD_HD and ALLEN_HD by 24 (hours in a day), 1440 (minutes in a day), and 86400 (seconds in a day).