I am try gather some statistics from a database and it need group by week numbers. and the results return unexpected.
SELECT create_date, WEEK(create_date) as week FROM `ttable` +---------------------+-------------+ | craete_date | week | +---------------------+-------------+ | 2018-01-02 00:00:00 | 0 | | 2018-04-10 00:00:00 | 14 | | 2018-03-13 00:00:00 | 10 | | 2018-03-30 00:00:00 | 12 | +---------------------+-------------+From the above you will found that the week number was wrong. and MySQL counts the first days of the year that do not belong week 1 as week 0.
This is because MySQL default week() function caused the issue. because in MySQL the first day of week is Sunday and it range was 0 to 53 weeks. And you need change the MySQL mode with the below table.
+--------------------------------------------------------------------+ | 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 more than 3 days this year | |------+-------------------+-------+---------------------------------| | 2 | Sunday | 1-53 | with a Sunday in this year | |------+-------------------+-------+---------------------------------| | 3 | Monday | 1-53 | with more than 3 days this year | |------+-------------------+-------+---------------------------------| | 4 | Sunday | 0-53 | with more than 3 days this year | |------+-------------------+-------+---------------------------------| | 5 | Monday | 0-53 | with a Monday in this year | |------+-------------------+-------+---------------------------------| | 6 | Sunday | 1-53 | with more than 3 days this year | |------+-------------------+-------+---------------------------------| | 7 | Monday | 1-53 | with a Monday in this year | +--------------------------------------------------------------------+What I needed was mode 3 of the week() function. and we try run the query with the mode and see what the results.
SELECT create_date, WEEK(create_date, 3) as week FROM `ttable` +---------------------+-------------+ | craete_date | week | +---------------------+-------------+ | 2018-01-02 00:00:00 | 1 | | 2018-04-10 00:00:00 | 15 | | 2018-03-13 00:00:00 | 11 | | 2018-03-30 00:00:00 | 13 | +---------------------+-------------+Yes, above is show me the correct results