How To Use INTERVAL in Oracle

How To Use INTERVAL in Oracle

October 15, 2019
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

dba oracle dba database management system oracle performance tuning oracle interval oracle interval in oracle to_yminterval oracle to_dsinterval oracle the stock market plumbing database programming database financial advisor personal financial

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;

dba oracle dba database management system oracle performance tuning oracle interval oracle interval in oracle to_yminterval oracle to_dsinterval oracle the stock market plumbing database programming database financial advisor personal financial

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;

dba oracle dba database management system oracle performance tuning oracle interval oracle interval in oracle to_yminterval oracle to_dsinterval oracle the stock market plumbing database programming database financial advisor personal financial

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;

dba oracle dba database management system oracle performance tuning oracle interval oracle interval in oracle to_yminterval oracle to_dsinterval oracle the stock market plumbing database programming database financial advisor personal financial

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;

dba oracle dba database management system oracle performance tuning oracle interval oracle interval in oracle to_yminterval oracle to_dsinterval oracle the stock market plumbing database programming database financial advisor personal financial

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;

dba oracle dba database management system oracle performance tuning oracle interval oracle interval in oracle to_yminterval oracle to_dsinterval oracle the stock market plumbing database programming database financial advisor personal financial

See Also:
Oracle Date Format Oracle Date Difference Oracle Home

Leave a Reply

Your email address will not be published. Required fields are marked *

*