mysql 日期函数提供基于一年中 53 周的 yyyy-mm-ww 日期

Question

是否有一个 mysql 日期函数可以根据下表提供基于一年中 53 周的 yyyy-mm-ww 日期？例如，2015-01-01 是 (2015-jan-week01)。或者，您是否有推荐的方法可以让我实现 yyyy-mm-ww？

顺便说一句，这种按周分组的月份有术语吗？我不知道要搜索什么

jan                 feb            mar
--------------- -------------- ----------------
| 01 02 03 04 | | 05 06 07 08| |09 10 11 12 13 |

apr             may            jun
--------------- -------------- -----------------
| 14 15 16 17 | | 18 19 20 21| |22 23 24 25 26 |

jul             aug            sep
--------------- -------------- -----------------
| 27 28 29 30 | | 31 32 33 34| | 35 36 37 38 39 |

oct             nov            dec
--------------- -------------- --------------------
| 40 41 42 43 | | 44 45 46 47| | 48 49 50 51 52 53 |

Answer 1

MySQL WEEK()函数的几种模式一年使用53周，参见http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_week

mysql> SELECT WEEK('2008-12-31',1);
+----------------------+
| WEEK('2008-12-31',1) |
+----------------------+
|                   53 |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT_WS('-', YEAR('2008-12-31'), SUBSTRING(MONTHNAME('2008-12-31'), 1, 3), WEEK('2008-12-31',1));
+----------------------------------------------------------------------------------------------------+
| CONCAT_WS('-', YEAR('2008-12-31'), SUBSTRING(MONTHNAME('2008-12-31'), 1, 3), WEEK('2008-12-31',1)) |
+----------------------------------------------------------------------------------------------------+
| 2008-Dec-53                                                                                        |
+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT(YEAR('2015-01-01'), '-', LOWER(SUBSTRING(MONTHNAME('2015-01-01'), 1, 3)), '-week', WEEK('2015-01-01',1));
+-----------------------------------------------------------------------------------------------------------------+
| CONCAT(YEAR('2015-01-01'), '-', LOWER(SUBSTRING(MONTHNAME('2015-01-01'), 1, 3)), '-week', WEEK('2015-01-01',1)) |
+-----------------------------------------------------------------------------------------------------------------+
| 2015-jan-week1                                                                                                  |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>