Hi David,
I had the same problem, and here is my solution (I posted it on Pg-Novice
quite a while back):
Q: How do I have an interval displayed only in 'hours:minutes' instead of
the default 'days hours:minutes'
A: My solution is a function like this:
CREATE FUNCTION "to_hours" (interval )
RETURNS text
AS 'select date_part(''day'', $1)*24 + date_part(''hour'', $1) || '':'' ||
date_part(''min'', $1);'
LANGUAGE 'SQL'
This allows for the following:
SELECT someattribute, to_hours( sum(myinterval) ) from mytable group by
someattribue;
which is all I need. It works like I expected a built-in - at least for my
purposes.
It even works if the interval is larger than a year, but only because the
interval uses days as its largest unit.
DRAWBACKS:
1. This function WILL break, if intervals will happen to have a
'date_part('[week|month|year]', i)'
2. This function returns the minute part only as single digit if minutes<10
(130:7 instead of 130:07) which makes it harder to parse the output. But
then again if You need the output split, You could use date_part on the
original value.
It seems to me that Postgres already has all the code it needs, it is just
has to be put together.
If to_char(INTERVAL) makes it into any release of Postgres, I will change
my queries to use it :-)
Thanks for Your help,
Stefan
