Обсуждение: Bug in date_part()
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
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
> 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
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
> 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
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
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