Using INTERVAL DAY TO SECOND Data Type In Oracle

Oracle has INTERVAL data types to store days or years. Sometimes it helps you to do less work. I demonstrate a simple example to show the usage.

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL>
SQL> DECLARE
  2    diff INTERVAL DAY TO SECOND;
  3 
  4    d1 DATE := SYSDATE;
  5    d2 DATE := SYSDATE +
  6               (6 + 1 / 24 * 3 + 1 / 24 / 60 * 7 + 1 / 24 / 60 / 60 * 9);
  7 
  8    t1 TIMESTAMP;
  9    t2 TIMESTAMP;
 10  BEGIN
 11    t1 := to_timestamp(d1);
 12    t2 := to_timestamp(d2);
 13 
 14    diff := t2 - t1;
 15 
 16    dbms_output.put_line('Time 2 is ' || to_char(t2));
 17    dbms_output.put_line('Time 1 is ' || to_char(t1));
 18 
 19    dbms_output.put_line('Total time difference is ' || diff);
 20 
 21    dbms_output.put_line('Details:');
 22    dbms_output.put_line('Day is    ' || extract(DAY    FROM diff));
 23    dbms_output.put_line('Hour  is  ' || extract(HOUR   FROM diff));
 24    dbms_output.put_line('Minute is ' || extract(MINUTE FROM diff));
 25    dbms_output.put_line('Second is ' || extract(SECOND FROM diff));
 26  END;
 27  /
Time 2 is 29/09/2006 23:50:39,000000
Time 1 is 23/09/2006 20:43:30,000000
Total time difference is +06 03:07:09.000000
Details:
Day is    6
Hour  is  3
Minute is 7
Second is 9
PL/SQL procedure successfully completed
SQL>