interval output format ?

David Pirotte <david@altosw.be> writes:

how can I ask postgres to return
43:18
instead of
@ 1 day 19 hours 18 mins

I think right now the only way is to convert the interval value to
seconds (date_part('epoch', interval)) and then format it yourself.
to_char() ought to have support for formatting intervals, but seems
not to at the moment.

regards, tom lane

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