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

On Thu, Mar 18, 2004 at 10:38:28AM -0400, 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.

date_part( 'epoch', <interval> ) does what you want to convert an interval
into seconds as a numeric value.

--Joe
--
Joe Sunday <sunday(at)csh(dot)rit(dot)edu> http://www.csh.rit.edu/~sunday/
Computer Science House, Rochester Inst. Of Technology