Can't EXTRACT number of months from an INTERVAL

From: "Eliot, Christopher" <christopher(dot)eliot(at)nagrastar(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Can't EXTRACT number of months from an INTERVAL
Date: 2010-06-30 22:45:48
Message-ID: EDF9B887785F8A47B7F96D70AD69D91001147F176E@nstar-server7
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

To start with, I tried subtracting a sample timestamp as would be found in the DB from my benchmark date:
uatrackingdb=> select timestamp '2010-06-26 00:00:00' - timestamp '2008-11-07 00:00:00';
?column?
----------
596 days
(1 row)

I get a result in just days; no years or months cited. I don't understand that.
When I try to extract the months "part" of this value, I get:

uatrackingdb=> select extract ('months' from (select timestamp '2010-06-26 00:00:00' - timestamp '2008-11-07 00:00:00'));
date_part
-----------
0
(1 row)

It doesn't matter if I use explicitly cited timestamps as I've shown here or select a value from a timestamp field in the DB, the results are the same.

Using DATEs instead of TIMESTAMPs just makes things worse, I can't even get my expressions to parse correctly. Attempting to coerce the 595 days into an INTERVAL gets me nowhere.

Can anyone please advise me on how to proceed?

Topher Eliot
christopher(dot)eliot(at)nagrastar(dot)com
+01 303 706-5766
[]

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rick.Casey 2010-06-30 23:01:58 left outer join fails because "column .. does not exist in left table?"
Previous Message Bidski 2010-06-30 22:30:31 Problems building from source