Adding months to date: PostgreSQL vs. Oracle

Question

PostgreSQL and Oracle behaviour in adding/subtracting months to/from date differs.

Basically, if we add 1 month to some day, which is not the last one of the month, they'll both return the same day number in the resulting month (or the last one for the resulting month if the day number we are adding to is greater, e.g. 28th of February when adding to 31th of January).

PostgreSQL:

# select '2015-01-12'::timestamptz + '1 month'::interval;
        date        
------------------------
 2015-02-12 00:00:00+03

Oracle:

> select add_months('12-JAN-2015',1) from dual;    
ADD_MONTH
---------
12-FEB-15

However.

If the day we are adding to is the last day of the month, Oracle will return the last day of the resulting month, even if it's bigger, and PostgreSQL will still return the same day number (or the lower one if the resulting month is shorter). This can lead to some inconsistency (even funny!), especially with adding/subtracting multiple times and even when grouping operations - in PostgreSQL the result differs:

Oracle:

> select add_months('28-FEB-2015',1) from dual;
ADD_MONTH
---------
31-MAR-15

> select add_months('31-JAN-2015',4) from dual;

ADD_MONTH
---------
31-MAY-15

> select add_months(add_months(add_months(add_months('31-JAN-2015',1),1),1),1) from dual;

ADD_MONTH
---------
31-MAY-15

PostgreSQL:

-- Adding 4 months at once:
# select '2015-01-31'::timestamptz + '4
months'::interval;
         date
-------------------------------
2015-05-31 00:00:00+03

-- Adding 4 months by one:
# select '2015-01-31'::timestamptz + '1
months'::interval + '1 months'::interval + '1 months'::interval +'1
months'::interval;
         date
-------------------------------
2015-05-28 00:00:00+03

-- Adding 4 months by one with grouping operations:
# select '2015-01-31'::timestamptz + ('1
months'::interval + '1 months'::interval) + '1 months'::interval +'1
months'::interval;
         date
-------------------------------
2015-05-30 00:00:00+03

-- And even adding 4 months and then subtracting them does not return the initial date!
# select '2015-01-31'::timestamptz + '1 months'::interval + '1 
months'::interval + '1 months'::interval +'1 months'::interval - '4 months'::interval;
        date        
------------------------
 2015-01-28 00:00:00+03

I know I could always use something like

SELECT (date_trunc('MONTH', now())+'1 month'::interval - '1 day'::interval);

to get the last day of month and use it when adding months in PostgreSQL, but

the question is: why both of them chose to implement different standards, which one is better/worse and why.

Answer 1

Oracle specify that

If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month. Otherwise, the result has the same day component as date.

PostgreSQL specify that

Note there can be ambiguity in the months returned by age because different months have a different number of days. PostgreSQL's approach uses the month from the earlier of the two dates when calculating partial months. For example, age('2004-06-01', '2004-04-30') uses April to yield 1 mon 1 day, while using May would yield 1 mon 2 days because May has 31 days, while April has only 30.

You might want to have a look at the justify_days(interval) function provided by PostgreSQL.

why both of them chose to implement different standards, which one is better/worse and why ?

None of them is better then the other (it is mostly opinion based), simply different. As of why they decided to implement different standards, honestly I don't think there really is a reason, probably just a matter of facts.