Re: Formatting an Interval

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° ;-)