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

没有评论

2011 年 02 月 10 日 at 下午 6:00分类:MySQL | WEB开发

原文地址:http://julianna-only.javaeye.com/blog/603008
http://www.cnblogs.com/oec2003/archive/2009/06/04/1496541.html
详细:1、http://blog.csdn.net/fffy2366/archive/2010/08/09/5799104.aspx
2、http://ahzhangziqiang.blog.163.com/blog/static/441343162009112322049353/
项目中的统计报表作的很多,需求中有按周、月统计数据的。查看了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可以使用的格式有:

mysql 按年度、季度、月度、周、日SQL统计查询

没有评论

2011 年 02 月 10 日 at 下午 5:57分类:MySQL | WEB开发

一、年度查询

查询 本年度的数据

SELECT *

FROM blog_article

WHERE year( FROM_UNIXTIME( BlogCreateTime ) ) = year( curdate( ))

二、查询季度数据

查询数据附带季度数

SELECT ArticleId, quarter( FROM_UNIXTIME( `BlogCreateTime` ) )

FROM `blog_article`

其他的同前面部分:查询 本季度的数据

SELECT *

FROM blog_article

WHERE quarter( FROM_UNIXTIME( BlogCreateTime ) ) = quarter( curdate( ))

三、查询月度数据

本月统计(MySQL)

select * from booking where month(booking_time) =

month(curdate()) and year(booking_time) = year(curdate())

本周统计(MySQL)

select * from spf_booking where month(booking_time) =

month(curdate()) and week(booking_time) = week(curdate())

四、时间段

N天内记录

WHERE TO_DAYS(NOW()) – TO_DAYS(时间字段) <= N 当天的记录 where date(时间字段)=date(now()) 或 where to_days(时间字段) = to_days(now()); 查询一周: select * from table where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(column_time); 查询一个月: select * from table where DATE_SUB(CURDATE(), INTERVAL INTERVAL 1 MONTH) <= date(column_time); 查询'06-03'到'07-08'这个时间段内所有过生日的会员: Select * From user Where DATE_FORMAT(birthday,'%m-%d') >= ’06-03′ and DATE_FORMAT(birthday,’%m-%d’)

<= ’07-08′; 统计一季度数据,表时间字段为:savetime group by concat(date_format(savetime, ‘%Y ‘),FLOOR((date_format(savetime, ‘%m ‘)+2)/3)) 或 select YEAR(savetime)*10+((MONTH(savetime)-1) DIV 3) +1,count(*) from yourTable group by YEAR(savetime)*10+((MONTH(savetime)-1) DIV 3) +1; 五、分组查询 1、年度分组 2、月度分组 3、先按年度分组,再按月度分组 4、按年月分组 SELECT count(ArticleId), date_format(FROM_UNIXTIME( `BlogCreateTime`),’%y%m’) sdate FROM `blog_article` group by sdate 结果: count( ArticleId ) sdate 17 0901 11 0902 5 0903 6 0904 2 0905 1 0907 12 0908 6 0909 11 0910 3 0911