Thread: Can't EXTRACT number of months from an INTERVAL

Can't EXTRACT number of months from an INTERVAL

From
"Eliot, Christopher"
Date:

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.eliot@nagrastar.com

+01 303 706-5766

[]

Re: Can't EXTRACT number of months from an INTERVAL

From
Michael Glaesemann
Date:
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


Re: Can't EXTRACT number of months from an INTERVAL

From
"Eliot, Christopher"
Date:
Thanks!  justify_interval is what I needed, I had never heard of it.  I was already prepared to deal with getting the
yearsand multiplying by 12. 

Topher
[]

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