extract () and interval formatting

From: Robert L Mathews <lists(at)tigertech(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: extract () and interval formatting
Date: 2002-08-13 18:27:48
Message-ID: 20020813182749.53E7F3FC3F5@mail1.tigertech.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a question about using extract() with intervals. In short, I was
surprised by the different results between these two queries:

# select extract (month from age ('2002-09-01 12:00'::timestamp,
'2002-07-01'::timestamp));

date_part
-----------
2
(1 row)

# select extract (month from ('2002-09-01 12:00'::timestamp -
'2002-07-01'::timestamp));

date_part
-----------
0
(1 row)

This apparently happens because:

age ('2002-09-01 12:00'::timestamp, '2002-07-01'::timestamp)

... is "2 mons 12:00", but:

'2002-09-01 12:00'::timestamp - '2002-07-01'::timestamp

... is "62 days 12:00".

I would have expected the extract() result to be the same -- I guess I
assumed that an interval is stored as an internal representation of a
time period, and extract() would work on that. But this result seems to
indicate that extract() works on the text representation of the interval,
so you get different results based on how the interval was formatted.

So, I guess my question is, am I understanding correctly that the
extract() function works on the formatted text representation of the
interval, so I need to be careful about how I create intervals to pass to
extract()?

If that's the case, is the output format of the different ways of
creating intervals something I can rely on -- for example, will
subtracting two timestamps directly always give a result that "prefers"
days, and the age function always give a result that "prefers" months?

------------------------------------
Robert L Mathews, Tiger Technologies

Browse pgsql-general by date

  From Date Subject
Next Message strange 2002-08-13 18:39:01 Re: [HACKERS] Linux Largefile Support In Postgresql RPMS
Previous Message Cindy 2002-08-13 18:15:38 Re: question about upper limit on TEXT size