Adding or subtracting time from Oracle dates is not always obvious and not always readable. Adding whole days is straightforward – 1 = one day – or with the use of the add_months function, adding months is straightforward, but if, for example you want to add 3 hours to sysdate you would have to do something like the following:

Of course that could be written differently – using constants perhaps – but for the purposes of this example i’ve kept it simple.

Luckily interval can be used to produce much more readable code. The example above could be rewritten like so:

sysdate + INTERVAL '3' HOUR |

sysdate + interval '3' hour

Note the use of singular “hour”.

Here are a few other uses for interval, including adding seconds and minutes to a date:

SELECT sysdate + INTERVAL '3' SECOND,
sysdate + INTERVAL '3' MINUTE,
sysdate + INTERVAL '3' HOUR,
sysdate + INTERVAL '3' DAY,
sysdate + INTERVAL '3' MONTH,
sysdate + INTERVAL '3' YEAR FROM dual |

select sysdate + interval '3' second,
sysdate + interval '3' minute,
sysdate + interval '3' hour,
sysdate + interval '3' day,
sysdate + interval '3' month,
sysdate + interval '3' year from dual