[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Converting time interval to double precision of time unit
Alban Hertroys 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Converting time interval to double precision of time unit
On 30 March 2010 11:55, Tom Lane 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
