MySQL Bugs: #66178: STR_TO_DATE and YEARWEEK inconsistencies

Bug #66178	STR_TO_DATE and YEARWEEK inconsistencies
Submitted:	3 Aug 2012 7:27	Modified:	23 Dec 2014 12:42
Reporter:	Indrek Altpere	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.5.16, 5.5.25, 5.1.36, 5.5.28	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
YEARWEEK and STR_TO_DATE behave inconsistently.
From documentation, yearweek mode parameter is same as for WEEK()
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_week
Current target of interest is mode 7:
Mode=7, Start of week=Monday, Range=1-53, Week 1 is the first week with a Monday in this year

STR_TO_DATE format parameter should be according to DATE_FORMAT() values
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format
Target of interest are %x %v and %W:
%x: Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%v: Week (01..53), where Monday is the first day of the week; used with %x
%W: Weekday name (Sunday..Saturday)

But, in the year of 2013, those rules do not seem to apply anymore, unless I'm missing some vital information.

How to repeat:
SELECT YEARWEEK("2013-03-03", 7);
returns "201308"
SELECT STR_TO_DATE("201308Sunday", "%x%v%W");
returns "2013-02-24"
SELECT YEARWEEK("2013-02-24", 7);
returns "201307"
And so on, always 1 week shifted.

Root of the cause can also be seen directly like this:
SELECT STR_TO_DATE("201301Monday", "%x%v%W");
returns "2012-12-31"
According to documentation, the week number should refer to the the first week that has monday in this year, "2012-12-31" is indeed monday, but it is NOT year 2013.

SELECT YEARWEEK("2012-12-31", 7);
returns "201253"

Suggested fix:
Since I'm not a c coder myself, I cannot suggest any fix. Not sure if there is a simple workaround on mysql side, I myself had to implement workaround in PHP code to process results.

Thank you for the bug report. Verified with current mysql-5.5:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.28-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT YEARWEEK("2013-03-03", 7);
+---------------------------+
| YEARWEEK("2013-03-03", 7) |
+---------------------------+
|                    201308 |
+---------------------------+
1 row in set (0.01 sec)

mysql> SELECT STR_TO_DATE("201308Sunday", "%x%v%W");
+---------------------------------------+
| STR_TO_DATE("201308Sunday", "%x%v%W") |
+---------------------------------------+
| 2013-02-24                            |
+---------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT YEARWEEK("2013-02-24", 7);
+---------------------------+
| YEARWEEK("2013-02-24", 7) |
+---------------------------+
|                    201307 |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT STR_TO_DATE("201301Monday", "%x%v%W");
+---------------------------------------+
| STR_TO_DATE("201301Monday", "%x%v%W") |
+---------------------------------------+
| 2012-12-31                            |
+---------------------------------------+
1 row in set (0.00 sec)

This is not a bug. It is behaving as it is supposed to. 

The original submitter is misunderstanding the documentation. The documentation could also be more precise. It should reference the ISO-8601 standard, from where these definitions are taken for the %v, %V, %x, and %X values.

Here is how this works. The %x week is an ordinal (1st, 2nd, etc) week of the year. Using %x, the week will be defined as starting on a Monday and ending on a Sunday. This is in contrast to %X, which will also return an ordinal week of the year, but will define a week as starting on a Sunday and ending on a Saturday.

It gets tricky with the year. According to the ISO-8601 standard, the year to which a week belongs to is the year in which most of the days of that week fall. In the case of 201301, 1 day (2012-12-31) falls in 2012 and six days fall within 2013, and hence the week will fall into 2013. It does not matter whether the Monday (for %x) or Sunday (for %X) falls in 2012.

So, in essence, there is no way to easily convert the result of YEARWEEK("2013-03-03", 7) correctly back to date format?
In that case, the documentation for DATE_FORMAT should be improved to define what is considered first week of year exactly and perhaps show this exact case that results in false values?

I am not qualified to tell you that there is no way to get the inverse function of YEARWEEK() in mode 7. I do not see any week designators in STR_TO_DATE which would use the "with a Monday in this year" test to determine year membership. Perhaps there is another way, but I am not familiar with it.

