Difference between two dates in hours

Oracle stores dates as a real number counting the number of days that have elapsed since some day thousands of years ago. When you subtract two dates, you are left with a real number showing the number of days that have elapsed between the two dates. To illustrate how subtracting two dates works, I'll generate a small test table and populate it with some sample data:

SQL> create table test (
  2  begindate date,
  3  enddate date);

Table created.

SQL> insert into test values (
  2  to_date('01/01/06 12:00','MM/DD/YY HH24:MI'),
  3  to_date('01/02/06 12:00','MM/DD/YY HH24:MI'));

1 row created.

SQL> insert into test values (
  2  to_date('01/01/06 12:00','MM/DD/YY HH24:MI'),
  3  to_date('01/01/06 12:30','MM/DD/YY HH24:MI'));

1 row created.

SQL> insert into test values (
  2  to_date('01/01/06 12:00','MM/DD/YY HH24:MI'),
  3  to_date('01/03/06 17:45','MM/DD/YY HH24:MI'));

1 row created.

SQL> commit;

Commit complete.

I will now query the two columns and show the difference between the two:

SQL> column diff format 9.9999
SQL> select begindate,enddate,enddate-begindate as diff
  2  from test;

BEGINDATE ENDDATE      DIFF
--------- --------- -------
01-JAN-06 02-JAN-06  1.0000
01-JAN-06 01-JAN-06   .0208
01-JAN-06 03-JAN-06  2.2396

Notice that the first row has a difference of 1.0 days. This is because the two dates are exactly 24 hours apart. The second row is only 30 minutes apart, so the difference is 0.0208 days. Taking out our calculator, we see that 30 minutes equals 1day/24hours*0.5 = 0.208333, so the value is correct!

So now that we know how subtracting dates works, we can perform some simple math to determine the number of hours between two dates. Since there are 24 hours in one day, simply multiply the difference by 24 as can be seen below:

SQL> select begindate,enddate,enddate-begindate as diff,
  2  (enddate-begindate)*24 as hours from test;

BEGINDATE ENDDATE      DIFF   HOURS
--------- --------- ------- -------
01-JAN-06 02-JAN-06  1.0000   24.00
01-JAN-06 01-JAN-06   .0208     .50
01-JAN-06 03-JAN-06  2.2396   53.75

Using similar mathematical operations, you can determine the number of hours and number of seconds.

Reader feedback:

Steve P. writes:

I saw this posting and thought I would pass on the DB2 equivalent.

The answer is ALWAYS a numeric in this format: YYYYMMDDHHMMSS.micros

  1 DAY DIFFERENCE                       
  SELECT     (( CURRENT TIMESTAMP )     
      -  (CURRENT TIMESTAMP - 1 DAY )) 
   FROM  SYSIBM.SYSDUMMY1;              

    1000000.000000                                    

  2 DAYS, 3 HOURS, 45 MINUTES MINUTES DIFFERENCE                   
  SELECT     (( CURRENT TIMESTAMP - 2 DAYS - 3 HOURS - 45 MINUTES)
         -  (CURRENT TIMESTAMP ))                                   
   FROM  SYSIBM.SYSDUMMY1;                                        

    -2034500.000000             

  1000 YEAR DIFFERENCE                           
   SELECT     (( CURRENT TIMESTAMP - 1000 YEARS )
       -  (CURRENT TIMESTAMP ))                 
    FROM  SYSIBM.SYSDUMMY1;                      

   -10000000000000.000000