项目中的统计报表作的很多，需求中有按周、月统计数据的。查看了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()的语法，看后快晕过去了，这么低级的错误我怎么之前一直没有发现呢？
|
%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
|
分享到：
相关推荐
您可能感兴趣的文章:MySQL中日期比较时遇到的编码问题解决办法PHP以及MYSQL日期比较方法mysql 获取当前日期函数及时间格式化参数详解mysql unix准换时间格式查找指定日期数据代码MySql日期查询语句详解深入mysql ...
在MySQL中，DATE_FORMAT函数是一个非常实用的日期时间格式化工具，它允许用户根据自定义的格式来展示日期和时间数据。然而，在实际使用中，DATE_FORMAT函数有一些需要注意的细节，尤其是在涉及到字符集和校对集...
MySQL DATE_FORMAT 函数是数据库操作中非常实用的功能，它允许用户根据需求自定义日期和时间的显示格式。在处理日期和时间数据时，DATE_FORMAT 函数可以帮助我们创建更易读、更符合特定需求的输出。以下是对 DATE_...
3. 日期和时间函数：如NOW()获取当前日期和时间，DATE_FORMAT()用于格式化日期和时间，DATE_ADD()和DATE_SUB()可以对日期进行加减操作。 4. 转换函数：如CAST()和CONVERT()用于在不同数据类型间转换，以及INET_ATON...
本文转自：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_FORMAT`函数是MySQL特有的一种日期时间格式化方法，用于将日期或时间数据转换为指定的格式。例如，你可以使用`DATE_FORMAT(date_column, '%Y-%m-%d')`来将日期列转换为'年-月-日'的格式。这个压缩包...
### Oracle中的TO_DATE和TO_CHAR函数详解 #### 一、TO_DATE函数 **TO_DATE**函数主要用于将字符串转换成日期格式。在Oracle数据库中，这是一个非常实用的功能，尤其是在处理日期和时间相关的数据时。 ##### 1. ...
本文将对MySQL中常用的日期函数进行总结，并通过示例来说明它们的具体用法。 #### 1. 获取当前日期和时间 - **NOW()**: 返回当前的日期和时间。 ```sql SELECT NOW(); ``` - **CURRENT_DATE()**: 返回当前的...
在MySQL中，可以通过`YEARWEEK()`函数来获取某日期对应的年和周的信息。为了查询本周的数据，可以使用以下SQL语句： ```sql SELECT name, submittime FROM enterprise WHERE YEARWEEK(date_format(submittime, '%Y-%...
再来说说`DATE_FORMAT`函数，它是MySQL中用于格式化日期时间的函数。`DATE_FORMAT(sch.create_time, '%Y-%m-%d %H:%i:%s') as createtime`这一句将`sch.create_time`的时间戳转换成"年-月-日 时:分:秒"的格式，使...
在MySQL数据库中，处理日期和时间是常见的任务，特别是在数据分析和报表生成中。本文将深入探讨两个关键的函数类别：时间差函数和日期转换计算函数，以帮助理解如何有效地进行日期和时间操作。 1. **时间差函数**：...
按月统计则相对简单，只需使用`MONTH([date])`： ```sql SELECT SUM(consume), MONTH([date]) FROM consume_record WHERE YEAR([date]) = '2006' GROUP BY MONTH([date]); ``` 对于季度统计，我们可以结合`...
总结来说，MySQL的`FROM_UNIXTIME`、`UNIX_TIMESTAMP`和`DATE_FORMAT`函数提供了强大的日期和时间处理能力，帮助我们方便地在Unix时间戳和日期格式之间转换，以及对日期进行格式化输出。理解并熟练运用这些函数，...
文章目录SQL Date 函数MySQL Date 函数NOW() 函数CURDATE() 函数CURTIME() 函数DATE() 函数EXTRACT() 函数DATE_ADD() 函数DATE_SUB() 函数DATEDIFF() 函数DATE_FORMAT() 函数SQL Server Date 函数GETDATE() 函数...
在"MySQL.rar_5M6_MYSQL"这个压缩包中，我们可能找到了关于MySQL数据库的一些教程或参考资料，重点关注了`SELECT FROM`语句以及`DATE_FORMAT()`函数，还有可能涉及了SQL中的`INNER JOIN`操作。 1. `SELECT FROM`...
- **按月筛选**：使用 `MONTH()` 函数。 - **按年筛选**：使用 `YEAR()` 函数。 这些方法可以根据实际需求灵活组合使用，实现更加精确的时间筛选。 通过以上介绍，我们可以看到 MySQL 提供了丰富的日期和时间函数...
MySQL 中日期时间的格式可以使用 DATE_FORMAT 函数来格式化。例如，使用 `SELECT DATE_FORMAT(NOW(), '%Y%m%d%H%i%S')` 可以获取当前时间的年月日时分秒格式。 日期时间运算 MySQL 中可以使用 DATE_ADD 和 DATE_...