[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] Can't EXTRACT number of months from an INTERVAL
From:       Michael Glaesemann <grzm () seespotcode ! net>
Date:       2010-06-30 23:27:26
Message-ID: C0532359-302A-4F07-95B8-7301239B2996 () seespotcode ! net
[Download RAW message or body]


On Jun 30, 2010, at 18:45 , Eliot, Christopher wrote:

> I need to read a timestamp from the database and turn that into an integer \
> describing how many months ago the event happened, rounding downward.  The events \
> are guaranteed to be in the past.

=# select timestamp '2010-06-26 00:00:00' - timestamp  '2008-11-07 00:00:00';
 ?column? 
----------
 596 days
(1 row)

=# select justify_interval(timestamp '2010-06-26 00:00:00' - timestamp  '2008-11-07 \
00:00:00');  justify_interval    
-----------------------
 1 year 7 mons 26 days
(1 row)

=# select extract('months' from justify_interval(timestamp '2010-06-26 00:00:00' - \
timestamp  '2008-11-07 00:00:00'));  date_part 
-----------
         7
(1 row)

This is likely not what you want: you're probably looking for 19.

One way would be:

=# select 12 * extract('years' from a.i) + extract('months' from a.i) 
  from (values (justify_interval(timestamp '2010-06-26 00:00:00' - timestamp  \
'2008-11-07 00:00:00'))) as a (i);  ?column? 
----------
       19
(1 row)

If you're willing to make the assumption that each month has 30 days:

=# select cast(extract('days' from timestamp '2010-06-26 00:00:00' - timestamp  \
'2008-11-07 00:00:00') as int) / 30;  ?column? 
----------
       19
(1 row)

And you're dealing only with dates):

=# select (cast('2010-06-26' as date) - cast('2008-11-07' as date)) / 30;
 ?column? 
----------
       19
(1 row)

Datetime math can be difficult as it can be very contextual.

Michael Glaesemann
grzm seespotcode net


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[prev in list] [next in list] [prev in thread] [next in thread]