项目中的统计报表作的很多,需求中有按周、月统计数据的。查看了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-3879.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
%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() 函数用于返回日期/时间的单独部分,比如年、月、日、小时、分钟等等。
语法
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
|
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
|
检查了一下,大致没有问题,然后又开始寻找如何按月统计,然后就看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()的语法,看后快晕过去了,这么低级的错误我怎么之前一直没有发现呢?
转自:http://blog.csdn.net/wcp88888888/article/details/6178812
http://blog.csdn.net/wgllz/article/details/5334764
分享到:
相关推荐
DATE_FORMAT 函数是 MySQL 中一个强大且常用的函数,它允许我们根据需要将日期或时间字段格式化为不同的形式。通过合理运用日期/时间格式符,我们可以轻松实现各种自定义日期和时间格式。希望本文提供的实例和代码...
您可能感兴趣的文章:MySQL中日期比较时遇到的编码问题解决办法PHP以及MYSQL日期比较方法mysql 获取当前日期函数及时间格式化参数详解mysql unix准换时间格式查找指定日期数据代码MySql日期查询语句详解深入mysql ...
本文转自:http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#function_date-format DATE_FORMAT(date,format) Formats the date value according to the format string. The following ...
文章目录SQL Date 函数MySQL Date 函数NOW() 函数CURDATE() 函数CURTIME() 函数DATE() 函数EXTRACT() 函数DATE_ADD() 函数DATE_SUB() 函数DATEDIFF() 函数DATE_FORMAT() 函数SQL Server Date 函数GETDATE() 函数...
mysql> select date_format('2013-11-19','Y-m-d') > timediff('2013-11-19', '2013-11-20'); ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,NUMERIC) for ...
mysql日期函数From_unixtime及UNIX_TIMESTAMP及DATE_FORMAT(后者只能格式化标准日期格式,时间戳的不行)
Oracle和mysql的一些简单命令对比 1) SQL> select to_char(sysdate,’yyyy-mm-dd’) from dual; SQL> select to_char(sysdate,... 日期函数 增加一个月: SQL> select to_char(add_months(to_date (‘20000101′,’yyy
以前使用mysql不是很多,对mysql的函数也不是很熟悉,遇到这个问题第一时间百度搜索,搜索到这两种方法,这两种方法是排在百度第一条的博客。 方法一 SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS...
主要介绍了MySQL的时间差函数(TIMESTAMPDIFF、DATEDIFF)、日期转换计算函数(date_add、day、date_format、str_to_date),文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友...
-> 2 WEEK(date) WEEK(date,first) 对于星期日是一周中的第一天的场合,如果函数只有一个参数调用,返回 date 为一年的第几周,返回值范围为 0 到 53 (是的,可能有第 53 周的开始)。两个参数形式的 ...
mysql 获取规定时间段内的统计数据 按年统计 SELECT count(*), DATE_FORMAT(order_info.create_time, '%Y-%m-%d') AS count_by_date ...按月统计 SELECT count(*), DATE_FORMAT(order_info.create_time, '%Y-%m-
MySQL 获取当前日期及日期格式 获取系统日期: NOW() 格式化日期: DATE_FORMAT(date, format) 注: date:时间字段 format:日期格式 返回系统日期,输出 2009-12-25 14:38:59 select now(); 输出 09-12-25 ...
在MySQL中对于时间日期的处理的函数主要是DATE_FORMAT(date,format)。可用的参数如下 格式 描述 %a 缩写星期名 %b 缩写月名 %c 月,数值 %D 带有英文前缀的月中的天 %d 月的天,数值(00-31) %e 月...
20.DATE_FORMAT(date,format) 21.TIME_FORMAT(time,format) 22.CURDATE() 23.CURTIME() 24.SYSDATE() 25.UNIX_TIMESTAMP() UNIX_TIMESTAMP(date) 26.FROM_UNIXTIME(unix_timestamp) FROM_UNIXTIME(unix_timestamp,...
mysql> select date_format(‘2013-11-19′,’Y-m-d’) > timediff(‘2013-11-19’, ‘2013-11-20’); ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,...
目录I.... 日期和时间函数> DATE_FORMAT 可以使用的格式5. 加密函数6. 控制流函数7. 格式化函数8. 类型转化函数9. 系统信息函数 I. MySQL 常用函数 为了简化操作,MySQL 提供了大量的函数给程序员使用
函数:FROM_UNIXTIME作用:将MYSQL中以INT(11)存储的时间以”YYYY-MM-DD”格式来显示。语法:FROM_UNIXTIME(unix_timestamp,format) 返回表示 Unix 时间标记的一个字符串,根据format字符串格式化。format可以包含与...
日期格式化函数: DATE_FORMAT:时间格式化为字符串; STR_TO_DATE:时间字符串转为为日期格式; 日期加减函数: DATE_SUB:日期做减法 DATE_ADD:日期做加法
date_format(date, format) 函数,MySQL日期格式化函数date_format() unix_timestamp() 函数 str_to_date(str, format) 函数 from_unixtime(unix_timestamp, format) 函数,MySQL时间戳格式化函