Lists: | pgsql-general |
---|
From: | "George Johnson" <gjohnson(at)jdsc(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | MySQL-esque sec_to_time() function |
Date: | 2000-12-06 16:27:56 |
Message-ID: | 002c01c05fa1$7d8deb00$0300a8c0@jdsc |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(at)jdsc(dot)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.
From: | "Francis Solomon" <francis(at)stellison(dot)co(dot)uk> |
---|---|
To: | "George Johnson" <gjohnson(at)jdsc(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | RE: MySQL-esque sec_to_time() function |
Date: | 2000-12-06 16:37:41 |
Message-ID: | NEBBIFFPELJMCJAODNPKMEJMCDAA.francis@stellison.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of George Johnson
Sent: 06 December 2000 16:28
To: pgsql-general(at)postgresql(dot)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(at)jdsc(dot)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.
From: | Zachary Beane <xach(at)xach(dot)com> |
---|---|
To: | George Johnson <gjohnson(at)jdsc(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: MySQL-esque sec_to_time() function |
Date: | 2000-12-06 16:43:33 |
Message-ID: | 20001206114333.C14297@xach.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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/