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

川长思鸟来 2022-07-13 15:24 243阅读 0赞

## MySQL的跨年周统计问题 ##

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

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

![这里写图片描述][SouthEast]  
图1

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

![这里写图片描述][SouthEast 1]  
图2

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

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

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

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

由表可以看出，%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')

![bUi1qK2.png][]

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

![QXatwbR.png][]

问题就出现在这里了，因为转换周的原理是先求出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，如下图所示

![这里写图片描述][SouthEast 2]

###     这是在处理项目跨年周统计的时候的总结，在第一点的时候也说到了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

结果为下图，查询正确

![i8O28hc.png][]

那如果写成%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周

![RtxxhZU.png][]

分析原因：网上找不到合理的资料，我就根据我的理解分析下产生这个现象的原因，%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

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

![AlFGaBU.png][]

再来看%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这个数据是有问题的

![Uid8fbj.png][]

## 总结 ##

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

[SouthEast]: /images/20220713/1f4f9abca9c0435da72f3311197fab49.png
[SouthEast 1]: /images/20220713/319e8ade29ce48028fa53b9271b7d397.png
[bUi1qK2.png]: http://i.imgur.com/bUi1qK2.png
[QXatwbR.png]: http://i.imgur.com/QXatwbR.png
[SouthEast 2]: /images/20220713/ee57479ca5d34a19bc88a3688aecce8b.png
[i8O28hc.png]: http://i.imgur.com/i8O28hc.png
[RtxxhZU.png]: http://i.imgur.com/RtxxhZU.png
[AlFGaBU.png]: http://i.imgur.com/AlFGaBU.png
[Uid8fbj.png]: http://i.imgur.com/Uid8fbj.png