PostgreSQL Bugs List wrote:
>
> The following bug has been logged online:
>
> Bug reference: 1107
> Logged by: Jozef Behran
>
> Email address: jozef.behran@krs.sk
>
> PostgreSQL version: 7.3.2
>
> Operating system: Mandrake GNU/Linux
>
> Description: Missing feature: interval <-> numeric quantity
> conversion
>
> Details:
>
> Having two timestamps it is common need to know how many
> seconds/minutes/hours/days/etc. passed from one to the other. However there
> is no easy way to do this task.
>
> The basic idea is subtracting the two timestamps. However it gives a data
> type called "interval". The thing I would like to have is a function that
> takes the "interval" and outputs it's length. Currently when I want a
> program to know how long an interval is I must let it parse the interval
> textual representation (which may be subject to change) to obtain what I
> want.
>
> I consider this to be a bug, because it seriously degrades the usability of
> timestamp data types in applications where interval lengths are extensively
> demanded and used. I was forced to store these data in an INT8 data type
> column because my project extensively uses time interval lengths for other
> computations and converting dates to INT8 before write and then subtracting
> the numbers when need arises is MUCH faster than subtracting timestamps and
> parsing the result of such a subtraction.
>
> Note: The 'date' data type does not have this problem. The result of two
> dates subtraction is an integer (not 'interval') which I can use quite
> easily.
You can do this:
test=> select date_part('days', '1 day 2 hours 3 seconds'::interval);
date_part
-----------
1
(1 row)
test=> select date_part('hours', '1 day 2 hours 3 seconds'::interval);
date_part
-----------
2
(1 row)
test=> select date_part('seconds', '1 day 2 hours 3 seconds'::interval);
date_part
-----------
3
(1 row)
What I can't seem to do is get the total seconds in 1 day, 2 hours, 3
seconds. Anyone?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073