Bug #80118 STR_TO_DATE doesn't work properly

• Submitted: 22 Jan 2016 14:45
• Modified: 25 Jan 2016 6:48
• Reporter: Andrius Versockas
• Status: Verified
• Category: MySQL Server: DML
• Severity: S1 (Critical)
• Version: 5.6.26, 5.6.28, 5.7.10
• OS: Any
• CPU Architecture: Any
• Tags: 5.6.26, bug

[22 Jan 2016 14:45] Andrius Versockas

Description:
STR_TO_DATE returns incorrect date

How to repeat:
select STR_TO_DATE('2016_01 Monday', "%X_%V %W");-- 2016-01-04
select STR_TO_DATE('2015_53 Monday', "%X_%V %W"); -- 2016-01-04

Suggested fix:
return correct date

[22 Jan 2016 14:53] Andrius Versockas

You can always check: http://www.epochconverter.com/epoch/weeknumbers.php

[25 Jan 2016 6:48] MySQL Verification Team

Hello Andrius,

Thank you for the bug report.

Thanks,
Umesh

[25 Jan 2016 9:59] Øystein Grøvlen

It is not quite clear to me what is considered the bug here.

Note that format string "%X_%V" considers Sunday to be the first week of the year.  Hence:

mysql> select STR_TO_DATE('2015_01 Monday', "%X_%V %W");                                                                                                               +-------------------------------------------+
| STR_TO_DATE('2015_01 Monday', "%X_%V %W") |
+-------------------------------------------+
| 2015-01-05                                |
+-------------------------------------------+
1 row in set (0,00 sec)

To use ISO week numbering where Monday is the first day of the week, format string "%x_%v" should be used:

mysql> select STR_TO_DATE('2015_01 Monday', "%x_%v %W");
+-------------------------------------------+
| STR_TO_DATE('2015_01 Monday', "%x_%v %W") |
+-------------------------------------------+
| 2014-12-29                                |
+-------------------------------------------+
1 row in set (0,00 sec)

The above shows that for 2015, week 1 differs depending on whether Sunday or Monday is considered the first day of the week.

The consequence for the week at the end of 2015 is that it will be week 52 when using %X%V and week 53 when using %x%v.  If %x%v is used, one will get:

mysql> select STR_TO_DATE('2015_53 Monday', "%x_%v %W");                                                                                                               +-------------------------------------------+
| STR_TO_DATE('2015_53 Monday', "%x_%v %W") |
+-------------------------------------------+
| 2015-12-28                                |
+-------------------------------------------+
1 row in set (0,00 sec)

mysql> select STR_TO_DATE('2016_01 Monday', "%x_%v %W");
+-------------------------------------------+
| STR_TO_DATE('2016_01 Monday', "%x_%v %W") |
+-------------------------------------------+
| 2016-01-04                                |
+-------------------------------------------+
1 row in set (0,00 sec)

However, what could be considered a bug is that request for week 53 gives a valid result for years where week number 53 is not used.  In other words, one could argue that select STR_TO_DATE('2015_53 Monday', "%X_%V %W") should give NULL and a warning similar to what happens when a week number larger than 53 is used:

mysql> select STR_TO_DATE('2015_54 Monday', "%X_%V %W");
+-------------------------------------------+
| STR_TO_DATE('2015_54 Monday', "%X_%V %W") |
+-------------------------------------------+
| NULL                                      |
+-------------------------------------------+
1 row in set, 2 warnings (0,00 sec)

Warning (Code 1411): Incorrect datetime value: '2015_54 Monday' for function str_to_date
Warning (Code 1411): Incorrect datetime value: '2015_54 Monday' for function str_to_date