Thread
-
PostgreSQL 7.0.2 Date Miscalculation
PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2001-04-02T23:52:42Z
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 -
Re: PostgreSQL 7.0.2 Date Miscalculation
Robert Hentosh <hentosh@io.com> — 2001-04-03T04:35:43Z
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 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"; ====================================================================== -
Re: PostgreSQL 7.0.2 Date Miscalculation
Tom Lane <tgl@sss.pgh.pa.us> — 2001-04-03T04:59:31Z
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
-
Re: PostgreSQL 7.0.2 Date Miscalculation
Philip Warner <pjw@rhyme.com.au> — 2001-04-03T06:31:57Z
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 |/ -
Re: PostgreSQL 7.0.2 Date Miscalculation
andrea gelmini <bungle@linux.it> — 2001-04-03T09:25:47Z
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
-
Re: PostgreSQL 7.0.2 Date Miscalculation
Tom Lane <tgl@sss.pgh.pa.us> — 2001-04-03T14:17:20Z
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
-
Re: PostgreSQL 7.0.2 Date Miscalculation
Thomas Lockhart <lockhart@alumni.caltech.edu> — 2001-04-03T15:52:45Z
> > 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 -
Re: Re: PostgreSQL 7.0.2 Date Miscalculation
Tom Lane <tgl@sss.pgh.pa.us> — 2001-04-03T16:08:06Z
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
-
Re: Re: PostgreSQL 7.0.2 Date Miscalculation
Thomas Lockhart <lockhart@alumni.caltech.edu> — 2001-04-03T16:48:52Z
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 -
Re: PostgreSQL 7.0.2 Date Miscalculation
Justin Clift <jclift@iprimus.com.au> — 2001-04-07T04:52:26Z
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 -
Re: PostgreSQL 7.0.2 Date Miscalculation
Jay Guerette <jayguerette@pobox.com> — 2001-04-07T13:59:48Z
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