I can tell you, however, that STR_TO_DATE can be used to turn the result of YEARWEEK back into the date for YEARWEEK modes 1,3,4, and 6. These modes use the "majority of the week in a given year" method of determining which year a week belongs to, which is the same as ISO-8601 describes. You will need to use the correct STR_TO_DATE week designator (%u, %U, %v, or %V) to get the week number, and the appropriate year designator %x or %X, depending on whether you want your weeks starting on a Monday or a Sunday. 

I agree that the DATE_FORMAT documentation should either reference the ISO-8601 standard in the %u, %U, %v, %V, %x and %X parameters, or describe that the week membership in a year is determined by the number of days of that week which fall in the given year.

[18 Dec 2014 22:44] Mithun C Y

As per user manual:

%U Week (00..53), where Sunday is the first day of the week; WEEK() mode 0
%u Week (00..53), where Monday is the first day of the week; WEEK() mode 1
%V Week (01..53), where Sunday is the first day of the week; WEEK() mode 2;
used with %X
%v Week (01..53), where Monday is the first day of the week; WEEK() mode 3;
used with %x

Mode 	First day of week 	Range 	Week 1 is the first week ???
0 	Sunday 	0-53 	with a Sunday in this year
1 	Monday 	0-53 	with 4 or more days this year
2 	Sunday 	1-53 	with a Sunday in this year
3 	Monday 	1-53 	with 4 or more days this year
4 	Sunday 	0-53 	with 4 or more days this year
5 	Monday 	0-53 	with a Monday in this year
6 	Sunday 	1-53 	with 4 or more days this year
7 	Monday 	1-53 	with a Monday in this year

SELECT STR_TO_DATE("201301Monday", "%x%v%W");
returns "2012-12-31"

Su 	Mo 	Tu 	We 	Th 	Fr 	Sa
  	  	1 	2 	3 	4 	5
6 	7 	8 	9 	10 	11 	12
13 	14 	15 	16 	17 	18 	19
20 	21 	22 	23 	24 	25 	26
27 	28 	29 	30 	31

It is %v, mode 3, first week have >= 4days.
so 2012-12-31 is in first week of 2013.

SELECT YEARWEEK("2012-12-31", 7);
returns "201253"
And this is not in mode 3 but in mode 7,
where first week has a Monday in this year.
So 2012-12-31 is in 2012.

If we change mode to 3 we get expected results.
SELECT YEARWEEK("2012-12-31", 3);
YEARWEEK("2012-12-31", 3)
201301

As per user manual:

%U Week (00..53), where Sunday is the first day of the week; WEEK() mode 0
%u Week (00..53), where Monday is the first day of the week; WEEK() mode 1
%V Week (01..53), where Sunday is the first day of the week; WEEK() mode 2;
used with %X
%v Week (01..53), where Monday is the first day of the week; WEEK() mode 3;
used with %x

Mode 	First day of week 	Range 	Week 1 is the first week ???
0 	Sunday 	0-53 	with a Sunday in this year
1 	Monday 	0-53 	with 4 or more days this year
2 	Sunday 	1-53 	with a Sunday in this year
3 	Monday 	1-53 	with 4 or more days this year
4 	Sunday 	0-53 	with 4 or more days this year
5 	Monday 	0-53 	with a Monday in this year
6 	Sunday 	1-53 	with 4 or more days this year
7 	Monday 	1-53 	with a Monday in this year

SELECT STR_TO_DATE("201301Monday", "%x%v%W");
returns "2012-12-31"

Su 	Mo 	Tu 	We 	Th 	Fr 	Sa
  	  	1 	2 	3 	4 	5
6 	7 	8 	9 	10 	11 	12
13 	14 	15 	16 	17 	18 	19
20 	21 	22 	23 	24 	25 	26
27 	28 	29 	30 	31

It is %v, mode 3, first week have >= 4days.
so 2012-12-31 is in first week of 2013.

SELECT YEARWEEK("2012-12-31", 7);
returns "201253"
And this is not in mode 3 but in mode 7,
where first week has a Monday in this year.
So 2012-12-31 is in 2012.

If we change mode to 3 we get expected results.
SELECT YEARWEEK("2012-12-31", 3);
YEARWEEK("2012-12-31", 3)
201301

So I think this is not a bug.