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:

sysdate + (1/24 * 3) |

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 |

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 |