Thread: Interval Formatting -> Convert to timestamp

Hi,

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've not found a way to do this (yet)

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