Re: Converting time interval to double precision of time unit

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 to base it on? It's a relative quantity with a variable value depending on it's base value. For a meaningful answer it requires information about DST changes, different month lengths, leap years, etc, which it won't have if you don't tell where you'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, although thats 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!