From: Shane Ambler <pgsql@Sheeky.Biz>
To: Andreas Joseph Krogh <andreak@officenet.no>
Cc: pgsql-sql@postgresql.org, Andreas Kretschmer <akretschmer@spamfence.net>
Subject: Re: Format intervall as hours/minutes etc
Date: Mon, 17 Sep 2007 02:15:51 +0930
Message-ID: <46ED5DBF.1030303@Sheeky.Biz> (raw)
In-Reply-To: <200709161327.16989.andreak@officenet.no>
References: <200709161300.53257.andreak@officenet.no>
<20070916111427.GA13886@KanotixBox>
<200709161327.16989.andreak@officenet.no>
Andreas Joseph Krogh wrote:
> On Sunday 16 September 2007 13:14:27 Andreas Kretschmer wrote:
>> Andreas Joseph Krogh <andreak@officenet.no> schrieb:
>>> Hi all. Any hint on how to format this interval as number of hour/seconds
>>> etc? select age('2007-09-22 17:00'::timestamp, '2000-02-20
>>> 18:00'::timestamp); age
>>> -------------------------------
>>> 7 years 7 mons 1 day 23:00:00
>> You can use extract(epoch, from ...) like this:
>>
>> test=*# select extract(epoch from '2007-09-22 17:00'::timestamp) - extract
>> (epoch from '2000-02-20 18:00'::timestamp); ?column?
>> -----------
>> 239407200
>> (1 row)
>>
>> Now you can calculate the hours and so on.
>
> Yes, this works fine for dates >= 1970, but I'm looking for a more general
> solution which takes an arbitrary interval as input. The reason why I'm using
> PG to calculate this is 'cause it takes 25/23 hour days, leapyears etc. into
> account when calculating intervals.
Is that all you use it for?? ;-)
You may want to add the timezone to get the effect of daylight savings.
postgres=# select age('2007-03-25 7:00:00'::timestamptz, '2007-03-25
1:00:00'::timestamptz);
age
----------
06:00:00
(1 row)
postgres=# select age('2007-03-25 7:00:00+9:30'::timestamptz,
'2007-03-25 1:00:00+9:30'::timestamptz);
age
----------
05:00:00
(1 row)
I haven't used intervals much so I may be missing something.
I get the idea you want the interval to be expressed as 2,765 days and
23 hours or 66,383 hours, which I think would be useful (more so for
shorter intervals).
I am thinking the exact function you are after isn't there - from what I
can find a larger interval is always given as x years y months z days...
which is why extracting the epoch is the easiest point to start your calcs.
Maybe this can be a feature request - functions to give an interval in
total number of days/hours/minutes instead of years months days
--
Shane Ambler
pgSQL@Sheeky.Biz
Get Sheeky @ http://Sheeky.Biz
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: pgsql@Sheeky.Biz, andreak@officenet.no, akretschmer@spamfence.net
Subject: Re: Format intervall as hours/minutes etc
In-Reply-To: <46ED5DBF.1030303@Sheeky.Biz>
* 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