mysql 时间格式化

康慧欣 2014-09-29

 格式化时间例子:

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

年,其中的星期日是周的第一天,位,与 %V 使用

%x

年,其中的星期一是周的第一天,位,与 %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

相关推荐

vqudominiapp / 0评论 2019-11-04
一只码畜 / 0评论 2011-09-02