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-%V
SELECT
DATE_FORMAT(
FROM_UNIXTIME(regTime, '%Y-%m-%d'),
'%X-%V'
) AS groupweek,count(distinct FROM_UNIXTIME(regTime, '%Y-%m-%d') ) from acctStatus
WHERE
regTime >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-%V
SELECT
DATE_FORMAT(
FROM_UNIXTIME(regTime, '%Y-%m-%d'),
'%Y-%V'
) AS groupweek,count(distinct FROM_UNIXTIME(regTime, '%Y-%m-%d') ) from acctStatus
WHERE
regTime >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-%m
SELECT
DATE_FORMAT(
FROM_UNIXTIME(regTime, '%Y-%m-%d'),
'%Y-%m'
) AS groupmonth,count(distinct FROM_UNIXTIME(regTime, '%Y-%m-%d') ) from acctStatus
WHERE
regTime >UNIX_TIMESTAMP('2015-12-20 00:00:00') and regTime < UNIX_TIMESTAMP('2016-01-17 00:00:00')
AND regTime <> '' group by groupmonth
结果如下图所示,查询结果正确
再来看%X-%m
# %X-%m
SELECT
DATE_FORMAT(
FROM_UNIXTIME(regTime, '%Y-%m-%d'),
'%X-%m'
) AS groupmonth,count(distinct FROM_UNIXTIME(regTime, '%Y-%m-%d') ) from acctStatus
WHERE
regTime >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也是一样的。