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