Standards for Date/Time addition?

Question

I'm looking for standards for Date/Time addition. I haven't been able to find any. In particular I'm hoping to find a spec that defines what should happen when you add a month to a date like January 31st. Is the right answer February 28th(/29th)? March 1st? March 2nd?

I've seen inconsistent implementations between different tools (PHP & MySQL in this case), and I'm trying to find some sort of standards to base my work on.

Differing Results:

PHP

$end = strtotime("+1 month", 1314835200);
//1317513600   Sat, 01 Oct 2011 20:00:00 -0400

MySQL

SELECT UNIX_TIMESTAMP(DATE_ADD(FROM_UNIXTIME(1314835200), INTERVAL 1 MONTH));
#1317427200    Fri, 30 Sep 2011 20:00:00 -0400

Oracle

SELECT ADD_MONTHS('31-Aug-11', 1) FROM dual;
#30-SEP-11

(sorry for the format change, my oracle foo is weak)

Java

Calendar c = Calendar.getInstance();
c.clear();
c.set( 2011, Calendar.AUGUST, 31 );
c.add( Calendar.MONTH, 1 );
c.getTime()
#Fri Sep 30 00:00:00 EDT 2011

Answer 1

Try the mysql date function :

SELECT ADDDATE('2011-01-31', INTERVAL 1 MONTH) // 2011-02-28

Input date with leap year

SELECT ADDDATE('2012-01-31', INTERVAL 1 MONTH) // 2012-02-29

Answer 2

In the .NET framework the behavior of System.DateTime.AddMonths is as follows:

The AddMonths method calculates the resulting month and year, taking into account leap years and the number of days in a month, then adjusts the day part of the resulting DateTime object. If the resulting day is not a valid day in the resulting month, the last valid day of the resulting month is used. For example, March 31st + 1 month = April 30th [rather than April 31st].

I've tested how it works exactly:

Console.WriteLine(new DateTime(2008,2,27).AddMonths(1));
Console.WriteLine(new DateTime(2008,2,28).AddMonths(1));
Console.WriteLine(new DateTime(2008,2,29).AddMonths(1));
Console.WriteLine(new DateTime(2011,2,27).AddMonths(1));
Console.WriteLine(new DateTime(2011,2,28).AddMonths(1));
Console.WriteLine(new DateTime(2008,1,30).AddMonths(1));
Console.WriteLine(new DateTime(2008,1,31).AddMonths(1));
Console.WriteLine(new DateTime(2011,1,30).AddMonths(1));
Console.WriteLine(new DateTime(2011,1,31).AddMonths(1));
/* output
3/27/2008 12:00:00 AM
3/28/2008 12:00:00 AM
3/29/2008 12:00:00 AM
3/27/2011 12:00:00 AM
3/28/2011 12:00:00 AM
2/29/2008 12:00:00 AM
2/29/2008 12:00:00 AM
2/28/2011 12:00:00 AM
2/28/2011 12:00:00 AM
    */

Answer 3

I believe the defacto standard is ISO 8601. Unfortunately, there are many ambiguities, for example:

Date arithmetic is not defined

2001-03-30 + P1M = 2001-04-29 (Add 30 days)
2001-03-30 + P1M = 2001-04-30 (Add 1 mon.)

Addition is not commutative or associative

2001-03-30 + P1D + P1M = 2001-04-30
2001-03-30 + P1M + P1D = 2001-05-01

Subtraction is not the inverse of Addition.

Precision of decimal fractions can vary.

The full specification can be found at http://www.iso.org/iso/catalogue_detail.htm?csnumber=26780

I think each product is attempting to adhere to an impossible to implement standard. The ambiguous parts are open to interpretation and so everyone interprets. This is the same standard that opened us up to the Y2K bug!!

Myself, I favor an implementation that converts a date/time to a 1970 based number (UNIX timestamp), performs the calculation and converts back. I believe this is the approach taken by Oracle/MySQL. I am surprised that more attention has not been paid this issue, as it is really important, sometimes critical, in so many applications. Thanks for the question!

