mysql date function to provide yyyy-mm-ww dates based on 53 weeks of the year

Question

is there a mysql date function that provides yyyy-mm-ww dates based on 53 weeks of the year as per the table below? For example, 2015-01-01 is (2015-jan-week01). Or, is there a method you'd recommend that would allow me to achieve yyyy-mm-ww?

btw, is there a term for this type of month grouping by week? i didn't know what to search for

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 |

Possible duplicate of [How to group by week in MySQL?](http://stackoverflow.com/questions/1736010/how-to-group-by-week-in-mysql) — Filipe Ferreira, Oct 14 '15 at 15:12

score 0 · Accepted Answer · answered Oct 14 '15 at 15:22

Several modes of MySQL's WEEK() function use 53 weeks in a year, see 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>

mysql date function to provide yyyy-mm-ww dates based on 53 weeks of the year

1 Answers1