Bug #1178 week function broken
Submitted: 2 Sep 2003 6:13 Modified: 8 Dec 2003 6:51
Reporter: Christof Drescher Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S4 (Feature request)
Version:4.0.14 OS:Microsoft Windows (Windows,Linux)
Assigned to: Sergey Glukhov
Triage: D5 (Feature request)

[2 Sep 2003 6:13] Christof Drescher
On the current stable (!) releases, the week() function seems to be broken:

select week('2003-08-31',1) should return 35, and does!
select week('2003-08-31',0) should return 36, but returns 35!

which is totally bad, and affects also other functions like yearweek().

Function was ok in 3.23.53a (tested).

How to repeat:
enter sql commands

Suggested fix:
[5 Sep 2003 8:15] Boyd Gerber
I have verified the behavior.
[5 Sep 2003 10:30] Sergei Golubchik
well, it's not really a bug.

It is the question of what week is the first week of the year. WEEK(...,0) and WEEK(...,2) assume that the first week of the year is the first week that STARTS in this year - it's how people is US usually count weeks.

WEEK(...,1) and WEEK(...,3) assume that the week that contain January 1st is the first week, if more that 3 days of this week are in the new year - it's how weeks are numbered in Europe, and what ISO 8601: 1988 says.

Another difference is for WEEK(...,0) and WEEK(...,2) first day of the week is Sunday (again, US mode), and for WEEK(...,1) and WEEK(...,3) first weekday is Monday (Europe mode).

What we can do is to add different modes for "week starts on Monday" and "ISO 8601: 1988 mode". So there will be 4 more WEEK modes, where one can chose starting day, and numbering mode independently.
[5 Sep 2003 10:43] Christof Drescher
Hi Sergei,
I don't think your explanation is correct:
week(...,0) and week(...,1) actually differ in whether a week starts on Sunday or Monday. So for 2003-08-31, which is a sunday, week('2003-08-31',0) should always return a higher value than week('2003-08-31',1), since we start counting "the next week" one day earlier, i.e. on Sunday.

This was normal behavior before (at least 3.xx.xx), which sounded reasonable...
[5 Sep 2003 11:47] Sergei Golubchik
No, week('2003-08-31',0) will not ALWAYS return a higher value than week('2003-08-31',1)
it's because they have different notion of the "first week"

Try week('2003-01-01',0) and week('2003-01-01',1) to see the difference.

January 1st is considered the first week of the year 2003 by  week('2003-01-01',1), but the last week of the year 2002 by week('2003-01-01',0) !
[15 Sep 2003 0:35] Christof Drescher
Hi Sergei,
you are right in your example, but I think you miss the point: for a given SUNDAY, the ,0 and ,1 versions should - imho - give different results.

Anyway, there is definitely a difference between 3.x stable versions and the current 4.0.x version, which must be resolved. For me, it seems like a serious bug now.

[29 Sep 2003 9:03] [ name withheld ]
i have the same trouble (
worked just fine under my old version, and now i upgraded and i don't have what i want
for me sunday 28 september with a ,0 should return 40 was the way it worked in older version of mysql.and it return 39!
so select week('2003-09-28',0); return 39 and it should be 40 !
i tried ,1 ,2 ,3 and none sweet me !! i really want back my old ,0 !!!
[16 Oct 2003 4:05] Sergei Golubchik
As explained above, it's not a bug.

trying WEEK for the January 1st, and for any given date, one can see that WEEK works absolutely correctly, based on the notion of the "first week in the year" and "first day in the week". Still, it would be reasonable to have more fine control on how WEEK works, and be able to chose different "first week in the year" and "first day in the week" modes independently.
It's something that I added to our TODO.
[16 Oct 2003 7:35] Christof Drescher
Hi Sergei,
ok, sorry for my not reading your explanation thoroughly enough. I do now unterstand what you described, and I see what was the notion behind it - if I see it correctly, mysql suggests using the week(...,0) version for "normal use in US" and week(...,1) version for "normal use in Europe". A point which is fair enough.

I must say though that this definitely needed to be explained better in the manual, as it broke the behavior in 3.x versions (I know there was information in the changelog...). Actually, I guess most usage in the european area needed to be changed. I agree with you that more modes would be better, and it would have been best if the change had been NOT destroying old behavior (i.e. ,0 should have been the same as before...).

But hey - we learn... :-)

Thanx for your work!
[8 Dec 2003 6:51] Sergei Golubchik
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at

Additional info:

four new week modes are implemented now in 4.0.17
[19 Jan 2004 1:51] Gert Veltink
Linux 4.0.17

select week('2004-01-01'); 
results in the answer "0".

Is that intended behaviour?