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: | |

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 |

[3 Aug 2012 7:27]
Indrek Altpere

[3 Aug 2012 8:24]
Valeriy Kravchuk

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)

[27 Nov 2012 18:23]
Krystian Cybulski

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.

[28 Nov 2012 9:42]
Indrek Altpere

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?

[28 Nov 2012 10:49]
Krystian Cybulski

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.

[23 Dec 2014 12:42]
Erlend Dahl

[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

[23 Dec 2014 12:45]
Mithun Chicklore Yogendra

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.