Mysql中Date_format函数按周、月统计总结

stbyruby

　　项目中的统计报表作的很多，需求中有按周、月统计数据的。查看了Mysql的API，发现Date_format是格式化日期的，看了Date_format()的具体说明后就用这个函数按周统计，sql大致如下：
select   DATE_FORMAT(check_date ,'%X-%V')  dates,avg(weight)/10000  weight from ho_body where user_id=295
 and  weight >0 and check_date  between '2009-02-24' and '2010-02-24' group by dates
------------------------------------------------------------------------------------------------------
查出的结果为：
dates       weight
2009-30   80.9
2009-31   80.425
2009-32   80.76666667
2009-33   80.75384615
2009-34   80.8
2009-35   79.88
2009-36   80.06
2009-37   79.875
2009-38   79.075
2009-39   79.26666667
2009-40   79.16666667
2009-41   78.875
2009-42   78.33333333
2009-43   78.77272727
2009-44   77.625
2009-45   77.825
2009-46   77.575
2009-47   77.45
2009-48   81.25
2009-49   76.5
2009-50   77.83333333
2009-52   79.8
2010-02   79.2
　　
　　检查了一下，大致没有问题，然后又开始寻找如何按月统计，然后就看Date_format的语法，
　　%m表示数值，%M表示名称，看后很兴奋的试一下：
　　select   distinct  DATE_FORMAT(check_date ,'%X-%m')  c1,avg(weight)/10000  wei from ho_body where user_id=295
 and  weight >0 and check_date  between '2009-02-24' and '2009-10-31' group by c1
　　——————————————————————————————————————
　　c1               wei
　　2009-08    80.66388889
2009-09    79.70555556
2009-10    78.83714286
　　
　　然后测试这样计算的结果是否正确，经过测试，果然是没问题的，很有成就感。然后就用这个方法统计，拿取数据，然后将这些统计的结果作为数据集，用JfreeChart画图。后期测试时，发现画的统计图横轴日期有问题，时间范围为2009-02-24～2010-02-24时，图片中竟然多出一个2009年1月，并且在最右侧。我确信我是经过排序的，不可能2009年1月会在最右侧，之后再调试，竟然发现数据库中根本就没有09年1月的数据，更直观的错误是，以between '2009-02-24' and '2009-10-31' 条件，根本不可能出现2009-01。
　　
　　然后将错误原因定位在sql语句上，执行这个语句，果然，原因就在这里：
　　select   distinct  DATE_FORMAT(check_date ,'%X-%m')  c1,avg(weight)/10000  wei from ho_body where user_id=295
 and  weight >0 and check_date  between '2009-02-24' and '2010-02-24' group by c1
　　————————————————————————————————————————
　　c1              wei
　　2009-01    79.8
2009-08    80.66388889
2009-09    79.70555556
2009-10    78.83714286
2009-11    77.64285714
2009-12    78.75
2010-01    79.2
　　这个09年1月到底是哪里出来的，我先确定2009年1月是否有数据，结果测试最早的数据为2009-08-01，并没有1月的任何数据。既然最早的数据是8月，那么就测试这个多出来的2009-01是从哪里冒出来的。
　　我就用最笨的方法测试：
select  distinct DATE_FORMAT(check_date ,'%X-%m')c1   from ho_body where user_id=295 and check_date  between '2009-08-01' and '2009-08-31' order by c1
　　————————————————————————————————————————
　　c1
　　2009-08
　　问题不在8月。继续测试：
　　select  distinct DATE_FORMAT(check_date ,'%X-%m')c1   from ho_body where user_id=295 and check_date  between '2009-9-01' and '2009-09-30' order by c1
　　——————————————————————————————————————
　　
　　c1
　　2009-09
　　
　　……
　　……
　　直到2010年时，
　　
select  distinct DATE_FORMAT(check_date ,'%X-%m')c1   from ho_body where user_id=295 and check_date  between '2010-01-01' and '2010-01-31' order by c1
　　————————————————————————————————————————
　　c1
　　2009-01
2010-01
　　问题就是2010年1月，并且，如果将条件改为between '2010-01-02' and '2010-01-31' 则不会多出2009-01。难道是Date_format函数有bug，在跨年时没有考虑正确，将2010年1月1日归为2009年的1月中？当然我也有怀疑过是我的sql语句有问题，仔细再看了看Date_format()的语法，
　　%X表示年，周日为一周第一天
　　%x表示年，周一为一周第一天
　　%M表示月，名称
　　%m表示月，数值，01形式
　　%V表示周，周日为第一天
　　%v表示周，周一为第一天
　　...........
　　
　　看来看去，感觉没什么错误呀，感觉自己对这个研究的够清楚明白了，一定没有问题的。
　　mysql有bug的想法我坚持了一下午，在我和一个同事说这个bug的时候，老大听到了，问我具体怎么回事，我就给她讲解，特别奇怪的地方在于数据库根本没有09年1月的数据，用mysql中的Date_format的按月统计函数时，在跨年时有问题。老大也觉得这个问题不可思议，这时那个同事说，你怎么用X表示年呀，一般不都是有Y表示年吗？我还反驳说，这个没区别的呀，都是一样的，表示年嘛，4位的。。。。
　　后来和他争论之中，为了表明修改这个X，Y什么的没效果，我就改成了%Y-%m，结果却是2009-01没有了！！！！
　　怎么会这样？？？？也太低级了吧，之前一直怀疑mysql的bug，竟然是这个原因？然后再仔细看Date_format()的语法，看后快晕过去了，这么低级的错误我怎么之前一直没有发现呢？
　　

%M	月名
%m	月，数值(00-12)

　　

%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y	年，4 位
%y	年，2 位

　　
　　难道%X只能与%V一起用表示年-周，并且周日为一周开始，
　　%x与%v一起用，表示年-周，周一为一周开始？
　　再看到前面的
　　

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

　　看来是这个意思了，我之前怎么就没有注意到这个特别之处呢？可能是当时作完以周统计，然后再写月统计时，一看m表示月，数值，就直接把Date_format('%X-%V')改为Date_format('%X-%m')了。再加上%Y年，4位，%m月，没有具体说明，所以一直没有发现，并且用%X-%m按月统计，测试了许多数据，发现年-月，数值全是正确的。结果在跨年的情况下有问题了。。。一般情况下我是会考虑跨年的情况的，比如在求一个日期属于这一年的第几周，在这个问题上，我测试2009-12-31，2010-01-01属于哪一周，2010-01-03是哪一周，发现之前的方法有bug，后来作了修正才正确。而这是用mysql函数统计，测试一些数据没问题就觉得ok了，根本没有想到这样写在跨年时有问题了。。。。
　　

为了将这个问题彻底整理清楚，下面是我找到的详细解读：

Date_format可以使用的格式有：

　　

格式	描述
%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 位

 

Mysql中还有另外几种返回日期的函数，如：

SELECT  EXTRACT(YEAR_MONTH  FROM  datecolum ) 返回格式如200902

EXTRACT() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。

语法

EXTRACT(unit FROM date)

date 参数是合法的日期表达式。unit 参数可以是下列的值：

　　

Unit 值

MICROSECOND

SECOND

MINUTE

HOUR

DAY

WEEK

MONTH

QUARTER

YEAR

SECOND_MICROSECOND

MINUTE_MICROSECOND

MINUTE_SECOND

HOUR_MICROSECOND

HOUR_SECOND

HOUR_MINUTE

DAY_MICROSECOND

DAY_SECOND

DAY_MINUTE

DAY_HOUR

YEAR_MONTH