On a related subject, I'm trying to get postgres 7.3.2 to spit out an
interval in terms of days. For example, I'm currently getting this
behavior:
<- select '2002-05-04 01:03'::timestamp - '2002-05-02'::timestamp;
-> 2 days 01:03
The result is an interval, which gets reported as some varying
combination of days, hours, minutes, etc. (Maybe even months or years
in some cases?) What my application wants is an integer value
representing the difference between timestamps in terms of days. I
tried using the round() function, but round() doesn't work on
intervals.
I just saw the following query posted earlier in this thread:
>Here is one consistent conversion that will show you the number of seconds
>without anything else:
>
>select round(extract(epoch from finish) - extract(epoch from start)) from timetable;
Hmm. The postgres 7.3 docs give me the impression that extract() will
return one field of a multi-field value, such as '4 days' from '2
years 4 days 15:01'. Experimenting in psql seems to prove this. For
example:
<- select extract( day from '2 years 4 days 15:01'::interval);
-> 4
Okay, so extract() doesn't fit the bill either. How do I get the
difference between two timestamps in terms of days, expressed as an
integer? Moreover, how do I get any interval expressed in those
terms?
(Should I be posting this to the -sql list instead?)