How to get numeric value of Months between two dates in PostgreSQL?

Question

I have two dates in format Time Stamp Without Time Zone.

I want to compare them and get the numeric value of months between them:

select age(NOW(), '2012-03-24 14:44:55.454041+03')

Gives:

4 years 9 mons 2 days 21:00:27.165482

The trick here is that I need to convert this result into one value of months.

So: In order to convert the YEARS to Months:

select EXTRACT(YEAR FROM age) * 12 + EXTRACT(MONTH FROM age) 
                FROM age(NOW(), '2012-06-24 14:44:55.454041+03') AS t(age)

I get 57 which is 4*12+9.

My problem is that I don't know how to convert the days. In the above example I need to convert the '2 days' into it's value in months. '2 days' isn't 0 months!

In Months of 30 days 15 days are 0.5 months.

How can I do that? The final result should be 57.something

@a_horse_with_no_name the answer there does not take days into account. — avi
– avi, Commented Mar 27, 2017 at 10:22

pozs · Accepted Answer · 2018-04-10 20:04:54Z

3

You can get a rough estimation with:

select (extract(epoch from timestamptz '2012-06-24 14:44:55.454041+03')
      - extract(epoch from timestamptz '2017-03-27 00:00:00+03'))
      / extract(epoch from interval '30.44 days') rough_estimation

(you can divide with extract(epoch from interval '1 month') for an even more rough estimation).

The problem with your original formula is that it is designed to give a complete month difference between two dates. If you want to account days too an interesting problem arises: in your example, the result should be 57 months and 2 days 21:00:27.165482. But in what month should the 2 days 21:00:27.165482 part is calculated? An average-length month (30.44 days)? If you want to be precise, it should be noted that in your example case, the difference is really only 56 months, plus almost 7 days in 2012-06 (which had 30 days) and 27 days in 2017-03 (which has 31 days). The question you should ask yourself: is it really worth an advanced formula which takes account both range ends' days-in-a-month or not?

Edit: For completeness, here is a function, which can take both range end into consideration:

create or replace function abs_month_diff(timestamptz, timestamptz)
  returns numeric
  language sql
  stable
as $func$
  select extract(year from age)::numeric * 12 + extract(month from age)::numeric
             + (extract(epoch from (lt + interval '1 month' - l))::numeric / extract(epoch from (lt + interval '1 month' - lt))::numeric)
             + (extract(epoch from (g - gt))::numeric / extract(epoch from (gt + interval '1 month' - gt))::numeric)
             - case when gt <= l or lt = l then 1 else 0 end
  from   least($1, $2) l,
         greatest($1, $2) g,
         date_trunc('month', l) lt,
         date_trunc('month', g) gt,
         age(gt, l)
$func$;

(Note: if you use timestamp instead of timestamptz, this function is immutable instead of stable. Just like the date_trunc functions.)

So:

select age('2017-03-27 00:00:00+03', '2012-06-24 14:44:55.454041+03'),
       abs_month_diff('2017-03-27 00:00:00+03', '2012-06-24 14:44:55.454041+03');

will yield:

 age                                   | abs_month_diff
---------------------------------------+-------------------------
 4 years 9 mons 2 days 09:15:04.545959 | 57.05138456751051843959

http://rextester.com/QLABV31257 (outdated)

Edit: function is corrected to produce exact results when the difference is less than a month.

See f.ex:

set time zone 'utc';

select abs_month_diff('2017-02-27 00:00:00+03', '2017-02-24 00:00:00+03'), 3.0 / 28,
       abs_month_diff('2017-03-27 00:00:00+03', '2017-03-24 00:00:00+03'), 3.0 / 31,
       abs_month_diff('2017-04-27 00:00:00+03', '2017-04-24 00:00:00+03'), 3.0 / 30,
       abs_month_diff('2017-02-27 00:00:00+00', '2017-03-27 00:00:00+00'), 2.0 / 28 + 26.0 / 31;

http://rextester.com/TIYQC5325 (outdated)

