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.

Advertisements

2 thoughts on “INTERVAL MONTH TO DAY

  1. Total coincidence – I was thinking about blogging about this today myself. Had the same issue – the business raised an issue with some date logic, saying that 28/2/2011 minus 3 years was incorrectly returning 29/2/2008. This is because I’m using the ADD_MONTHS function, which recognises the last day of the month.

    They havn’t responded to my followup question yet though – “what date should be returned if the starting date is 29/2/2008?” 🙂

    • Hi Jeff,

      Yes, getting users to think about how they would like to see the end of month handled is always interesting. I tend to propose one of two approaches to help them get started.

      One approach is to use the ADD_MONTHS logic. The other approach is if after incrementing the starting date by “x” months results in a valid date then use that, otherwise cut the days back to what would be the end of the month (ala ADD_MONTHS functionality). So, if the starting date is 30 Jan 2012 and I’m adding 1 month then the result would be 29 Feb 2012. If I’m adding 2 months then the result would be 30 Mar 2012.I find this logic to be palatable to most users.

      Mark

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s