Обсуждение: BUG #1107: Missing feature: interval <-> numeric quantity conversion

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.

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

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

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

On Thu, 18 Mar 2004, PostgreSQL Bugs List wrote:

> 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

date_part('epoch', intervalval)
 or
EXTRACT(EPOCH FROM intervalval)
 gives the number of seconds in the interval as per the documentation of
these functions in the date/time functions and operators section of the
documentation.

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

Hi:
....
> 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 do this a lot, just try 'extract(epoch from the_interval)' ( actually in my
code i use things like 'extract(epoch from age(disconnect_time,
connect_time))' where *_time are of type 'timestamp with time zone' and works
like a charm, even across time zone changes.

Francisco Olarte..