From: Kretschmer Andreas <andreas_kretschmer@despammed.com>
To: List pgsql-sql <pgsql-sql@postgreSQL.org>
Subject: Re: Formatting an Interval
Date: Sun, 2 Jan 2005 17:46:52 +0100
Message-ID: <20050102164652.GA8118@kaufbach.delug.de> (raw)
In-Reply-To: <1104682763.3610.2.camel@petra>
References: <238e52230412310837183fb730@mail.gmail.com>
<20041231194118.GA23987@winnie.fuhr.org>
<1104682763.3610.2.camel@petra>
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° ;-)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreas_kretschmer@despammed.com
Subject: Re: Formatting an Interval
In-Reply-To: <20050102164652.GA8118@kaufbach.delug.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox