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

8 comments:

  1. Very useful info.. thanks A lot Nimish

    ReplyDelete
  2. Excellent writeup.

    I have to gently disagree with you on calling this a "bug" however. Any behaviour that conforms to the documentation is not a bug, by definition. Neither is an exception evidence of a defect.

    A "bug" for you might be "expected and desired" behaviour for someone else (e.g. someone might consider it a bug that ADD_MONTHS(DATE'2000-04-30',1) results in 31 May rather than 30 May!). It just depends on the business requirements.

    Certainly, if you use INTERVAL arithmetic, you have to add an exception handler to handle this case.

    Thanks

    ReplyDelete
    Replies
    1. Thanks for your comment and explanation.

      I never said INTERVAL functionality has bug. What I am trying to say is, if INTERVAL is not used properly without carefully reading docs, as there is a difference between INTERVAL and Oracle Native Date Arithmetic, it may lead your code to have bug

      Delete
  3. Very interesting. One would've thought that Oracle would use a consistent definition of what constitutes a "month".

    ReplyDelete
    Replies
    1. Interval is basically ANSI standard, so Oracle has to behave as per standard until ANSI itself changes it.

      Delete
  4. Thank you! Very interesting. Have idea to review code, tomorrow.

    ReplyDelete
  5. Very useful, thanks. Even if it could not be considered as a bug, these INTERVAL operator are not usable for me.

    ReplyDelete
  6. thanks very much for a very important topic, i really appreciate your efforts. best regards.

    ReplyDelete