MySQL的跨年周统计问题（%X-%V）

MySQL的跨年周统计问题

    最近在做项目的时候，发现了一个报表在进行周统计并且跨年的时候会出现问题，具体问题截图如下：(主要与%X-%V的时间格式符有关)

2016-12-18 ~~ 2017-01-13 周统计

图1

2015-12-20 ~~ 2016-01-17 周统计

图2

    这两张图中，周统计中，代码中的sql时间格式化为

DATE_FORMAT(FROM_UNIXTIME(regTime,'%Y-%m-%d'),'%Y-%U')

    为了让接下来的解释更加清楚，这里先贴一张MySQL的时间格式化符和描述

格式 描述 %a 缩写星期名 %b 缩写月名 %c 月，数值 %D 带有英文前缀的月中的天 %d 月的天，数值(00-31) %e 月的天，数值(0-31) %f 微妙 %H 小时 (00-23) %h 小时 (01-12) %I 小时 (01-12) %i 分钟，数值(00-59) %j 年的天 (001-366) %k 小时 (0-23) %l 小时 (1-12) %M 月名 %m 月，数值(00-12) %p AM 或 PM %r 时间，12-小时（hh:mm:ss AM 或 PM） %S 秒(00-59) %s 秒(00-59) %T 时间, 24-小时 (hh:mm:ss) %U 周 (00-53) 星期日是一周的第一天 %u 周 (00-53) 星期一是一周的第一天 %V 周 (01-53) 星期日是一周的第一天，与 %X 使用 %v 周 (01-53) 星期一是一周的第一天，与 %x 使用 %W 星期名 %w 周的天 （0=星期日, 6=星期六） %X 年，其中的星期日是周的第一天，4 位，与 %V 使用 %x 年，其中的星期一是周的第一天，4 位，与 %v 使用 %Y 年，4 位 %y 年，2 位

    由表可以看出，%U是代表一周，并且是以周日当做第一天，由图2可以看出，15-12-27作为15年的最后一周，其7天的时间分别为12-27、12-28、12-29、12-30、12-31、16-01-01、16-01-02，也就是说，15年的最后一周跨到了16年，那么%U是怎么处理的呢？%U代表的是第0周开始的，使用星期天作为一周的第一天，当遇到本年的第一个星期天时，就是第 1 周了，那么在1.1到今年第一周之前的天数就当做今年的第0周

举例说明：

（1）2017的第一个周日 正好是2017-01-01，那么从这一天开始就是第 1 周，而不是第0周

（2） 2016的第一个周日，是 2016-01-03，那么2016-01-01至2016-01-03是算作第 0 周，而
把2013-01-03 至 2013-01-10 算作2013年的第 1 周

    所以这里就有匪夷所思的第0周出现了，那么程序是如何格式化这个第0周的呢，这样的跨年现象，是把15年的最后一周变成只有12-27、12-28、12-29、12-30、12-31这5天，而16-01-01、16-01-02算做新的一周，也就是图2中从1.1开始算一周，到了16.1.3号又重新计算一周。换句话说，15年的最后一周的数据是图2报表中
15.12.27的数据加上16.01.01的数据。

    因为说到了%U，所以这里再补充解释一下%u，%u:使用星期一作为一周的第一天，这个就不像上面一样了，这里并不是遇到每年的第一个周一算第 1 周，而是计算第一个周一之前的天数如果能超过3天(不包括3天)，那么计算为第 1 周。否则就计算为第0周。

    对于这样的跨年引发的周统计的问题，有没有更合理的解决方式呢？答案是有的，那就是MySQL的另外个两个格式符%X-%V

%X: 年，其中的星期日是周的第一天，4 位，与 %V 使用

%V: 周， (01-53) 星期日是一周的第一天，与 %X 使用

    网上很少有关于%X,%V的使用方法的讲解，我也只是偶然中看到CSDN的一篇帖子才发现有这么个东西的存在，
%V 与 %U 一样的地方就是也是使用周日作为一周的开始，并且也是遇到第一个周日开始算作是第一周，但是这两个对这个下一年第一个周日之前的这几天处理不一样，%U是把下一年之前的几天算作第0周而%V是把下一年之前的几天算作上一年的最后一周，所以使用%X-%V这样的格式符就能解决图2的问题

    这里也补充一下%v,%v与%u也有相似之处，就是同样适用周一作为 一周的开始,也同样计算每年第一个周一之前的
天数，如果＞3天则记为第 1 周，不同是，如果≤3天，则算作上一年的最后一周

