Interval Formatting -> Convert to timestamp

On 21/08/2008 10: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

I had to do something like this recently when adding up the durations of
music CD tracks (stored as INTERVALs), and I wanted the totals returned
in terms of minutes rather than hours:

create or replace function format_duration(theDuration interval)
returns text
as
$$
select
to_char(extract(hour from $1) * 60 + extract(minute from $1), 'FM9,999')
|| to_char(extract(second from $1), '":"FM00');
$$
language sql;

HTH,

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

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.

Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh

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