CODEWITHSUNDEEP
sqlpostgresql
avi
avi

Reputation: 1846

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

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

Upvotes: 2

Views: 4848

Answers (3)

pozs
pozs

Reputation: 36224

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

Upvotes: 3

Georgi Raychev
Georgi Raychev

Reputation: 1334

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)

Upvotes: 1

etsa
etsa

Reputation: 5060

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;

Upvotes: 1

Related Questions