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

Question

是否有一个mysql日期函数，根据一年中的53周提供yyyy-mm- www日期，如下表所示?例如，"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>