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° ;-)
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
