Re: PostgreSQL 7.0.2 Date Miscalculation

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

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