I've been having some email problems, so my apologies if this is a
duplicate.
On Mar 16, 2006, at 22:49 , Linda wrote:
Thanks for your reply. I guess you missed the original email. I
have an
application that is retrieving "uptime" (an integer number of
seconds since
reboot) and recasting it as varchar and then interval type.
Rather than perform this cast, you might want to make your own
function to handle this. Here are a couple (one in PL/pgSQL, the
other SQL). You should be able to use these functions any relatively
modern PostgreSQL installation.
(I find the x_int * interval some_int construct a bit cleaner than
forcing a cast as well.)
create or replace function secs_to_interval(integer)
returns interval
strict
immutable
language plpgsql as '
declare
secs alias for $1;
secs_per_day constant integer default 86400;
begin
return secs / secs_per_day * interval ''1 day'' + secs %
secs_per_day * interval ''1 second'';
end;
';
create or replace function secs_to_interval_sql(integer) returns
interval
strict
immutable
language sql as '
select $1 / 86400 * interval ''1 day'' + $1 % 86400 * interval ''1
second'';
';
test=# select secs_to_interval(1824459), secs_to_interval_sql(1824459);
secs_to_interval | secs_to_interval_sql
------------------+----------------------
21 days 02:47:39 | 21 days 02:47:39
(1 row)
test=# select secs_to_interval(86400), secs_to_interval_sql(86400);
secs_to_interval | secs_to_interval_sql
------------------+----------------------
1 day | 1 day
(1 row)
test=# select secs_to_interval(302), secs_to_interval_sql(302);
secs_to_interval | secs_to_interval_sql
------------------+----------------------
00:05:02 | 00:05:02
(1 row)
test=# select secs_to_interval(1824459 * 2), secs_to_interval_sql
(1824459 * 2);
secs_to_interval | secs_to_interval_sql
------------------+----------------------
42 days 05:35:18 | 42 days 05:35:18
(1 row)
Hope this helps.
Michael Glaesemann
grzm myrealbox com
