Oracle Working with Dates
Date Arithmetic
Oracle supports DATE (includes time to the nearest second) and TIMESTAMP (includes time to fractions of a second) datatypes, which allow arithmetic (addition and subtraction) natively. For example:
To get the next day:
SELECT TO_CHAR(SYSDATE + 1, 'YYYY-MM-DD') AS tomorrow FROM dual;To get the previous day:
SELECT TO_CHAR(SYSDATE - 1, 'YYYY-MM-DD') AS yesterday FROM dual;To add 5 days to the current date:
SELECT TO_CHAR(SYSDATE + 5, 'YYYY-MM-DD') AS five_days_from_now FROM dual;To add 5 hours to the current date:
SELECT TO_CHAR(SYSDATE + (5/24), 'YYYY-MM-DD HH24:MI:SS') AS five_hours_from_now FROM dual;To add 10 minutes to the current date:
SELECT TO_CHAR(SYSDATE + (10/1440), 'YYYY-MM-DD HH24:MI:SS') AS ten_mintues_from_now FROM dual;To add 7 seconds to the current date:
SELECT TO_CHAR(SYSDATE + (7/86400), 'YYYY-MM-DD HH24:MI:SS') AS seven_seconds_from_now FROM dual;To select rows where hire_date is 30 days ago or more:
SELECT * FROM emp WHERE hire_date < SYSDATE - 30;To select rows where last_updated column is in the last hour:
SELECT * FROM logfile WHERE last_updated >= SYSDATE - (1/24);Oracle also provides the built-in datatype INTERVAL which represents a duration of time (e.g. 1.5 days, 36 hours, 2 months, etc.). These can also be used with arithmetic with DATE and TIMESTAMP expressions. For example:
SELECT * FROM logfile WHERE last_updated >= SYSDATE - INTERVAL '1' HOUR;