Re: MySQL-esque sec_to_time() function

On Wed, Dec 06, 2000 at 08:27:56AM -0800, George Johnson wrote:
> Hello,
>
> I'm converting from MySQL to PostgreSQL (actually flipping/flopping
> back) and have a question:
>
> MySQL has a cool function sec_to_time() which converts your number
> of seconds to hh:mm:ss
>
> I've read thru 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 and come up with useless
> conversions.

You could implement sec_to_time as a SQL function:

create function sec_to_time(int4)
returns text
as '
select(to_char(''today''::timestamp + interval($1), ''HH24:MI:SS''))
' language 'sql';

However, this function would be limited to time spans of 24 hours.

Zach
--
xach(at)xach(dot)com Zachary Beane http://www.xach.com/