From: | Linda <lgray(at)unitrends(dot)com> |
---|---|
To: | Michael Glaesemann <grzm(at)myrealbox(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: question about postgresql time intervals |
Date: | 2006-03-20 14:44:38 |
Message-ID: | 200603200944.38496.lgray@unitrends.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Luuk Jansen | 2006-03-20 15:37:15 | Re: Updating in multiple tables |
Previous Message | Tino Wildenhain | 2006-03-20 14:34:30 | Re: Updating in multiple tables |