On Thu, 2008-08-21 at 11:53 +0200, Tomasz Ostrowski wrote:
> On 2008-08-21 11:09, Ow Mun Heng wrote:
>
> > I want to find out if there's a method to change this
> > select to_char('1 day 09:18:42.37996'::interval,'HH24:MI:SS')
> > to something like
> > 24+9(hours) = 33:18:42 instead of returning It as 09:19:42
>
> That's because 1 day doesn't always have 24 hours, because there are
> daylight saving times. 1 month can also have various number of days or
> hours.
>
> If you want 1 day to be always 24 hours then:
> select date_part('day', t)*'24h'::interval + t-date_trunc('day',t)
> from (select '1 day 09:18:42.37996'::interval as t) as s;
>
> It will not work for intervals with months. I'm not sure for negative ones.
>
Thanks for all the suggestions, but in the end, I went back to
old-school solution, meaning, choose the lowest denominator which is
epoch and seconds.
extract('epoch' from (max(a.delta))/3600)
where a.delta = '1 day 09:18:42.37996'
which returns me something in the form of X.YZ (14.11) or sotmehing like
that..
