From: Tom Lane <tgl@sss.pgh.pa.us>
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: Sun, 16 Sep 2007 11:41:56 -0400
Message-ID: <6629.1189957316@sss.pgh.pa.us> (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 <andreak@officenet.no> writes:
> On Sunday 16 September 2007 13:14:27 Andreas Kretschmer wrote:
>> 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
There's no particular restriction to dates after 1970 there.
> solution which takes an arbitrary interval as input.
Well, you could subtract the two timestamps and then "extract(epoch ...)"
the resulting interval, but I think you'll get the very same answer.
[ pokes at it ... ] Hm, we seem to have an overflow problem in the
interval-to-epoch code for intervals exceeding 60-some years:
regression=# select extract(epoch from '2007-09-22 17:00'::timestamp - '1940-02-20 18:00'::timestamp);
date_part
------------
2132866800
(1 row)
regression=# select extract(epoch from '2007-09-22 17:00'::timestamp - '1930-02-20 18:00'::timestamp);
date_part
-------------
-1846567696
(1 row)
Looks pretty trivial to fix ...
regards, tom lane
inline patch 1: timestamp.c (+2/-2 lines) download inline-1.patchIndex: timestamp.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v
retrieving revision 1.181
diff -c -r1.181 timestamp.c
*** timestamp.c 4 Aug 2007 01:26:54 -0000 1.181
--- timestamp.c 16 Sep 2007 15:33:33 -0000
***************
*** 4395,4403 ****
#else
result = interval->time;
#endif
! result += (DAYS_PER_YEAR * SECS_PER_DAY) * (interval->month / MONTHS_PER_YEAR);
result += ((double) DAYS_PER_MONTH * SECS_PER_DAY) * (interval->month % MONTHS_PER_YEAR);
! result += interval->day * SECS_PER_DAY;
}
else
{
--- 4395,4403 ----
#else
result = interval->time;
#endif
! result += ((double) DAYS_PER_YEAR * SECS_PER_DAY) * (interval->month / MONTHS_PER_YEAR);
result += ((double) DAYS_PER_MONTH * SECS_PER_DAY) * (interval->month % MONTHS_PER_YEAR);
! result += ((double) SECS_PER_DAY) * interval->day;
}
else
{
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: tgl@sss.pgh.pa.us, andreak@officenet.no, akretschmer@spamfence.net
Subject: Re: Format intervall as hours/minutes etc
In-Reply-To: <6629.1189957316@sss.pgh.pa.us>
* 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