Re: PostgreSQL 7.0.2 Date Miscalculation

On Mon, Apr 02, 2001 at 07:52:42PM -0400, pgsql-bugs(at)postgresql(dot)org wrote:
> Jay Guerette (JayGuerette(at)pobox(dot)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 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)

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_sanity timestamp 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";

======================================================================