Oracle Database Working with Dates Date Arithmetic
Example
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;
