Prefer Oracle Native Date Arithmetic over ANSI INTERVAL

While coding PL/SQL APIs or during code reviews reviews I like to stick with Oracle Native Date Arithmetic over INTERVAL to keep my code simple and some times Bug free. Wait I said Bug !!! Some developers may not agree, but I think using INTERVAL leads code to another level of complexity which may add some potential bug in your code.

Before going to execute SQLs let me set NLS_DATE_FORMAT, so that I do not need to use TO_CHAR to format dates every time.

SQL> alter session set NLS_DATE_FORMAT = 'dd-Mon-yyyy HH24:mi:ss';
Session altered.

Lets check some basic examples with both Oracle Native Date Arithmetic over ANSI Standards.

1. Add One Second

SQL> select to_date('01-Jan-2016 00:00:00','dd-Mon-yyyy hh24:mi:ss') + 1/(24*60*60) from dual;
TO_DATE('01-JAN-2016
--------------------
01-Jan-2016 00:00:01

SQL> select to_date('01-Jan-2016 00:00:00','dd-Mon-yyyy hh24:mi:ss') + INTERVAL '1' SECOND from dual;
TO_DATE('01-JAN-2016
--------------------
01-Jan-2016 00:00:01

2. Add One Minute

SQL> select to_date('01-Jan-2016 00:00:00','dd-Mon-yyyy hh24:mi:ss') + 1/(24*60) from dual;
TO_DATE('01-JAN-2016
--------------------
01-Jan-2016 00:01:00

SQL> select to_date('01-Jan-2016 00:00:00','dd-Mon-yyyy hh24:mi:ss') + INTERVAL '1' MINUTE from dual;
TO_DATE('01-JAN-2016
--------------------
01-Jan-2016 00:01:00

3. Add One Hour

SQL> select to_date('01-Jan-2016 00:00:00','dd-Mon-yyyy hh24:mi:ss') + 1/24 from dual;
TO_DATE('01-JAN-2016
--------------------
01-Jan-2016 01:00:00

SQL> select to_date('01-Jan-2016 00:00:00','dd-Mon-yyyy hh24:mi:ss') + INTERVAL '1' HOUR from dual;
TO_DATE('01-JAN-2016
--------------------
01-Jan-2016 01:00:00

4. Add One Day

SQL> select to_date('01-Jan-2016 00:00:00','dd-Mon-yyyy hh24:mi:ss') + 1 from dual;
TO_DATE('01-JAN-2016
--------------------
02-Jan-2016 00:00:00

SQL> select to_date('01-Jan-2016 00:00:00','dd-Mon-yyyy hh24:mi:ss') + INTERVAL '1' DAY from dual;
TO_DATE('01-JAN-2016
--------------------
02-Jan-2016 00:00:00

5. Add One Month

SQL> select add_months(to_date('01-Jan-2016 00:00:00','dd-Mon-yyyy hh24:mi:ss'),1) from dual;
ADD_MONTHS(TO_DATE('
--------------------
01-Feb-2016 00:00:00

SQL> select to_date('01-Jan-2016 00:00:00','dd-Mon-yyyy hh24:mi:ss') + INTERVAL '1' MONTH from dual;
TO_DATE('01-JAN-2016
--------------------
01-Feb-2016 00:00:00

6. Add One Year

SQL> select add_months(to_date('01-Jan-2016 00:00:00','dd-Mon-yyyy hh24:mi:ss'),12) from dual;
ADD_MONTHS(TO_DATE('
--------------------
01-Jan-2017 00:00:00

SQL> select to_date('01-Jan-2016 00:00:00','dd-Mon-yyyy hh24:mi:ss') + INTERVAL '1' YEAR from dual;
TO_DATE('01-JAN-2016
--------------------
01-Jan-2017 00:00:00

So till now INTERVAL looks great and even add readability, so where is the problem? The problem is when we do date arithmetic with INTERVAL it only modify the date value with component specified (MONTH|YEAR|DAY|MINUTE|SECOND|HOUR), and does not care about validity of date. Date arithmetic with INTERVAL may lead us to either exception or not-desired results as in following examples

1) Want to move back/forward one month from 30-APR-2016

SQL> select add_months(to_date('30-APR-2016 00:00:00','dd-Mon-yyyy hh24:mi:ss'),-1) from dual;
ADD_MONTHS(TO_DATE('
--------------------
31-Mar-2016 00:00:00

SQL> select to_date('30-APR-2016 00:00:00','dd-Mon-yyyy hh24:mi:ss') - INTERVAL '1' MONTH from dual;
TO_DATE('30-APR-2016
--------------------
30-Mar-2016 00:00:00

SQL> select add_months(to_date('30-APR-2016 00:00:00','dd-Mon-yyyy hh24:mi:ss'),1) from dual;
ADD_MONTHS(TO_DATE('
--------------------
31-May-2016 00:00:00

SQL> select to_date('30-APR-2016 00:00:00','dd-Mon-yyyy hh24:mi:ss') + INTERVAL '1' MONTH from dual;
TO_DATE('30-APR-2016
--------------------
30-May-2016 00:00:00

Oh, INTERVAL just played with MONTH but did not adjust DATE part accordingly. This could be a pain. Lets check another examples

2) Want to move back/forward one month from 31-MAY-2016

SQL> select add_months(to_date('31-MAR-2016 00:00:00','dd-Mon-yyyy hh24:mi:ss'),1) from dual;
ADD_MONTHS(TO_DATE('
--------------------
30-Apr-2016 00:00:00

SQL> select to_date('31-MAR-2016 00:00:00','dd-Mon-yyyy hh24:mi:ss') - INTERVAL '1' MONTH from dual;
select to_date('31-MAR-2016 00:00:00','dd-Mon-yyyy hh24:mi:ss') - INTERVAL '1' MONTH from dual
                                                                *
ERROR at line 1:
ORA-01839: date not valid for month specified

SQL> select add_months(to_date('31-MAR-2016 00:00:00','dd-Mon-yyyy hh24:mi:ss'),-1) from dual;
ADD_MONTHS(TO_DATE('
--------------------
29-Feb-2016 00:00:00

SQL> select to_date('31-MAR-2016 00:00:00','dd-Mon-yyyy hh24:mi:ss') + INTERVAL '1' MONTH from dual;
select to_date('31-MAR-2016 00:00:00','dd-Mon-yyyy hh24:mi:ss') + INTERVAL '1' MONTH from dual
                                                                *
ERROR at line 1:
ORA-01839: date not valid for month specified

OH NO!!! Date Arithmetic with INTERVAL raised exception, because INTERVAL only modified MONTH, forgot to adjust date part and tried to return 31-FEB-2016/31-APR-2016 which are not valid date. Sad, My Application might be broken. Same can happen if we try to do arithmetic on leap year.

3) Want to move back/forward one year from 29-FEB-2016

SQL> select add_months(to_date('29-FEB-2016 00:00:00','dd-Mon-yyyy hh24:mi:ss'),12) from dual;
ADD_MONTHS(TO_DATE('
--------------------
28-Feb-2017 00:00:00

SQL> select to_date('29-FEB-2016 00:00:00','dd-Mon-yyyy hh24:mi:ss') + INTERVAL '12' MONTH from dual;
select to_date('29-FEB-2016 00:00:00','dd-Mon-yyyy hh24:mi:ss') + INTERVAL '12' MONTH from dual
                                                                *
ERROR at line 1:
ORA-01839: date not valid for month specified

SQL> select add_months(to_date('29-FEB-2016 00:00:00','dd-Mon-yyyy hh24:mi:ss'),-12) from dual;
ADD_MONTHS(TO_DATE('
--------------------
28-Feb-2015 00:00:00

SQL> select to_date('29-FEB-2016 00:00:00','dd-Mon-yyyy hh24:mi:ss') - INTERVAL '12' MONTH from dual;
select to_date('29-FEB-2016 00:00:00','dd-Mon-yyyy hh24:mi:ss') - INTERVAL '12' MONTH from dual
                                                                *
ERROR at line 1:
ORA-01839: date not valid for month specified

29-FEB-2015/29-FEB-2017 are not valid dates, so we have to face the exception. 

It is disappointing to find out that INTERVAL data type does not handle dates properly when doing arithmetic but it is expected as per the Oracle Documentation. According to the ANSI Standard of INTERVAL arithmetic, the result of any datetime/interval arithmetic had to be in the range of permissible values and this is unlikely to change in Oracle unless the ANSI standard does.

So please do consider all these pain points while using INTERVAL in your application, or simply avoid using INTERVAL as I do. Hope you have enjoyed this article on "Date Arithmetic with INTERVAL". Feedback are always welcome :)



Related Posts:
- Playing With Truncate and Date
- Oracle: Some Important Date Queries
- Convert seconds to time - hh:mi:ss
- Oracle: convert time hh:mi:ss to seconds
- ORA-01830 date format picture ends before converting entire input string