Thread: Converting time interval to double precision of time unit

I'm using 8.3, and I'm trying to work with the interval type, and I
can't seem to get things right. I've been all over the docs[1,2], and
there is no mention on how this can be done.

While I can get:
SELECT '3 day 2 hour 34 minute'::interval

.. how can then get the fractional hours of this time interval in
double precision (or seconds, minutes, years, decades, etc.)?

Do I really need to extract the time subcomponents and do the math myself?

For example:
SELECT extract(day from interval)*24 + extract(hour from interval) +
extract(minute from interval)/60 as hours
FROM (SELECT '3 day 2 hour 34 minute'::interval) AS foo;

This seem like a bad hack, and I can't believe a function doesn't
already exist to properly cast a time interval to a fractional unit of
time, so I thought I'd check up to see if there is a better solution.

Thanks,

-Mike

[1] http://www.postgresql.org/docs/8.3/static/functions-datetime.html
[2] http://www.postgresql.org/docs/8.3/static/datatype-datetime.html

On 30 Mar 2010, at 18:29, Mike Toews wrote:

> I'm using 8.3, and I'm trying to work with the interval type, and I
> can't seem to get things right. I've been all over the docs[1,2], and
> there is no mention on how this can be done.
>
> While I can get:
> SELECT '3 day 2 hour 34 minute'::interval
>
> .. how can then get the fractional hours of this time interval in
> double precision (or seconds, minutes, years, decades, etc.)?
>
> Do I really need to extract the time subcomponents and do the math myself?

You shouldn't try to do that. How do you expect to convert an interval type to a timestamp without having a timestamp
tobase it on? It's a relative quantity with a variable value depending on it's base value. For a meaningful answer it
requiresinformation about DST changes, different month lengths, leap years, etc, which it won't have if you don't tell
whereyou're basing your interval off. 

If instead you base your interval on a relevant base-timestamp, then you can simply extract epoch from the result,
althoughthats in seconds and not (fractional) hours, but that's a linear relationship. 

For example,

SELECT extract(epoch from now() + interval '3 days 2 hours 34 minutes')


Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4bb236cf10412084085775!

Alban Hertroys <dalroi@solfertje.student.utwente.nl> writes:
> On 30 Mar 2010, at 18:29, Mike Toews wrote:
>> I'm using 8.3, and I'm trying to work with the interval type, and I
>> can't seem to get things right. I've been all over the docs[1,2], and
>> there is no mention on how this can be done.
>>
>> While I can get:
>> SELECT '3 day 2 hour 34 minute'::interval
>>
>> .. how can then get the fractional hours of this time interval in
>> double precision (or seconds, minutes, years, decades, etc.)?
>>
>> Do I really need to extract the time subcomponents and do the math myself?

> You shouldn't try to do that. How do you expect to convert an interval type to a timestamp without having a timestamp
tobase it on? It's a relative quantity with a variable value depending on it's base value. For a meaningful answer it
requiresinformation about DST changes, different month lengths, leap years, etc, which it won't have if you don't tell
whereyou're basing your interval off. 

> If instead you base your interval on a relevant base-timestamp, then you can simply extract epoch from the result,
althoughthats in seconds and not (fractional) hours, but that's a linear relationship. 

I think what Mike is actually looking for is

SELECT extract(epoch from interval '3 days 2 hours 34 minutes');
 date_part
-----------
    268440
(1 row)

although your point about the uncertainty of the conversion for units of
days or larger is certainly well-taken.

            regards, tom lane

On 30 March 2010 11:55, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I think what Mike is actually looking for is
>
> SELECT extract(epoch from interval '3 days 2 hours 34 minutes');
>  date_part
> -----------
>    268440

Yet better, if I define 1 hour as 3600 seconds (this is only incorrect
if the interval spans over a leap second), then the fractional hours
are:

SELECT extract(epoch from interval '3 days 2 hours 34 minutes')/3600 as hours;

Thanks!

-Mike