Thread: PostgreSQL 7.0.2 Date Miscalculation
Jay Guerette (JayGuerette@pobox.com) reports a bug with a severity of 2
The lower the number the more severe it is.
Short Description
PostgreSQL 7.0.2 Date Miscalculation
Long Description
PostgreSQL 7.0.2
The RELTIME function is miscalculating dates.
(all my graphs were wrong today!)
Sample Code
The query:
SELECT STAMP FROM SYSLOG WHERE DATE(STAMP)=DATE(TIMESTAMP('TODAY'-'1 WEEK'::RELTIME)) LIMIT 1;
Produces:
stamp
------------------------
2001-03-25 02:53:52-05
(1 row)
When the date is:
Mon Apr 2 19:45:40 EDT 2001
And the result SHOULD be:
stamp
------------------------
2001-03-26 02:53:52-05
I also have NO idea what this means:
SELECT DATE(CURRENT_DATE - ('1 WEEK'::RELTIME));
date
------------
0345-05-14
(1 row)
No file was uploaded with this report
On Mon, Apr 02, 2001 at 07:52:42PM -0400, pgsql-bugs@postgresql.org wrote:
> Jay Guerette (JayGuerette@pobox.com) reports a bug with a severity of 2
> The lower the number the more severe it is.
>
> Short Description
> PostgreSQL 7.0.2 Date Miscalculation
>
> Long Description
> PostgreSQL 7.0.2
>
> The RELTIME function is miscalculating dates.
> (all my graphs were wrong today!)
>
> Sample Code
> The query:
>
> SELECT STAMP FROM SYSLOG WHERE DATE(STAMP)=DATE(TIMESTAMP('TODAY'-'1 WEEK'::RELTIME)) LIMIT 1;
>
> Produces:
>
> stamp
> ------------------------
> 2001-03-25 02:53:52-05
> (1 row)
>
> When the date is:
>
> Mon Apr 2 19:45:40 EDT 2001
>
> And the result SHOULD be:
>
> stamp
> ------------------------
> 2001-03-26 02:53:52-05
>
> I also have NO idea what this means:
>
> SELECT DATE(CURRENT_DATE - ('1 WEEK'::RELTIME));
> date
> ------------
> 0345-05-14
> (1 row)
>
>
> No file was uploaded with this report
After the daylight savings time change I have had similar failures with 7.1RC2 and the latest CVS of 7.1. It failed on
bothtimestamp and horology regression tests. This happened on both my RH7.0 and OpenBSD 2.8 (RH7.0 on an Athlon and BSD
ona P3 laptop)
If I changed the system clock back before the DST change... it works fine.
Setting TZ didn't help:
export TZ=CST6CDT
On both platforms, postgresql was compiled with:
./configure --enable-syslog
gmake
gmake check
I checked the CVS version on both platforms and the RC2 on RH7, only.
Here is the snippet of regression.out:
parallel group (18 tests): point lseg box polygon path circle time abstime interval tinterval inet reltime comments
type_sanitytimestamp date oidjoins opr_sanity
point ... ok
lseg ... ok
box ... ok
path ... ok
polygon ... ok
circle ... ok
date ... ok
time ... ok
timestamp ... FAILED
interval ... ok
abstime ... ok
reltime ... ok
tinterval ... ok
inet ... ok
comments ... ok
oidjoins ... ok
type_sanity ... ok
opr_sanity ... ok
test geometry ... ok
test horology ... FAILED
test create_function_1 ... ok
test create_type ... ok
test create_table ... ok
test create_function_2 ... ok
test copy ... ok
And here is the output of regression.diff:
*** ./expected/timestamp.out Mon Apr 2 16:48:50 2001
--- ./results/timestamp.out Mon Apr 2 17:06:58 2001
***************
*** 7,13 ****
SELECT (timestamp 'today' = (timestamp 'yesterday' + interval '1 day')) as "True";
True
------
! t
(1 row)
SELECT (timestamp 'today' = (timestamp 'tomorrow' - interval '1 day')) as "True";
--- 7,13 ----
SELECT (timestamp 'today' = (timestamp 'yesterday' + interval '1 day')) as "True";
True
------
! f
(1 row)
SELECT (timestamp 'today' = (timestamp 'tomorrow' - interval '1 day')) as "True";
***************
*** 19,25 ****
SELECT (timestamp 'tomorrow' = (timestamp 'yesterday' + interval '2 days')) as "True";
True
------
! t
(1 row)
SELECT (timestamp 'current' = 'now') as "True";
--- 19,25 ----
SELECT (timestamp 'tomorrow' = (timestamp 'yesterday' + interval '2 days')) as "True";
True
------
! f
(1 row)
SELECT (timestamp 'current' = 'now') as "True";
***************
*** 87,93 ****
SELECT count(*) AS one FROM TIMESTAMP_TBL WHERE d1 = timestamp 'today' - interval '1 day';
one
-----
! 1
(1 row)
SELECT count(*) AS one FROM TIMESTAMP_TBL WHERE d1 = timestamp 'now';
--- 87,93 ----
SELECT count(*) AS one FROM TIMESTAMP_TBL WHERE d1 = timestamp 'today' - interval '1 day';
one
-----
! 0
(1 row)
SELECT count(*) AS one FROM TIMESTAMP_TBL WHERE d1 = timestamp 'now';
======================================================================
*** ./expected/horology.out Mon Apr 2 16:48:49 2001
--- ./results/horology.out Mon Apr 2 17:06:59 2001
***************
*** 122,128 ****
SELECT time with time zone '01:30' + interval '02:01' AS "03:31:00-08";
03:31:00-08
-------------
! 03:31:00-08
(1 row)
SELECT time with time zone '01:30-08' - interval '02:01' AS "23:29:00-08";
--- 122,128 ----
SELECT time with time zone '01:30' + interval '02:01' AS "03:31:00-08";
03:31:00-08
-------------
! 03:31:00-07
(1 row)
SELECT time with time zone '01:30-08' - interval '02:01' AS "23:29:00-08";
***************
*** 140,146 ****
SELECT time with time zone '03:30' + interval '1 month 04:01' AS "07:31:00-08";
07:31:00-08
-------------
! 07:31:00-08
(1 row)
SELECT interval '04:30' - time with time zone '01:02' AS "+03:28";
--- 140,146 ----
SELECT time with time zone '03:30' + interval '1 month 04:01' AS "07:31:00-08";
07:31:00-08
-------------
! 07:31:00-07
(1 row)
SELECT interval '04:30' - time with time zone '01:02' AS "+03:28";
======================================================================
Robert Hentosh <hentosh@io.com> writes: > After the daylight savings time change I have had similar failures > with 7.1RC2 and the latest CVS of 7.1. It failed on both timestamp > and horology regression tests. This happened on both my RH7.0 and > OpenBSD 2.8 (RH7.0 on an Athlon and BSD on a P3 laptop) Hm. The timestamp diffs are an expected behavior near DST transition days --- see http://www.postgresql.org/devel-corner/docs/postgres/regress.html#AEN14359 However, the horology diffs are not, and I can't reproduce them here. Did anyone else see that? regards, tom lane
At 00:59 3/04/01 -0400, Tom Lane wrote:
>
>However, the horology diffs are not, and I can't reproduce them here.
>Did anyone else see that?
>
I've just started seeing both...
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
On Tue, Apr 03, 2001 at 12:59:31AM -0400, Tom Lane wrote: > However, the horology diffs are not, and I can't reproduce them here. > Did anyone else see that? me too (the problem started in these days) ciao, andrea
Philip Warner <pjw@rhyme.com.au> writes:
> At 00:59 3/04/01 -0400, Tom Lane wrote:
>> However, the horology diffs are not, and I can't reproduce them here.
>> Did anyone else see that?
> I've just started seeing both...
What is the date of the nearest daylight-savings transition in your
timezone?
Wait a minute ... considering that the regress tests run in PST8PDT,
your local timezone shouldn't make a difference. Maybe a platform-
specific issue? What platform (esp. which C library) do you use?
FWIW, as of this morning I'm back to no failure on timestamp test
(as expected), and still no horology failure either.
regards, tom lane
Here is a bit more information on this date type problem.
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
[PostgreSQL 7.0.2 on i386-unknown-freebsdelf3.4, compiled by gcc 2.7.2.3]
type \? for help on slash commands
type \q to quit
type \g or terminate with semicolon to execute query
You are currently connected to the database: darcy
darcy=> select date_part('dow','april 1, 2001'::date);
date_part
---------
6
(1 row)
darcy=> select date_part('dow','april 2, 2001'::date);
date_part
---------
1
(1 row)
darcy=>\q
At 10:17 AM 4/3/01 -0400, Tom Lane wrote:
>Philip Warner <pjw@rhyme.com.au> writes:
>> At 00:59 3/04/01 -0400, Tom Lane wrote:
>>> However, the horology diffs are not, and I can't reproduce them here.
>>> Did anyone else see that?
>
>> I've just started seeing both...
>
>What is the date of the nearest daylight-savings transition in your
>timezone?
>
>Wait a minute ... considering that the regress tests run in PST8PDT,
>your local timezone shouldn't make a difference. Maybe a platform-
>specific issue? What platform (esp. which C library) do you use?
>
>FWIW, as of this morning I'm back to no failure on timestamp test
>(as expected), and still no horology failure either.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
> > The RELTIME function is miscalculating dates.
> > (all my graphs were wrong today!)
Just an aside: INTERVAL is the preferred type for, uh, intervals.
RELTIME is used internally for historical reasons. In particular,
INTERVAL maintains the distinction between qualitative units such as
months and years, while RELTIME assumes a 30 day month and 365 day year
*always*.
But for your example that does not make a difference...
> > stamp
> > ------------------------
> > 2001-03-25 02:53:52-05
> > When the date is:
> > Mon Apr 2 19:45:40 EDT 2001
> > And the result SHOULD be:
> > ------------------------
> > 2001-03-26 02:53:52-05
Should be fixed in current sources (and the upcoming 7.1 release).
> > I also have NO idea what this means:
> > SELECT DATE(CURRENT_DATE - ('1 WEEK'::RELTIME));
> > ------------
> > 0345-05-14
Whoops. Still a problem even in current sources, probably related to
changes to help with time zone manipulation. There is an internal units
mismatch between DATE and RELTIME. Use INTERVAL instead.
> If I changed the system clock back before the DST change... it works fine.
> --- ./results/horology.out Mon Apr 2 17:06:59 2001
> SELECT time with time zone '01:30' + interval '02:01' AS "03:31:00-08";
> 03:31:00-08
> -------------
> ! 03:31:00-07
Hmm. This is just a badly designed regression test (I can say that,
since it is probably mine ;)
I was trying to exercise TIME WITH TIME ZONE with the *implicit* time
zone for today. That really won't work in a testable way, since the
result varies during the year :(
This illustrates a fundamental problem with the SQL9x TIME WITH TIME
ZONE type, which carries no date info for context. And they have no
"date with time zone", which except for a few hours a year might be more
helpful. imho TIMESTAMP is to be preferred in most cases.
- Thomas
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
>> --- ./results/horology.out Mon Apr 2 17:06:59 2001
>> SELECT time with time zone '01:30' + interval '02:01' AS "03:31:00-08";
>> 03:31:00-08
>> -------------
>> ! 03:31:00-07
> Hmm. This is just a badly designed regression test (I can say that,
> since it is probably mine ;)
> I was trying to exercise TIME WITH TIME ZONE with the *implicit* time
> zone for today. That really won't work in a testable way, since the
> result varies during the year :(
What I'm curious about is why I'm not seeing a failure on HPUX. If your
explanation is right then this test should fail everywhere during
daylight savings season.
regards, tom lane
Tom Lane wrote:
>
> Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> >> --- ./results/horology.out Mon Apr 2 17:06:59 2001
> >> SELECT time with time zone '01:30' + interval '02:01' AS "03:31:00-08";
> >> 03:31:00-08
> >> -------------
> >> ! 03:31:00-07
>
> > Hmm. This is just a badly designed regression test (I can say that,
> > since it is probably mine ;)
>
> > I was trying to exercise TIME WITH TIME ZONE with the *implicit* time
> > zone for today. That really won't work in a testable way, since the
> > result varies during the year :(
> What I'm curious about is why I'm not seeing a failure on HPUX. If your
> explanation is right then this test should fail everywhere during
> daylight savings season.
Well, we won't hold up HPUX as a model for "standard behavior", eh? But
I'm not sure why you don't see the behavior. afaik the calculations
involved should be something like (haven't looked it up, but...):
1) interpret TIME WITH TIME ZONE '01:30' as the time with the time zone
appropriate for that hour today. Convert to internal representation as a
time field with an explicit numeric time zone value.
2) interpret INTERVAL '02:01' as an interval. No month/year fields, and
no time zone involved.
3) Add the interval to the time. Both are in units of seconds
internally.
4) Store the time field modulo 86400, pushing it back into a 24 hour
range. Store the time zone field from step (1) into the result.
5) Print result, using only the internal time zone offset.
- Thomas
Hi Jay,
Which OS are you using? Mandrake-Linux 7.2 is known to have bugs in the
version of PostgreSQL they supply as RPM's.
Regards and best wishes,
Justin Clift
pgsql-bugs@postgresql.org wrote:
>
> Jay Guerette (JayGuerette@pobox.com) reports a bug with a severity of 2
> The lower the number the more severe it is.
>
> Short Description
> PostgreSQL 7.0.2 Date Miscalculation
>
> Long Description
> PostgreSQL 7.0.2
>
> The RELTIME function is miscalculating dates.
> (all my graphs were wrong today!)
>
> Sample Code
> The query:
>
> SELECT STAMP FROM SYSLOG WHERE DATE(STAMP)=DATE(TIMESTAMP('TODAY'-'1 WEEK'::RELTIME)) LIMIT 1;
>
> Produces:
>
> stamp
> ------------------------
> 2001-03-25 02:53:52-05
> (1 row)
>
> When the date is:
>
> Mon Apr 2 19:45:40 EDT 2001
>
> And the result SHOULD be:
>
> stamp
> ------------------------
> 2001-03-26 02:53:52-05
>
> I also have NO idea what this means:
>
> SELECT DATE(CURRENT_DATE - ('1 WEEK'::RELTIME));
> date
> ------------
> 0345-05-14
> (1 row)
>
> No file was uploaded with this report
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi
Justin,
I am using the Redhat 7.0 distribution, updated with a 2.2.18 kernel.
> Hi Jay,
>
> Which OS are you using? Mandrake-Linux 7.2 is known to have bugs in
> the version of PostgreSQL they supply as RPM's.
>
> Regards and best wishes,
>
> Justin Clift
>
> pgsql-bugs@postgresql.org wrote:
>>
>> Jay Guerette (JayGuerette@pobox.com) reports a bug with a severity of
>> 2 The lower the number the more severe it is.
>>
>> Short Description
>> PostgreSQL 7.0.2 Date Miscalculation
>>
>> Long Description
>> PostgreSQL 7.0.2
>>
>> The RELTIME function is miscalculating dates.
>> (all my graphs were wrong today!)
>>
>> Sample Code
>> The query:
>>
>> SELECT STAMP FROM SYSLOG WHERE DATE(STAMP)=DATE(TIMESTAMP('TODAY'-'1
>> WEEK'::RELTIME)) LIMIT 1;
>>
>> Produces:
>>
>> stamp
>> ------------------------
>> 2001-03-25 02:53:52-05
>> (1 row)
>>
>> When the date is:
>>
>> Mon Apr 2 19:45:40 EDT 2001
>>
>> And the result SHOULD be:
>>
>> stamp
>> ------------------------
>> 2001-03-26 02:53:52-05
>>
>> I also have NO idea what this means:
>>
>> SELECT DATE(CURRENT_DATE - ('1 WEEK'::RELTIME));
>> date
>> ------------
>> 0345-05-14
>> (1 row)
>>
>> No file was uploaded with this report
>>
>> ---------------------------(end of
>> broadcast)--------------------------- TIP 4: Don't 'kill -9' the
>> postmaster
>
> --
> "My grandfather once told me that there are two kinds of people: those
> who work and those who take the credit. He told me to try to be in the
> first group; there was less competition there."
> - Indira Gandhi