Edit: While doing some more reading, I found Joe Celko's thoughts on different date/time representations and standardization HERE.

Answer 4

Query:

SELECT
ADDDATE(DATE('2010-12-31'), INTERVAL 1 MONTH) 'Dec + Month',
ADDDATE(DATE('2011-01-31'), INTERVAL 1 MONTH) 'Jan + Month',
ADDDATE(DATE('2011-02-28'), INTERVAL 1 MONTH) 'Feb + Month',
ADDDATE(DATE('2011-03-31'), INTERVAL 1 MONTH) 'Mar + Month';

Output:

    Dec + Month  Jan + Month  Feb + Month   Mar + Month
    2011-01-31   2011-02-28   2011-03-28    2011-04-30

My conclusion:

1. Calculate the number of days in the month of the input date.
2. Add that many days to the input date.
3. Check if the day in the resulting date exceeds the maximun number of days in the resulting month.
4. If yes, then change the resulting day to maximum day of the resulting month.

If you add MONTH, YEAR_MONTH, or YEAR and the resulting date has a day that is larger than the maximum day for the new month, the day is adjusted to the maximum days in the new month

source

Problem here is that it doesn't mention that the month is actually the month from the input date.

Answer 5

Joda-Time in Java chooses the previous valid date when an invalid one is created. For example, 2011-01-31 + P1M = 2011-02-28. I believe this is the most widely chosen default choice in date-time libraries, and thus a de facto standard.

ThreeTen/JSR-310 provides a strategy pattern for this, with four choices, see the code.

More amusing is the question of what the answer to 2011-01-31 + P1M-1D is. If you add the month, then resolve the invalid date, then subtract the day, you get 2011-02-27. But I think most users expect 2011-02-28 because the period is being added in a single lump. See how ThreeTen handles this here.

I have considered trying to write a general purpose best practices in date/time calculations, or actual spec, but haven't really had the time!

Answer 6

First day of the month + 1 month should equal the first of the next month. Trying this on SQL Server

          SELECT CAST ('01/01/2012' AS DateTime), DATEADD (m, 1, '01/01/2012')
UNION ALL SELECT CAST ('02/01/2012' AS DateTime), DATEADD (m, 1, '02/01/2012')
UNION ALL SELECT CAST ('03/01/2012' AS DateTime), DATEADD (m, 1, '03/01/2012')
UNION ALL SELECT CAST ('04/01/2012' AS DateTime), DATEADD (m, 1, '04/01/2012')
UNION ALL SELECT CAST ('05/01/2012' AS DateTime), DATEADD (m, 1, '05/01/2012')

This results in

----------------------- -----------------------
2012-01-01              2012-02-01             
2012-02-01              2012-03-01             
2012-03-01              2012-04-01             
2012-04-01              2012-05-01             
2012-05-01              2012-06-01             

Last day of this month + 1 month should equal last day of next month. This should go for next month, current month, 10 months down, etc.

          SELECT CAST ('01/31/2012' AS DateTime), DATEADD (m, 1, '01/31/2012')
UNION ALL SELECT CAST ('01/30/2012' AS DateTime), DATEADD (m, 1, '01/30/2012')
UNION ALL SELECT CAST ('01/29/2012' AS DateTime), DATEADD (m, 1, '01/29/2012')
UNION ALL SELECT CAST ('01/28/2012' AS DateTime), DATEADD (m, 1, '01/28/2012')
UNION ALL SELECT CAST ('01/27/2012' AS DateTime), DATEADD (m, 1, '01/27/2012')
UNION ALL SELECT CAST ('01/26/2012' AS DateTime), DATEADD (m, 1, '01/26/2012')

This results in

----------------------- -----------------------
2012-01-31              2012-02-29             
2012-01-30              2012-02-29             
2012-01-29              2012-02-29             
2012-01-28              2012-02-28             
2012-01-27              2012-02-27             
2012-01-26              2012-02-26             

See how 31, 30, 29 all become feb 29 (2012 is a leap year).

p.s. I took off the time parts (all zeroes) to help make it more readable