Thread: BUG #1107: Missing feature: interval <-> numeric quantity conversion
BUG #1107: Missing feature: interval <-> numeric quantity conversion
From
"PostgreSQL Bugs List"
Date:
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
Re: BUG #1107: Missing feature: interval <-> numeric quantity conversion
From
Francisco Olarte Sanz
Date:
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..