Thread: Formatting an Interval
All of the functions that i've looked at seem to only extract parts from Intervals. What I need to do is to format the interval. For instance, I have a Interval with the value 1 day 07:57:52. I would like that in HH:MM:SS. So in the example the output would be 31:57:52. How can this be done?
On Fri, Dec 31, 2004 at 11:37:32AM -0500, Jamison Roberts wrote: > All of the functions that i've looked at seem to only extract parts > from Intervals. What I need to do is to format the interval. For > instance, I have a Interval with the value 1 day 07:57:52. I would > like that in HH:MM:SS. So in the example the output would be > 31:57:52. I'm not aware of a built-in way to get such a format; somebody please correct me if I'm mistaken. You could write a function to format the interval. For example, with PL/pgSQL you could use EXTRACT(epoch FROM interval_value) to convert the interval to a number of seconds; convert that to hours, minutes, and seconds; and use TO_CHAR to format the return value. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Fri, 2004-12-31 at 12:41 -0700, Michael Fuhr wrote: > On Fri, Dec 31, 2004 at 11:37:32AM -0500, Jamison Roberts wrote: > > > All of the functions that i've looked at seem to only extract parts > > from Intervals. What I need to do is to format the interval. For > > instance, I have a Interval with the value 1 day 07:57:52. I would > > like that in HH:MM:SS. So in the example the output would be > > 31:57:52. > > I'm not aware of a built-in way to get such a format; somebody > please correct me if I'm mistaken. > > You could write a function to format the interval. For example, > with PL/pgSQL you could use EXTRACT(epoch FROM interval_value) to > convert the interval to a number of seconds; convert that to hours, > minutes, and seconds; and use TO_CHAR to format the return value. to_char() works with standard date/time ranges, for example 1-24 -- so there is no way how convert to anything like "31:57:52". Karel -- Karel Zak <zakkr@zf.jcu.cz>
On Sun, Jan 02, 2005 at 05:19:23PM +0100, Karel Zak wrote: > On Fri, 2004-12-31 at 12:41 -0700, Michael Fuhr wrote: > > > You could write a function to format the interval. For example, > > with PL/pgSQL you could use EXTRACT(epoch FROM interval_value) to > > convert the interval to a number of seconds; convert that to hours, > > minutes, and seconds; and use TO_CHAR to format the return value. > > to_char() works with standard date/time ranges, for example 1-24 -- so > there is no way how convert to anything like "31:57:52". TO_CHAR() works with several types, including integer, numeric, and double precision. If you've broken the interval into three variables, one containing hours, one containing minutes, and one containing seconds, then you can use TO_CHAR() to add leading zeroes where needed. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
am Sun, dem 02.01.2005, um 17:19:23 +0100 mailte Karel Zak folgendes: > > You could write a function to format the interval. For example, > > with PL/pgSQL you could use EXTRACT(epoch FROM interval_value) to > > convert the interval to a number of seconds; convert that to hours, > > minutes, and seconds; and use TO_CHAR to format the return value. > > to_char() works with standard date/time ranges, for example 1-24 -- so > there is no way how convert to anything like "31:57:52". Read again. Extract the seconds and calculate the hours, minutes and seconds. test_db=# select extract (epoch from '1 day 07:57:52'::interval);date_part ----------- 115072 (1 Zeile) Okay, 115072 Seconds. test_db=# select extract (day from '1 day 07:57:52'::interval);date_part ----------- 1 (1 Zeile) Okay, this is 1 day, 24 hours. test_db=# select extract (epoch from '1 day 07:57:52'::interval) - 60*60*24*(extract (day from '1 day 07:57:52'::interval));?column? ---------- 28672 (1 Zeile) Okay, 24 hours and 28672 seconds, and you know, this is less then 1 day. Now calculate, how many hours in 28672 seconds: test_db=# select 28672 / 3600;?column? ---------- 7 (1 Zeile) Now you can add 24 hours and 7 hours, the remainder is test_db=# select 28672 - 3600*7;?column? ---------- 3472 (1 Zeile) seconds. Now you can calculate the minutes and, finaly, the seconds. Is the way now okay? Write a function for this job. Regards, Andreas -- Diese Message wurde erstellt mit freundlicher Unterstützung eines freilau- fenden Pinguins aus artgerechter Freilandhaltung. Er ist garantiert frei von Micro$oft'schen Viren. (#97922 http://counter.li.org) GPG 7F4584DA Was, Sie wissen nicht, wo Kaufbach ist? Hier: N 51.05082°, E 13.56889° ;-)
Note that there will be a loss of precision as an interval of 1 month, for instance, does not mean any specific number of days, as : 1 february + 1 month = 1 march (1 month = 28 or 29 days) 1 december + 1 month = 1 january (1 month = 31 days) Same for years etc.
Michael Fuhr wrote:
> On Fri, Dec 31, 2004 at 11:37:32AM -0500, Jamison Roberts wrote:
>
>
>>All of the functions that i've looked at seem to only extract parts
>>from Intervals. What I need to do is to format the interval. For
>>instance, I have a Interval with the value 1 day 07:57:52. I would
>>like that in HH:MM:SS. So in the example the output would be
>>31:57:52.
>
>
> I'm not aware of a built-in way to get such a format; somebody
> please correct me if I'm mistaken.
>
> You could write a function to format the interval. For example,
> with PL/pgSQL you could use EXTRACT(epoch FROM interval_value) to
> convert the interval to a number of seconds; convert that to hours,
> minutes, and seconds; and use TO_CHAR to format the return value.
>
Something like this, perhaps:
create or replace function interval_to_hms(interval)
returns text
language 'plpgsql'
as 'declare interval_sec integer; sec integer; min integer; hr integer;
begin select into interval_sec extract (epoch from $1); sec := interval_sec % 60; min :=
(interval_sec/60)%60; hr := (interval_sec/3600);
return hr || '':'' || to_char(min, ''FM00'') || '':'' ||
to_char(sec, ''FM00'');
end;';
--
Edmund Bacon <ebacon@onesystem.com>