Re: Format intervall as hours/minutes etc

[Shane Ambler <pgsql(at)Sheeky(dot)Biz>]

Andreas Joseph Krogh wrote:
> On Sunday 16 September 2007 13:14:27 Andreas Kretschmer wrote:
> > Andreas Joseph Krogh <andreak(at)officenet(dot)no> schrieb:
> > > Hi all. Any hint on how to format this interval as number of hour/seconds
> > > etc? select age('2007-09-22 17:00'::timestamp, '2000-02-20
> > > 18:00'::timestamp); age
> > > -------------------------------
> > >  7 years 7 mons 1 day 23:00:00
> > You can use extract(epoch, from ...) like this:
> >
> > test=*# select extract(epoch from '2007-09-22 17:00'::timestamp) - extract
> > (epoch from '2000-02-20 18:00'::timestamp); ?column?
> > -----------
> >  239407200
> > (1 row)
> >
> > Now you can calculate the hours and so on.
>
> Yes, this works fine for dates >= 1970, but I'm looking for a more general 
> solution which takes an arbitrary interval as input. The reason why I'm using 
> PG to calculate this is 'cause it takes 25/23 hour days, leapyears etc. into 
> account when calculating intervals.

Is that all you use it for?? ;-)

You may want to add the timezone to get the effect of daylight savings.

postgres=# select age('2007-03-25 7:00:00'::timestamptz, '2007-03-25 
1:00:00'::timestamptz);
   age
----------
 06:00:00
(1 row)

postgres=# select age('2007-03-25 7:00:00+9:30'::timestamptz, 
'2007-03-25 1:00:00+9:30'::timestamptz);
   age
----------
 05:00:00
(1 row)


I haven't used intervals much so I may be missing something.

I get the idea you want the interval to be expressed as 2,765 days and 
23 hours or 66,383 hours, which I think would be useful (more so for 
shorter intervals).

I am thinking the exact function you are after isn't there - from what I 
can find a larger interval is always given as x years y months z days... 
which is why extracting the epoch is the easiest point to start your calcs.

Maybe this can be a feature request - functions to give an interval in 
total number of days/hours/minutes instead of years months days




--

Shane Ambler
pgSQL(at)Sheeky(dot)Biz

Get Sheeky @ http://Sheeky.Biz