INTERVAL MONTH TO DAY

I’ve already noted a couple of aspects about interval data types previously. In the examples used I have used the INTERVAL DAY TO SECOND data type. The other type of interval is INTERVAL YEAR TO MONTH.

Oracle handles intervals with TIMESTAMPs is a very natural way; adding or subtracting an INTERVAL to a TIMESTAMP results in a TIMESTAMP that has been incremented (or decremented) by the value of the interval. (You can read about the allowable operations in the Datetime/Interval Arithmetic section of the Oracle documentation).

So, if we were to run the following script:

DECLARE
   l_old_timestamp TIMESTAMP := TO_TIMESTAMP('05/01/2012 8:00','dd/mm/yyyy hh24:mi');
   l_new_timestamp TIMESTAMP;
   l_interval      INTERVAL DAY TO SECOND := INTERVAL '1' DAY;
BEGIN
   l_new_timestamp := l_old_timestamp + l_interval;
   dbms_output.put_line('New timestamp: ' || TO_CHAR(l_new_timestamp,'dd/mm/yyyy hh24:mi'));
END;   

we’d get:

New timestamp: 06/01/2012 08:00

What could be more natural!

Again though, you notice that I used the data type INTERVAL DAY TO SECOND. I could just as easily have run:

DECLARE
   l_old_timestamp TIMESTAMP := TO_TIMESTAMP('05/01/2012 8:00','dd/mm/yyyy hh24:mi');
   l_new_timestamp TIMESTAMP;
   l_interval      INTERVAL YEAR TO MONTH := INTERVAL '1' MONTH;
BEGIN
   l_new_timestamp := l_old_timestamp + l_interval;
   dbms_output.put_line('New timestamp: ' || TO_CHAR(l_new_timestamp,'dd/mm/yyyy hh24:mi'));
END;   

and got an answer of:

New timestamp: 05/02/2012 08:00

but I didn’t. The reason is that as a general rule I shun the INTERVAL YEAR TO MONTH data type. The reason is quite simple. If I were to modify the example above and run:

DECLARE
   l_old_timestamp TIMESTAMP := TO_TIMESTAMP('30/01/2012 8:00','dd/mm/yyyy hh24:mi');
   l_new_timestamp TIMESTAMP;
   l_interval      INTERVAL YEAR TO MONTH := INTERVAL '1' MONTH;
BEGIN
   l_new_timestamp := l_old_timestamp + l_interval;
   dbms_output.put_line('New timestamp: ' || TO_CHAR(l_new_timestamp,'dd/mm/yyyy hh24:mi'));
END;   

I would get:

ERROR at line 1:
ORA-01839: date not valid for month specified
ORA-06512: at line 6

So, adding months to a timestamp that results in a timestamp that exceeds the number of days in the resulting month generates an error. This might seem quite obvious to many people and if you’ve ever been bitten by the logic of the ADD_MONTHS function and the way it handles dates on the last day of the month you may even welcome this handling. However, the problem I have is that if I used something like this in my code then I would have to provide a work around for when the error occurs… and if I were to have to do that then I might as well write the entire “month adding” logic myself and skip the use of the INTERVAL data type altogether.

Worse yet, if we were to perform the calculation inside an SQL statement then we would also receive the same error. So now our long running batch process falls over because one entry combines a timestamp and interval that results in an invalid date… and in SQL the ability to catch and handle exceptions like these is very difficult and complicated.

So, the solution would appear to be not to use the INTERVAL MONTH TO DAY data type when requiring an offset from a TIMESTAMP. Unfortunately this is one of the core reasons for using INTERVAL data types in the first place.

Advertisement