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