Re: Re: Interval output format

Just for the records, in case someone has the same problem:

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

At 14:29 19.01.2001 +0100, Karel Zak wrote:

> > "Stefan Waidele jun." <St(dot)Waidele(dot)jun(at)Krone-Neuenburg(dot)de> writes:
> > > At 13:37 18.01.2001 -0500, Tom Lane wrote:
> > > How can I tell Postgres to return an interval value in an format like
> > >> hhh:mm?
> > >> [...]
> > does this seem like it should be added for 7.2? (Too late for
> > 7.1, I think.)
>
> For 7.1 it's really too late. For 7.2 I will speculate about it - first
>I must explore intervals inwards.
>
> Karel