Calculate difference between 2 date / times in Oracle SQL

by

You can substract dates in Oracle. This will give you the difference in days. Multiply by 24 to get hours, and so on.

SQL> select oldest - creation from my_table;

If your date is stored as character data, you have to convert it to a date type first.

SQL> select 24 * (to_date('2009-07-07 22:00', 'YYYY-MM-DD hh24:mi') 
             - to_date('2009-07-07 19:30', 'YYYY-MM-DD hh24:mi')) diff_hours 
       from dual;

DIFF_HOURS
----------
       2.5

Note:

This answer applies to dates represented by the Oracle data type DATE.
Oracle also has a data type TIMESTAMP, which can also represent a date (with time). If you subtract TIMESTAMP values, you get an INTERVAL; to extract numeric values, use the EXTRACT function.

More Related Contents:

  1. Oracle: how to add minutes to a timestamp?
  2. Get the number of days between two dates in Oracle, inclusive of the dates
  3. How to uninstall / completely remove Oracle 11g (client)?
  4. How do I escape a reserved word in Oracle?
  5. Using date in a check constraint, Oracle
  6. How to export query result to csv in Oracle SQL Developer?
  7. No more data to read from socket error
  8. Oracle JDBC ojdbc6 Jar as a Maven Dependency
  9. ORA-01882: timezone region not found
  10. how to export data from log table to email body in oracle
  11. Oracle: Import CSV file
  12. PL/SQL – Use “List” Variable in Where In Clause
  13. How do I calculate tables size in Oracle
  14. What is the effect of placing the commit after DML in procedure?
  15. Convert timestamp datatype into unix timestamp Oracle
  16. Oracle client ORA-12541: TNS:no listener [closed]
  17. Auto-increment in Oracle without using a trigger
  18. How to send email using Oracle 10 g Forms
  19. How to get primary key column in Oracle?
  20. How to create a user in Oracle 11g and grant permissions
  21. Trigger to enforce M-M relationship
  22. How do you get nicely formatted results from an Oracle procedure that returns a reference cursor?
  23. Oracle: how to INSERT if a row doesn’t exist
  24. Valid Date Checks in Oracle
  25. Functions vs procedures in Oracle
  26. Oracle: difference between max(id)+1 and sequence.nextval
  27. How to see refcursor result/output in Oracle SQL Developer? [duplicate]
  28. HikariCP: What database level timeouts should be considered to set maxLifetime for Oracle 11g
  29. How to generate a version 4 (random) UUID on Oracle?
  30. Find out the history of SQL queries

Leave a Comment