Re: question about postgresql time intervals

On Friday 17 March 2006 10:20 pm, Michael Glaesemann wrote:
> 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
>
>
>

Hi, Michael

Thanks for the suggestion!  This approach will work on both older and newer
version of PostgreSQL.

Thanks,
Linda

--
Linda Gray
Unitrends Corporation
803.454.0300 ext. 241