MySQL-esque sec_to_time() function

Hi George,

Difference of two timestamps directly :

dbtest=# select 'now'::timestamp - '2000-12-06 13:47:57+00'::timestamp
as "Time Interval";
Time Interval
---------------
02:49:34
(1 row)

Number of seconds converted to hh:mm:ss :

dbtest=# select '12345 seconds'::interval as "Time Interval";
Time Interval
---------------
03:25:45
(1 row)

Hope this helps

Francis Solomon

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of George Johnson
Sent: 06 December 2000 16:28
To: pgsql-general@postgresql.org
Subject: [GENERAL] MySQL-esque sec_to_time() function

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.

Thanks,
George Johnson
gjohnson@jdsc.com

PS: i can't find documentation on how to load the functions in contrib.
I can compile and
install them O.K., but not sure how to make them load.

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@xach.com Zachary Beane http://www.xach.com/