From: | Michael Glaesemann <grzm(at)seespotcode(dot)net> |
---|---|
To: | "Eliot, Christopher" <christopher(dot)eliot(at)nagrastar(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Can't EXTRACT number of months from an INTERVAL |
Date: | 2010-06-30 23:27:26 |
Message-ID: | C0532359-302A-4F07-95B8-7301239B2996@seespotcode.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Howard Rogers | 2010-06-30 23:49:12 | Re: DBI::Oracle problems |
Previous Message | Scott Marlowe | 2010-06-30 23:25:25 | Re: left outer join fails because "column .. does not exist in left table?" |