## Re: Converting time interval to double precision of time unit

From: Alban Hertroys Mike Toews pgsql-general(at)postgresql(dot)org Re: Converting time interval to double precision of time unit 2010-03-30 17:37:05 02CF9FED-4DCA-4FF6-9926-B87CE167F0B0@solfertje.student.utwente.nl (view raw, whole thread or download thread mbox) 2010-03-30 16:29:52 from Mike Toews  2010-03-30 17:37:05 from Alban Hertroys   2010-03-30 18:55:26 from Tom Lane    2010-03-30 19:36:07 from Mike Toews pgsql-general
```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!

```

### pgsql-general by date

 Next: From: Scott Marlowe Date: 2010-03-30 17:44:14 Subject: Re: set statement_timeout does not work Previous: From: Jun Wang Date: 2010-03-30 17:36:38 Subject: Re: set statement_timeout does not work