Re: Converting time interval to double precision of time unit

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl>
Cc: Mike Toews <mwtoews(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Converting time interval to double precision of time unit
Date: 2010-03-30 18:55:26
Message-ID: 27171.1269975326@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)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 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.

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Mike Toews 2010-03-30 19:36:07 Re: Converting time interval to double precision of time unit
Previous Message Greg Smith 2010-03-30 18:25:17 Re: Wiki Updates - 9.0