Thread
-
Bug in date_part()
phil@stimpy.netroedge.com — 2001-01-16T20:09:59Z
Query to reproduce the bug (7.0.3): edge=# select date_part('dow','9/1/2001'::date)::int4; ?column? ---------- 6 (1 row) edge=# select date_part('dow','4/1/2001'::date)::int4; ?column? ---------- 6 (1 row) Clearly, the first of April and September are not both on the same day of the week. I noticed this when using some web-based calendar software, which uses PostgreSQL as the backend, was displaying April 2001 wrong. Here's what cal says: # cal 2001 2001 January February March Su Mo Tu We Th Fr Sa Su Mo Tu We Th Fr Sa Su Mo Tu We Th Fr Sa 1 2 3 4 5 6 1 2 3 1 2 3 7 8 9 10 11 12 13 4 5 6 7 8 9 10 4 5 6 7 8 9 10 14 15 16 17 18 19 20 11 12 13 14 15 16 17 11 12 13 14 15 16 17 21 22 23 24 25 26 27 18 19 20 21 22 23 24 18 19 20 21 22 23 24 28 29 30 31 25 26 27 28 25 26 27 28 29 30 31 April May June Su Mo Tu We Th Fr Sa Su Mo Tu We Th Fr Sa Su Mo Tu We Th Fr Sa 1 2 3 4 5 6 7 1 2 3 4 5 1 2 8 9 10 11 12 13 14 6 7 8 9 10 11 12 3 4 5 6 7 8 9 15 16 17 18 19 20 21 13 14 15 16 17 18 19 10 11 12 13 14 15 16 22 23 24 25 26 27 28 20 21 22 23 24 25 26 17 18 19 20 21 22 23 29 30 27 28 29 30 31 24 25 26 27 28 29 30 July August September Su Mo Tu We Th Fr Sa Su Mo Tu We Th Fr Sa Su Mo Tu We Th Fr Sa 1 2 3 4 5 6 7 1 2 3 4 1 8 9 10 11 12 13 14 5 6 7 8 9 10 11 2 3 4 5 6 7 8 15 16 17 18 19 20 21 12 13 14 15 16 17 18 9 10 11 12 13 14 15 22 23 24 25 26 27 28 19 20 21 22 23 24 25 16 17 18 19 20 21 22 29 30 31 26 27 28 29 30 31 23 24 25 26 27 28 29 30 October November December Su Mo Tu We Th Fr Sa Su Mo Tu We Th Fr Sa Su Mo Tu We Th Fr Sa 1 2 3 4 5 6 1 2 3 1 7 8 9 10 11 12 13 4 5 6 7 8 9 10 2 3 4 5 6 7 8 14 15 16 17 18 19 20 11 12 13 14 15 16 17 9 10 11 12 13 14 15 21 22 23 24 25 26 27 18 19 20 21 22 23 24 16 17 18 19 20 21 22 28 29 30 31 25 26 27 28 29 30 23 24 25 26 27 28 29 30 31 Otherwise, I love PostgreSQL! An awesome backend, and knocks the socks off MySQL for anything SQL worthy. ;') Phil -- Philip Edelbrock -- IS Manager -- Edge Design, Corvallis, OR phil@netroedge.com -- http://www.netroedge.com/~phil PGP F16: 01 D2 FD 01 B5 46 F4 F0 3A 8B 9D 7E 14 7F FB 7A -
Re: Bug in date_part()
Thomas Lockhart <lockhart@alumni.caltech.edu> — 2001-01-17T16:13:11Z
> Query to reproduce the bug (7.0.3): > edge=# select date_part('dow','4/1/2001'::date)::int4; > ---------- > 6 > Clearly, the first of April ... is not on Saturday ... (and my app) was displaying April > 2001 wrong. I'm not seeing this on my 7.0.2 RPM installation, or on my from-cvs current sources (dow for April 1 comes up as zero, as you would expect). What machine are you running on, and how did you build? - Thomas -
Re: Bug in date_part()
Tom Lane <tgl@sss.pgh.pa.us> — 2001-01-17T17:13:20Z
phil@Stimpy.netroedge.com writes: > edge=# select date_part('dow','4/1/2001'::date)::int4; > ?column? > ---------- > 6 > (1 row) Seems to be fixed in current sources: regression=# select date_part('dow','4/1/2001'::date)::int4; ?column? ---------- 0 (1 row) I think this is a side-effect of the known 7.0 bug in date-to-timestamp conversion on DST transition days. Check out select '4/1/2001'::date::timestamp; regards, tom lane -
Re: Bug in date_part()
phil@stimpy.netroedge.com — 2001-01-17T20:27:04Z
Thanks for the reply Tom Lane, and Tom Lockhart. On my 7.0.3: phil=# select '4/1/2001'::date::timestamp; ?column? ------------------------ 2001-03-31 23:00:00-08 (1 row) This was built from source downloaded from the primary FTP site link on www.postgresql.org. [cartret@Stimpy signup]$ psql --version psql (PostgreSQL) 7.0.3 This machine is a redhat 5.0 based machine, although it's gone through a lot of software updates (mostly by hand). I have not tried the latest CVS/developer code, so perhaps it is fixed. Is there a chance that this bug is actually outside of postgresql? Like in a shared lib or something? Like I noted, this is an old RH install with updates, but very likely has some old libs and stuff on it. BTW- I've solved my issue by using a perl function to figure out the dow, so this follow up is purely for your assistance in squashing the bug if it hasn't already. Thanks! Phil On Wed, Jan 17, 2001 at 12:13:20PM -0500, Tom Lane wrote: > phil@Stimpy.netroedge.com writes: > > edge=# select date_part('dow','4/1/2001'::date)::int4; > > ?column? > > ---------- > > 6 > > (1 row) > > Seems to be fixed in current sources: > > regression=# select date_part('dow','4/1/2001'::date)::int4; > ?column? > ---------- > 0 > (1 row) > > > I think this is a side-effect of the known 7.0 bug in date-to-timestamp > conversion on DST transition days. Check out > > select '4/1/2001'::date::timestamp; > > regards, tom lane -- Philip Edelbrock -- IS Manager -- Edge Design, Corvallis, OR phil@netroedge.com -- http://www.netroedge.com/~phil PGP F16: 01 D2 FD 01 B5 46 F4 F0 3A 8B 9D 7E 14 7F FB 7A -
Re: Re: Bug in date_part()
Tom Lane <tgl@sss.pgh.pa.us> — 2001-01-18T02:07:34Z
Thomas Lockhart <lockhart@alumni.caltech.edu> writes: >> Query to reproduce the bug (7.0.3): >> edge=# select date_part('dow','4/1/2001'::date)::int4; >> ---------- >> 6 > I'm not seeing this on my 7.0.2 RPM installation, or on my from-cvs > current sources (dow for April 1 comes up as zero, as you would expect). I get the right answer from current sources, and the wrong one on 7.0.2. It's ye olde date-to-timestamp-off-an-hour-at-DST-boundary problem. Are you not running in a USA timezone? regards, tom lane -
Re: Bug in date_part()
Thomas Lockhart <lockhart@alumni.caltech.edu> — 2001-01-18T03:43:15Z
> I get the right answer from current sources, and the wrong one on 7.0.2. > It's ye olde date-to-timestamp-off-an-hour-at-DST-boundary problem. > Are you not running in a USA timezone? Oh right. I'm usually in GMT, which is a bad place to test time zone behavior, eh? - Thomas -
Re: Re: Bug in date_part()
phil@stimpy.netroedge.com — 2001-01-18T18:13:54Z
Ah, I think I understand the trouble now. It's good it's fixed for the next release. And, as predicted, I'm running in a DST zone (/etc/localtime -> ../usr/share/zoneinfo/US/Pacific) Thanks for the quick diagnosis! Phil On Wed, Jan 17, 2001 at 09:07:34PM -0500, Tom Lane wrote: > Thomas Lockhart <lockhart@alumni.caltech.edu> writes: > >> Query to reproduce the bug (7.0.3): > >> edge=# select date_part('dow','4/1/2001'::date)::int4; > >> ---------- > >> 6 > > > I'm not seeing this on my 7.0.2 RPM installation, or on my from-cvs > > current sources (dow for April 1 comes up as zero, as you would expect). > > I get the right answer from current sources, and the wrong one on 7.0.2. > It's ye olde date-to-timestamp-off-an-hour-at-DST-boundary problem. > Are you not running in a USA timezone? > > regards, tom lane -- Philip Edelbrock -- IS Manager -- Edge Design, Corvallis, OR phil@netroedge.com -- http://www.netroedge.com/~phil PGP F16: 01 D2 FD 01 B5 46 F4 F0 3A 8B 9D 7E 14 7F FB 7A