Thread: interval output format ?

Hello,

Given the following table:

pilotage=# \d vols
           Table "vols"
  Attribute  |   Type   | Modifier
-------------+----------+----------
 jour        | date     |
 modele      | char(20) |
 matricule   | char(20) |
 pilote1     | char(20) |
 pilote2     | char(20) |
 origine     | char(20) |
 destination | char(20) |
 double      | interval |
 comandant   | interval |
 description | text     |
 code        | char(10) |


and the following query:

    pilotage=# select sum(comandant) from vols;
               sum
    --------------------------
     @ 1 day 19 hours 18 mins
    (1 row)

how can I ask postgres  to return

    43:18

instead of

    @ 1 day 19 hours 18 mins

Thanks,
David

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