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
