Re: question about postgresql time intervals

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