Online Tech Support

This tutorial is based on examples to be easier to follow. The Oracle INTERVAL is used with date, timestamp etc and it helps to write more simpler SQL statements. The following two examples are describing how to achieve the same result using Oracle date versus interval.

The first example is using Oracle SYSDATE and multiply it with 600 seconds which is 10 times 60 seconds. The output shows current time plus 10 minutes.

SELECT sysdate, sysdate * 10 * 60 AS sysdate_plus_10min
FROM dual;

The second example will do the same calculation as the last one only with using Oracle INTERVAL.

SELECT sysdate, sysdate + INTERVAL '10' MINUTE AS sysdate_plus_10min
FROM dual;

The last SQL query above is showing how the Interval function makes your code more clear and easier to read as the 10 MINUTE can be read directly from the query.

The next example is about different options that can be used with Oracle Interval.

SELECT sysdate,
       sysdate + INTERVAL '1' second AS interval_sec,
       sysdate + INTERVAL '2' minute AS interval_min,
       sysdate + INTERVAL '3' hour AS interval_hour,
       sysdate + INTERVAL '4' DAY AS interval_day,
       sysdate + INTERVAL '5' MONTH AS interval_month,
       sysdate + INTERVAL '6' YEAR AS interval_year
FROM dual;

Keep in mind that the Oracle Interval functions can also be used with minus (“-“) too to reduce some time or days.

Now the last functions above were easy to use if you need to add only one type of units like years, but when you would need to add 1 year and 2 months then the following example can help you with it. This example is based on a new function TO_YMINTERVAL.

SELECT sysdate,
       sysdate + TO_YMINTERVAL('1-2') AS year_and_month
FROM dual;

This Oracle TO_YMINTERVAL function is used to add years and months but it can also take off the years and month by adding a minus in front of the parameters as we are done on the following example.

SELECT sysdate,
       sysdate + TO_YMINTERVAL('-1-2') AS year_and_month
FROM dual;

The next examples below are about adding days, minutes and seconds and to do that we are using another Oracle interval function named Oracle TO_DSINTERVAL.

SELECT sysdate,
       sysdate + TO_DSINTERVAL('1 02:03:04') AS day_to_sec
FROM dual;

And to remove the days, minutes or seconds take a look at the last example below.

SELECT sysdate,
       sysdate + TO_DSINTERVAL('-1 02:03:04') AS day_to_sec
FROM dual;

See Also:
Home Oracle Date Difference