Date: Sun, 2 Jan 2005 17:46:52 +0100
From: Kretschmer Andreas <andreas_kretschmer@despammed.com>
To: List pgsql-sql <pgsql-sql@postgreSQL.org>
Subject: Re: Formatting an Interval
Message-ID: <20050102164652.GA8118@kaufbach.delug.de>
References: <238e52230412310837183fb730@mail.gmail.com>
	<20041231194118.GA23987@winnie.fuhr.org>
	<1104682763.3610.2.camel@petra>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <1104682763.3610.2.camel@petra>
User-Agent: Mutt/1.3.28i

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