This online help page is based on examples to make it easier to follow. The Oracle interval works with Oracle date, Oracle timestamp etc and the purpose is make date and time calculations more easier to write and read. The following two examples are describing how to achieve the same result using Oracle date and multiplying the amount of minutes and seconds. And the next example is with using Oracle Interval and getting the same result as on the first SQL query.
SELECT sysdate, sysdate * 10 * 60 AS sysdate_plus_10min FROM dual;
In the SQL query above we are using Oracle Sysdate multiplied with 600 seconds which is 10 times 60 seconds. The next example will get the same result using Oracle Interval function.
SELECT sysdate, sysdate + INTERVAL '10' MINUTE AS sysdate_plus_10min FROM dual;
As you see on the last query that using function Interval in Oracle makes your code much clear and more easier to read. The following example is about the most common parameters 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;
The same Oracle Interval function can be used with minus (“-“) too to remove time or dates. The function above is handy when you need to add or remove only one type of units like only years, minutes or seconds but when you would like to add 1 year and 2 months then see our following examples.
SELECT sysdate, sysdate + TO_YMINTERVAL('1-2') AS year_and_month FROM dual;
The Oracle TO_YMINTERVAL function can be used to add years and months. As the last example above you also can take off years and month with just adding a minus in front of the value as in the following Oracle TO_YMINTERVAL example.
SELECT sysdate, sysdate + TO_YMINTERVAL('-1-2') AS year_and_month FROM dual;
The next example is about how to add days, minutes and seconds together in the same function. In that case you would need to use another Oracle Interval function named Oracle TO_DSINTERVAL. See below the next example to understand how to use it.
SELECT sysdate, sysdate + TO_DSINTERVAL('1 02:03:04') AS day_to_sec FROM dual;
As in the previous examples to remove the days, minutes and seconds you need to use the negative value as on the example below.
SELECT sysdate, sysdate + TO_DSINTERVAL('-1 02:03:04') AS day_to_sec FROM dual;