Find out number of months between 2 dates

Question

select 
  (age('2012-11-30 00:00:00'::timestamp, '2012-10-31 00:00:00'::timestamp)),
  (age('2012-12-31 00:00:00'::timestamp, '2012-10-31 00:00:00'::timestamp)),
  (age('2013-01-31 00:00:00'::timestamp, '2012-10-31 00:00:00'::timestamp)),
  (age('2013-02-28 00:00:00'::timestamp, '2012-10-31 00:00:00'::timestamp))

which gives the followings:

  0 years 0 mons 30 days 0 hours 0 mins 0.00 secs
  0 years 2 mons 0 days 0 hours 0 mins 0.00 secs
  0 years 3 mons 0 days 0 hours 0 mins 0.00 secs
  0 years 3 mons 28 days 0 hours 0 mins 0.00 secs

But I want to have the following month definition , how can I do it?

  0 years 1 mons 0 days 0 hours 0 mins 0.00 secs
  0 years 2 mons 0 days 0 hours 0 mins 0.00 secs
  0 years 3 mons 0 days 0 hours 0 mins 0.00 secs
  0 years 4 mons 0 days 0 hours 0 mins 0.00 secs

This question needs more information on the requirements. Do you always have the last day of the month in your timestamps? Why do you have timestamps at all instead of dates? How is "month" defined exactly in your question? — Erwin Brandstetter, May 19 '14 at 02:07
@ErwinBrandstetter Yes . Actually I just want to compare two dates only . The month definition just compares the month field , so N months later from a start date just increases the month field of the start date. But for some special case when the start date is from a "long" month (eg '2012-10-31') , the date does not exist after increasing the month field (eg '2013-02-31') , in such case , just use the last date of that month (i.e '2013-02-31') .It looks like the behavior of "select date '2012-10-31' + interval '4 month' " — Ken Chan, May 19 '14 at 04:38
I repeat: Do you *always* have the last day of the month in your data? — Erwin Brandstetter, May 19 '14 at 13:54
@ErwinBrandstetter Both the from date and the to date can be any date. — Ken Chan, May 19 '14 at 14:03
Basic requirements are best disclosed in the question itself. I suggest you edit to clarify. — Erwin Brandstetter, May 19 '14 at 14:20

klin · Accepted Answer · 2018-03-29T19:13:48.970

The expression

age('2012-11-30 00:00:00'::timestamp, '2012-10-31 00:00:00'::timestamp)

gives 30 days. We are expecting 1 month as both values point to last days of month. If we add 1 day to the values we shall get first days of next month and

age('2012-12-01 00:00:00'::timestamp, '2012-11-01 00:00:00'::timestamp)

will give us 1 month as expected. So let us check if we have two last days of month and in this case return age interval of the next days. In other cases we shall return age interval of original values:

create or replace function age_m (t1 timestamp, t2 timestamp)
returns interval language plpgsql immutable
as $$
declare
    _t1 timestamp = t1+ interval '1 day';
    _t2 timestamp = t2+ interval '1 day';
begin
    if extract(day from _t1) = 1 and extract(day from _t2) = 1 then
        return age(_t1, _t2);
    else
        return age(t1, t2);
    end if;
end $$;

Some examples:

with my_table(date1, date2) as (
values
    ('2012-11-30 00:00:00'::timestamp, '2012-10-31 00:00:00'::timestamp),
    ('2012-12-31 00:00:00'::timestamp, '2012-10-31 00:00:00'::timestamp),
    ('2013-01-31 00:00:00'::timestamp, '2012-10-31 00:00:00'::timestamp),
    ('2013-02-28 00:00:00'::timestamp, '2012-10-31 00:00:00'::timestamp)
)

select *, age(date1, date2), age_m(date1, date2)
from my_table

        date1        |        date2        |      age       | age_m  
---------------------+---------------------+----------------+--------
 2012-11-30 00:00:00 | 2012-10-31 00:00:00 | 30 days        | 1 mon
 2012-12-31 00:00:00 | 2012-10-31 00:00:00 | 2 mons         | 2 mons
 2013-01-31 00:00:00 | 2012-10-31 00:00:00 | 3 mons         | 3 mons
 2013-02-28 00:00:00 | 2012-10-31 00:00:00 | 3 mons 28 days | 4 mons
(4 rows)

score 2 · Answer 2 · edited May 23 '17 at 12:15

It seems like you always use the last day of the month. What you are trying to do works flawlessly with the first day of the month. So use that instead. You can always subtract a single day to get the last day of the previous month.

@klin's function is based on that. For dates (instead of timestamps), simplify:

_t1 date = t1 + 1;
_t2 date = t2 + 1;

One can just add / subtract integer values from dates (but not timestamps).

If you want to add "a month", don't just increase the month field, since this can fail like you have experienced. And there is also the wrap around at the end of the year. Add an interval '1 month' instead.

SELECT (mydate + interval '1 month')::date AS mydate_next_month;

I cast back to date because the result of date + interval is a timestamp.

This "rounds down" automatically, if the last day of the next month is before the day in the original date. Note that it does not "round up" in the opposite case. If you want that, operate with the first of the month instead as explained above.

SQL Fiddle.

score 0 · Answer 3 · answered May 19 '14 at 10:29

This is a modified version of the time rounding function located on PostgreSQL's official wiki:

CREATE OR REPLACE FUNCTION interval_round(base_interval INTERVAL, round_interval INTERVAL) RETURNS INTERVAL AS $BODY$
SELECT justify_interval((EXTRACT(epoch FROM $1)::INTEGER + EXTRACT(epoch FROM $2)::INTEGER / 2)
                / EXTRACT(epoch FROM $2)::INTEGER * EXTRACT(epoch FROM $2)::INTEGER * INTERVAL '1 second');
$BODY$ LANGUAGE SQL STABLE;

You can call it with another interval to round to, f.ex.

SELECT interval_round(age('2013-02-28 00:00:00'::timestamp,
                          '2012-10-31 00:00:00'::timestamp), '1 month')

will return 4 mons.

score -1 · Answer 4 · answered May 18 '14 at 22:37

From http://www.postgresql.org/docs/8.4/static/functions-datetime.html, can you swap the order of the timestamps?

"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."

Swapping the order of the timestamps doesn't give the desired output. — Mike Sherrill 'Cat Recall', May 18 '14 at 22:50

Find out number of months between 2 dates

4 Answers4

Linked