Edit 2: This function is based on the following formula, to calculate the length of a month:

select (mon + interval '1 month' - mon)
from   date_trunc('month', now()) mon

This will even take DST changes into account. F.ex. in my country there was a DST change yesterday (on 2017-03-26), so today (2017-03-27) the above query reports: 30 days 23:00:00.

Edit 3: Function is corrected again (thanks to @Jonathan who noticed an edge-case of an edge-case).

http://rextester.com/JLG68351

edited Apr 10, 2018 at 20:04

answered Mar 27, 2017 at 10:34

pozs

36.6k5 gold badges61 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

avi Over a year ago

I for example ignore the hours.... Days are important but not that much. If it's 56 months +1 day or 56 months it's the same for me. However 56.5 months are not the same as 56 months... (15 days difference) Days are also more important for cases where there are 0 months...then it should be better accurate.

pozs Over a year ago

@avi then a rough estimation should be enough for you.

avi Over a year ago

OK. but how do I modify it work with variables? Why did you use '2017-03-27 00:00:00+03' and not NOW()?

pozs Over a year ago

@avi you can use any timestamp, even now(). I used a fix timestamp to avoid my sample observations to be outdated with time. F.ex. a week from now the difference between now() and '2012-06-24 14:44:55.454041+03' will be much higher.

avi Over a year ago

It seems to work but it has bugs. It doesn't calculate correctly if the difference is less than 1 month. for example: select abs_month_diff (NOW()::timestamp without time zone ,(NOW()- interval '15 days' )::timestamp without time zone) The difference is approximately 0.5 months (15 days) and not 1.48387096774193548387 which is 1 and a half months.

|

etsa · Accepted Answer · 2017-03-27 09:15:33Z

1

You can obtain an approximate value with something similar to this:

SELECT A, B*12+C-1+E/30 MONTHSBETWEEN 
FROM (
SELECT current_timestamp A, EXTRACT(YEAR FROM current_timestamp) B, EXTRACT(MONTH FROM current_timestamp) C, EXTRACT(DAYS FROM current_timestamp) E
    ) X;

or better precision with something like this:

SELECT A, B*12+C-1+E/ DATE_PART('days', 
        DATE_TRUNC('month', A) 
        + '1 MONTH'::INTERVAL 
        - '1 DAY'::INTERVAL
    ) MONTHSBETWEEN 
FROM (
SELECT current_timestamp A, EXTRACT(YEAR FROM current_timestamp) B, EXTRACT(MONTH FROM current_timestamp) C, EXTRACT(DAYS FROM current_timestamp) E
    ) X;

edited Mar 27, 2017 at 9:15

answered Mar 27, 2017 at 9:01

etsa

5,0501 gold badge9 silver badges18 bronze badges

4 Comments

etsa Over a year ago

(added -1 to months: if you add the days, I think it should be done...)

avi Over a year ago

why do I need the -1?

etsa Over a year ago

Otherwise I think you consider two times the current month.

avi Over a year ago

Can you give an example?

Georgi Raychev · Accepted Answer · 2017-03-27 09:21:55Z

1

This should do:

select (EXTRACT(YEAR FROM age) * 12 + EXTRACT(MONTH FROM age) + EXTRACT(DAY FROM age) / 30)::numeric 
FROM age(NOW(), '2012-06-24 14:44:55.454041+03') AS t(age)

You can also add ROUND() to make it prettier:

select ROUND((EXTRACT(YEAR FROM age) * 12 + EXTRACT(MONTH FROM age) + EXTRACT(DAY FROM age) / 30)::numeric,2) as Months
FROM age(NOW(), '2012-06-24 14:44:55.454041+03') AS t(age)

edited Mar 27, 2017 at 9:21

answered Mar 27, 2017 at 9:01

Georgi Raychev

1,3441 gold badge13 silver badges26 bronze badges

Collectives™ on Stack Overflow

How to get numeric value of Months between two dates in PostgreSQL?

3 Answers 3

10 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

10 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related