MySQL YEARWEEK函数格式化问题及周数归属疑问

为什么2018-01-06被归为2017年第53周？ #

这是因为MySQL的YEARWEEK()函数默认使用模式0，它的规则是：

• 一周以周日作为起始日
• 一年的第一周是包含当年第一个周日的那一周

2018年的第一个周日是1月7日，所以1月1日-6日这段时间还属于2017年的最后一周（也就是第53周）——因为这一周没有包含2018年的第一个周日，所以被划归到上一年的周数里。

如果想改变这个行为，可以给YEARWEEK()传入第二个参数指定模式，比如：

• 模式1：一周以周一为起始，第一周是包含至少4天在当年的周
• 模式3：一周以周一为起始，第一周包含1月1日

比如执行YEARWEEK('2018-01-06', 1)会得到201801，因为按照模式1的规则，这一周已经有4天在2018年了，会被算作2018年的第一周。

如何格式化为week04, December这类易读形式？ #

你可以结合字符串拼接函数和日期函数来实现，这里有两种常用方案：

方案1：直接匹配YEARWEEK()的周数 + 原日期的月份

这个方案保证周数和你当前查询的week_字段完全对应，同时提取原日期的英文月份名：

SELECT 
  DATE(date_created) as date_,
  YEARWEEK(date_created) as week_,
  CONCAT(
    'week',
    -- 提取YEARWEEK结果里的周数部分，补前导零到2位
    LPAD(SUBSTRING(YEARWEEK(date_created), 5, 2), 2, '0'),
    ', ',
    -- 提取原日期的英文月份名称
    MONTHNAME(date_created)
  ) AS readable_week
FROM your_table;

执行后结果会是：

方案2：按周所属年份显示对应月份（比如2017年第53周显示December）

如果希望跨年份的周显示所属年份的最后一个月份，可以先计算该周的第一天，再提取对应月份：

SELECT 
  DATE(date_created) as date_,
  YEARWEEK(date_created) as week_,
  CONCAT(
    'week',
    LPAD(SUBSTRING(YEARWEEK(date_created), 5, 2), 2, '0'),
    ', ',
    -- 计算该周的第一天（模式0下的周日），再提取月份名
    MONTHNAME(STR_TO_DATE(CONCAT(SUBSTRING(YEARWEEK(date_created),1,4), 'W', SUBSTRING(YEARWEEK(date_created),5,2), '0'), '%YW%u%w'))
  ) AS readable_week
FROM your_table;

这个方案下，2018-01-06的readable_week会变成week53, December，因为它属于2017年的周，对应的周第一天是2017-12-31（周日），月份是December。

内容的提问来源于stack exchange，提问作者Lululu