sec_to_time() function in PostgreSQL?

Question

MySQL has a cool function sec_to_time() which converts your number of seconds to hh:mm:ss

I've read through the mailing lists and am basically trying to implement the following:

MySQL:
select sec_to_time(sum(unix_timestamp(enddate) - unix_timestamp(startdate))) from foo;

PostgreSQL:
select XXX(sum(date_part('epoch',enddate) - date_part('epoch',startdate))) from foo;

I just need to know what XXX is/can be. I've tried a lot of combinations of the documented functions .

Please let how to do this in PostgreSQL?

Answer 1

Use to_char:

regress=# SELECT to_char( (9999999 ||' seconds')::interval, 'HH24:MM:SS' );
  to_char   
------------
 2777:00:39
(1 row)

Here's a function that produces a text formatted value:

CREATE OR REPLACE FUNCTION sec_to_time(bigint) RETURNS text AS $$
SELECT to_char( ($1|| ' seconds')::interval, 'HH24:MI:SS');
$$ LANGUAGE 'SQL' IMMUTABLE;

eg:

regress=# SELECT sec_to_time(9999999);
 sec_to_time 
-------------
 2777:00:39
(1 row)

If you'd prefer an INTERVAL result, use:

CREATE OR REPLACE FUNCTION sec_to_time(bigint) RETURNS interval AS $$
SELECT justify_interval( ($1|| ' seconds')::interval);
$$ LANGUAGE 'SQL' IMMUTABLE;

... which will produce results like:

SELECT sec_to_time(9999999);
       sec_to_time       
-------------------------
 3 mons 25 days 17:46:39
(1 row)

Don't cast an INTERVAL to TIME though; it'll discard the days part. Use to_char(theinterval, 'HH24:MI:SS) to convert it to text without truncation instead.