2

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

avi
  • 1,626
  • 3
  • 27
  • 45

3 Answers3

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

pozs
  • 34,608
  • 5
  • 57
  • 63
  • 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. – avi Mar 27 '17 at 10:37
  • @avi then a rough estimation should be enough for you. – pozs Mar 27 '17 at 10:40
  • OK. but how do I modify it work with variables? Why did you use '2017-03-27 00:00:00+03' and not NOW()? – avi Mar 27 '17 at 11:13
  • @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. – pozs Mar 27 '17 at 11:15
  • 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. – avi Mar 27 '17 at 12:50
  • @avi yes, I fixed that edge case. – pozs Mar 27 '17 at 13:06
  • I use time stamp without time zone. It should be immutable!? Why? – avi Mar 27 '17 at 14:11
  • @avi the `age` function works with `timestamp`s, so that's fine. But `date_trunc` can work with both `timestamp` or `timestamptz`. When you work with both of the same time, conversion will happen (which will depend on your currently set time zone). If you work with just `timestamp` values (without time zone) than things will get somewhat easier (and the function won't depend on external settings, so it can be `immutable` -- which means it will always return the same results for the same parameters: PostgreSQL can cache them, use in indexes, etc.). – pozs Mar 27 '17 at 14:15
  • @pozs the difference between 2018-03-01 and 2018-04-01 is 2 according to this function? This error appears to be inserted after you fixed the edge case where there was less then 1 months difference. – Jonathan Apr 10 '18 at 09:01
  • @Jonathan your suggestion wouldn't work in all cases unfortunately, but I modified the solution to handle your edge-case as well; thanks to your comment. – pozs Apr 10 '18 at 20:06
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)
Georgi Raychev
  • 1,288
  • 1
  • 13
  • 26
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;
etsa
  • 5,020
  • 1
  • 7
  • 18