Oracle interval – adding seconds, minutes and hours to dates

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

One thought on “Oracle interval – adding seconds, minutes and hours to dates”

  1. Great! Tried it and it works, with timestamp as well (and probably other date formats too). The question is if it is really more readable, + interval ‘1’ second VS + 1/86400

Leave a Reply