Skip site navigation (1) Skip section navigation (2)

Re: Converting time interval to double precision of time unit

From: Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl>
To: Mike Toews <mwtoews(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Converting time interval to double precision of time unit
Date: 2010-03-30 17:37:05
Message-ID: (view raw, whole thread or download thread mbox)
Lists: 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.


In response to


pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group