格式化时间例子:
date_format(now(),'%y-%m-%d %H:%i:%S')
项目中的统计报表作的很多,需求中有按周、月统计数据的。查看了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
|
相关推荐
MySQL中时间日期格式化函数的例子。例如 TO_DAYS、DAYOFWEEK、WEEKDAY ... 等函数的使用。
1. DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。 DATE_FORMAT(date,format) format参数的格式有 %a 缩写星期名 %b 缩写月名 %c 月,数值 %D 带有英文前缀的月中的天 %d 月的天,数值(00-31)...
mysql 获取当前日期及格式化,mysql 获取当前日期及格式化简单方便mysql 获取当前日期及格式化
bootstap-talbe日期格式化,包括mysql数据库,后台为spring mybatis
MySQL 获取当前日期及日期格式 获取系统日期: NOW() 格式化日期: DATE_FORMAT(date, format) 注: date:时间字段 format:日期格式 返回系统日期,输出 2009-12-25 14:38:59 select now(); 输出 09-12-25 ...
最近因为工作的需求,需要对mysql中数字进行格式化,但发现网上的资料较少,索性自己总结一下,方便自己也帮助有需要的朋友们,下面话不多说,来一起看看详细的介绍: 一、format函数: 格式化浮点数 format(number...
这里是一个使用日期函数的例子。下面的查询选择了所有记录,其date_col的值是在最后30天以内: mysql> SELECT something FROM table WHERE TO_DAYS(NOW()) – TO_DAYS(date_col) <= 30; DAYOFWEEK(date) 返回日期...
主要介绍了mysql 获取当前日期函数now()及时间格式化DATE_FROMAT函数以及参数详细介绍,需要的朋友可以参考下
Mysql格式化日期时间[归类].pdf
本文实例讲述了thinkphp5.1框架实现格式化mysql时间戳为日期的方式。分享给大家供大家参考,具体如下: 方式一 使用mysql函数FROM_UNIXTIME(unix_timestamp,format)直接转换 select FROM_UNIXTIME(o.create_time,'...
1.我的MySQL中的start_time存储的是2018-03-21 10:55:32格式的时间,我需要按照YYYY-MM-DD格式来查询,我的MySQL中的sql是这样写的: SELECT * from mytable WHERE DATE_FORMAT(start_time,”%Y-%m-%d”)=’2018-03...
这是总结的一下关于mysql的对一些 格式的判断 比方说对日期格式是否正确的判断,数据的格式是否 全部是数字 等
您可能感兴趣的文章:MySQL中日期比较时遇到的编码问题解决办法PHP以及MYSQL日期比较方法mysql 获取当前日期函数及时间格式化参数详解mysql unix准换时间格式查找指定日期数据代码MySql日期查询语句详解深入mysql ...
DATE_FORMA T(date, format) 根据格式串format 格式化日期或日期和时间值date,返回结果串。 可用DATE_FORMAT( ) 来格式化DATE 或DATETIME 值,以便得到所希望的格式。根据format字符串格式化date值: %S, %s 两位...
主要给大家介绍了关于golang gorm中格式化时间问题的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用golang具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
获取当前时间的MySql时间函数处理MySql时间日期的函数有很多,下面为您介绍的就是用于获取当前时间的MySql时间函数,如果您对此感兴趣的话,不妨一看下面为您介绍的MySql时间函数用于获取当前时间,该MySql时间函数...
date_format(date, format) 函数,MySQL日期格式化函数date_format() unix_timestamp() 函数 str_to_date(str, format) 函数 from_unixtime(unix_timestamp, format) 函数,MySQL时间戳格式化函