Re: BUG #1107: Missing feature: interval <-> numeric quantity

PostgreSQL Bugs List wrote:
>
> The following bug has been logged online:
>
> Bug reference: 1107
> Logged by: Jozef Behran
>
> Email address: jozef(dot)behran(at)krs(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073