Re: Interval Formatting -> Convert to timestamp

From: Ow Mun Heng <Ow(dot)Mun(dot)Heng(at)wdc(dot)com>
To: Tomasz Ostrowski <tometzky(at)batory(dot)org(dot)pl>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Interval Formatting -> Convert to timestamp
Date: 2008-08-21 11:33:34
Message-ID: 1219318414.10730.2.camel@neuromancer.home.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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..

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Albe Laurenz 2008-08-21 12:03:44 Re: [GENERAL] Regarding access to a user
Previous Message Tomasz Ostrowski 2008-08-21 09:53:22 Re: Interval Formatting -> Convert to timestamp