DATE_FORMAT(FROM_UNIXTIME(regTime,'%Y-%m-%d'),'%X-%V')

    既然图2出现的跨年现象解决了，那么图1的现象是否也解决了呢？答案是不，为什么呢？分析图1和图2，我们可以看出，图2是因为上一年的最后一周跨到下一年了，所以导致有下一年的1.1这一周出现，但是图1不一样，16年的最后一周刚好到了12.31，并没有跨到17年，图一的17年第一周是从1.8开始的，1.1这一周没有显示出来。咦，这就奇怪啦，我是可以保证时间格式化是没有问题的，为什么还会出现这个问题呢？只有一个答案可以解释了，那么就是代码问题，如果是代码问题，那么就要追溯时间转换的方法上了，view代码。

    问题就出现在这里了，因为转换周的原理是先求出1.1 + 周数 * 7 的日期，然后再减去1.1号距离第一个周日的天数 - 1，然后就得到某一周的开始的周日日期，具体公式为:(1.1 + 周数 * 7) - （1.1距离第一个周日的天数 - 1）

举例说明：

（1）2016年的1.1是周五，按周日为一周第一天这样来计算，周五就是一周的第6天，从1.1号开始，一周为7天。假设我们从第二周开始，1.1 + （周数 * 7）也就是 1.1 + 14 = 1.15,1.15 - （1.1距离第一个周日的天数 - 1）也就是1.15 - （6-1） = 1.10.所以1.10就是刚好是16年的第二个周的开始

（2）上面的例子对于大多数情况是适用的，但是如果一年的第一个周日刚好是1.1的话，上面的例子就会出现这样的情况，1.8 - (1 - 1) = 1.8,所以刚好1.1这一周的数据就被过滤掉了，所以如果刚好一年的第一个周日刚好是1.1的话，那么就应该是1.8 - 7 = 1.1，如下图所示

    这是在处理项目跨年周统计的时候的总结，在第一点的时候也说到了MySQL的格式符问题，下面就想详细的解释一下MySQL几个格式符的问题

    既然%Y表示年，%X也表示年，那么%X-%V是否可以写成%Y-%V呢？常用的表示年月的%Y-%m是否可以写成%X-%m呢？

# %X-%VSELECTDATE_FORMAT(    FROM_UNIXTIME(regTime, '%Y-%m-%d'),    '%X-%V') AS groupweek,count(distinct FROM_UNIXTIME(regTime, '%Y-%m-%d') ) from acctStatusWHEREregTime >UNIX_TIMESTAMP('2015-12-20 00:00:00') and regTime < UNIX_TIMESTAMP('2016-01-17 00:00:00')AND regTime <> '' group by groupweek

    结果为下图，查询正确

    那如果写成%Y-%V，会发生什么情况呢？

# %Y-%VSELECTDATE_FORMAT(    FROM_UNIXTIME(regTime, '%Y-%m-%d'),    '%Y-%V') AS groupweek,count(distinct FROM_UNIXTIME(regTime, '%Y-%m-%d') ) from acctStatusWHEREregTime >UNIX_TIMESTAMP('2015-12-20 00:00:00') and regTime < UNIX_TIMESTAMP('2016-01-17 00:00:00')AND regTime <> '' group by groupweek

    结果如下图所示，会发现多出了一个诡异的2016年52周

    分析原因：网上找不到合理的资料，我就根据我的理解分析下产生这个现象的原因，%Y的解释为年，%X的解释为年，其中的星期日是周的第一天，也就是说%Y是把1.1当做一年的第一天，所以%Y-%U会从第0周开始算起，而%X把第一个周日当做一周的第一天，也把第一个周日当做一年的第一天，所以和%V（周 (01-53) 星期日是一周的第一天，与 %X 使用刚好适用。而%Y与%V本身之间对于第一天的基准就是不同的，一起使用当然就会有问题，如果1.1距离第一个周日之间是没有数据的，那么我们不会发现这两种写法有什么区别，因为数据显示会是完全一样的。同理，来验证下%Y-%m与%X-%m的差别

# %Y-%mSELECTDATE_FORMAT(    FROM_UNIXTIME(regTime, '%Y-%m-%d'),    '%Y-%m') AS groupmonth,count(distinct FROM_UNIXTIME(regTime, '%Y-%m-%d') ) from acctStatusWHEREregTime >UNIX_TIMESTAMP('2015-12-20 00:00:00') and regTime < UNIX_TIMESTAMP('2016-01-17 00:00:00')AND regTime <> '' group by groupmonth

    结果如下图所示，查询结果正确

&nbsp;&nbsp;&nbsp;&nbsp;再来看%X-%m# %X-%mSELECTDATE_FORMAT(    FROM_UNIXTIME(regTime, '%Y-%m-%d'),    '%X-%m') AS groupmonth,count(distinct FROM_UNIXTIME(regTime, '%Y-%m-%d') ) from acctStatusWHEREregTime >UNIX_TIMESTAMP('2015-12-20 00:00:00') and regTime < UNIX_TIMESTAMP('2016-01-17 00:00:00')AND regTime <> '' group by groupmonth

    结果如下图所示，多出了一个2015-1，但是我们的查询条件却是从15.12开始的，也就是说2015-01这个数据是有问题的

总结

    跨年的周统计要使用%X-%V，%Y要和%U、%m一起使用，%X要和%V一起使用，如果随意使用，就会产生错误，同理,
%x,%y,%u,%v也是一样的。