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.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