Mysql 中使用DATE_FORMAT函数按月、周统计数据

最新推荐文章于 2025-05-18 00:44:45 发布
最新推荐文章于 2025-05-18 00:44:45 发布
阅读量1.8k 收藏 3
点赞数
分类专栏： mysql
本文深入探讨MySQL中的Date_format函数在按周、月统计数据时的特殊用法及注意事项，特别是关于年份表示的不同格式（%X与%Y）可能导致的问题。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

 #按天统计
　　SELECT date,SUM(macs) AS macsAll FROM tb 
　　WHERE DATE_FORMAT(date,'%Y-%m-%d')<'2014-03-01' GROUP BY date
　　#按周统计
　　SELECT DATE_FORMAT(date,'%x-%v') AS date,SUM(macs) AS macs 
　　FROM tb 
　　GROUP BY date 
　　#按月统计
　　SELECT SUM(macs) AS macs,MONTH(date) AS date,names FROM tb  
　　WHERE names='上海' GROUP BY date;


项目中的统计报表作的很多，需求中有按周、月统计数据的。查看了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

 

 

%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

 

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()的语法，看后快晕过去了，这么低级的错误我怎么之前一直没有发现呢？

MySQL｜MySQL 中 `DATE_FORMAT()` 函数的使用
m0_67392010的博客
02-14 378
是 MySQL 中的一个内置函数，用于格式化日期和时间数据。它可以根据指定的格式字符串来展示日期和时间，使得数据更容易阅读和理解。本文档将详细介绍函数的使用方法及其常见应用场景。
「MySQL」日期时间格式化函数 DATE_FORMAT() 的使用详解
web13688565871的博客
02-11 2373
是MySQL中用于格式化输出日期时间的函数，我们可以通过使用该函数满足大多数格式化日期时间的应用场景，本文的示例展示了如何获取当前日期和时间并将其格式化为一个常见的格式，您可以根据需要调整格式字符串来满足您的具体需求。但需要注意的是，不同的数据库系统可能支持不同的格式字符串，所以在使用时需要参考相应数据库的文档来确定正确的格式字符串。
Mysql中Date_format函数按周、月统计总结
远去的渡口
02-25 1205
      项目中的统计报表作的很多，需求中有按周、月统计数据的。查看了Mysql的API，发现Date_format是格式化日期的，看了Date_format()的具体说明后就用这个函数按周统计，sql大致如下： select   DATE_FORMAT(check_date ,'%X-%V')  dates,avg(weight)/10000  weight from ho_body whe...
mysql日期格式
昌哥(Java)
01-27 458
知识关键词：DATE_FORMAT  select DATE_FORMAT(create_time,'%Y%u') weeks,count(caseid) count from tc_case group by weeks;   select DATE_FORMAT(create_time,'%Y%m%d') days,count(caseid) count from tc_case g...
mysql：DATE_FORMAT函数详解：日期格式化的艺术
weixin_72058778的博客
05-18 913
灵活展示各种日期格式简化分组统计操作提升报表可读性满足多语言环境需求正确使用这个函数可以显著提高SQL查询的实用性和输出结果的可读性。
ASP 取日期函数
huike008的博客
05-21 326
ASP 取日期函数 年月日 显示： 今日是： 2006年5月24日 星期三 VBScript有许多函数，使你可以得到各种格式的日期和时间。你已经用过了这些函数中的一个。你可以用函数NOW返回当前的日期和时间： 程序代码 At the tone,the time will bi: 你应该注意，返回的日期和时间是你的Web服务器的系统时钟的日期和时间。如果身处纽约的某个人在看你的网页，...
mysql根据周查询统计，比如week()或者DATE_FORMAT或者跨年要注意的几点
做你想做的人，没有时间的限制，只要愿意，什么时候都可以start。
07-10 6964
目录 前言： 周的划分情况： 1.周的起始 2.周的跨年 3.一年有多少周? Mysql的做法： 每周的第一天： 返回值区间： 如何得出返回值是1： 总结： 其他： 前言： 周检索和月检索是不一样的，月检索是超级简单。 但是周检索稍不注意，就会入坑，而且是很久之后才发现的，比如元旦。 在详细接触周检索之前，你需要了解一些关于周的知识。 周的划分情况： 1....
mysql按照周/月统计数据
yayaqwl的博客
06-27 1854
mysql按照周统计数据
Mysql 日期时间 DATE_FORMAT(date,format)
12-15
本文转自：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将日期转换成具体的周几维度，我建议…
weixin_44976611的博客
05-28 5821
如果你想用sql将日期转换成具体的"周几"维度，我建议直接使用这两个函数就好了 方式一： -- 在这里，0代表周天，1-6 依次代表周一到周六 SELECT DATE_FORMAT('2020-05-23','%w'); -- 输出：6;周六？ SELECT DATE_FORMAT('2020-05-24','%w'); -- 输出：0;周天？ SELECT DATE_FORMAT('2020-05-25','%w'); -- 输出：1;周一？ SELECT DATE_FORMAT('2020-05-26
MySQL的跨年周统计问题（%X-%V）
鲁肃小生
01-14 1万+
MySQL的跨年周统计问题    最近在做项目的时候，发现了一个报表在进行周统计并且跨年的时候会出现问题，具体问题截图如下：(主要与%X-%V的时间格式符有关)2016-12-18 ~~ 2017-01-13 周统计 图12015-12-20 ~~ 2016-01-17 周统计 图2    这两张图中，周统计中，代码中的sql时间格式化为DATE_FORMAT(FROM_UNIXTIME(reg
MYSQL 周统计函数 data_format YEARWEEK​​​​​​​
Henry_Lin_Wind的博客
07-02 1930
周转换函数采用 data_format，修饰符 %x-%v select date_format("2018-12-31","%x-%v") %x ：年，星期一是周的第一天 %v ：周, 星期一是周的第一天 该函数中的跨年周统计 ： 下一年的第一个周一之前的天数如果能超过3天(不包括3天)，那么计算为第 1 周。否则就计算为上一年的最后一周（例：201601...
直接在SQL中对日期格式进行转换yyyy/MM/dd到yyyy-MM-dd【DATE_FORMAT()】
IKonwNothing的博客
02-28 1万+
直接在SQL中对日期格式进行转换yyyy/MM/dd到yyyy-MM-dd【DATE_FORMAT()】
mysql内置函数DATE_FORMAT()用法及注意事项
weixin_38225763的博客
09-13 5379
mysql内置函数DATE_FORMAT()用法及注意事项。
MySQL 查询日期格式化
qq_42617840的博客
11-23 2544
实现函数：DATE_FORMATDATE_FORMAT DATE_FORMAT( c.start_time, '%H' ) 支持的格式如下： %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) %
mysql date_format的使用——年月日时分秒
热门推荐
猎人在吃肉
11-10 2万+
（%Y-%m-%d %H:%i:%s）年月日，时分秒select id,content,is_read,DATE_FORMAT(create_date,'%Y-%m-%d %H:%i:%s') create_date from lcs_notice where del_flag=0 and id=? and user_uuid=?（%Y-%m-%d %H:%i） 年月日，时分select co
Mysql 按当天、当月、上月及按日期范围查询 DATE_FORMAT( date, ‘%Y%m‘ )
moyuhao的博客
10-19 2773
//1、根据开始时间结束时间查询 SELECT * FROM record WHERE 1=1 <if test="dateEnd != null and dateEnd != ''"> AND operation_time <![CDATA[ < ]]> str_to_date(#{dateEnd},'%Y-%m-%d %H:%i:%s')</if> <if test="dateStart != null and dateStart != ''"> .
MySQL中DATE_FORMAT() 函数对日期格式化参数与JAVA中各种日期表示字母对比
xqg1316的博客
01-19 1136
MySQL 中使用 DATE_FORMAT() 函数对日期格式化 项目中的统计报表作的很多，需求中有按周、月统计数据的。查看了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.
【MySQL 】MySQL 里面的DATE_FORMAT怎么用？
qq_41755239的博客
03-19 2807
其中 %Y 表示四位年份，%m 表示两位月份，%d 表示两位日期。%W：完整的星期几名称（Sunday - Saturday）%a：缩写的星期几名称（Sun - Sat）%b：缩写的月份名称（Jan - Dec）%H：24小时制的小时（00 - 23）%h：12小时制的小时（01 - 12）%Y：四位年份（例如：2024）%m：两位月份（01 - 12）%d：两位日期（01 - 31）%i：两位分钟（00 - 59）%s：两位秒数（00 - 59）%y：两位年份（例如：24）%c：月份（1 - 12）
在mysql数据库中的date_format函数使用方式
最新发布
06-28
<think>我们正在讨论的是MySQL中的DATE_FORMAT函数，而不是Greenplum。根据引用内容，DATE_FORMAT是MySQL中用于日期格式化的函数。用户的问题：如何查询MySQL数据库中date_format函数的使用方式根据引用[1][2][3]：基本语法：DATE_FORMAT(date,format)参数：date：要格式化的日期或时间表达式format：指定输出格式的字符串，由特定的格式说明符组成常用格式说明符：%Y：四位年份（例如：2024）%y：两位年份（例如：24）%m：两位月份（01到12）%c：月份（1到12，不带前导零）%d：两位日期（01到31）%e：日期（1到31，不带前导零）%H：24小时制的小时（00-23）%h：12小时制的小时（01-12）%i：两位分钟（00-59）%s：两位秒（00-59）%p：AM或PM%W：星期几的全称（例如：Sunday）%a：星期几的缩写（例如：Sun）%M：月份的全称（例如：January）%b：月份的缩写（例如：Jan）示例（来自引用[3]）：DATE_FORMAT(NOW(),'%b%d%Y%h:%i%p')->例如：Jan01202402:30PMDATE_FORMAT(NOW(),'%m-%d-%Y')->例如：01-01-2024DATE_FORMAT(NOW(),'%d %b %y')->例如：01Jan24DATE_FORMAT(NOW(),'%d%b%Y%T:%f')->例如：01 Jan202414:30:00.000000另外引用[2]中的例子：SELECTDATE_FORMAT(my_date,'%Y-%m-%d') ASformatted_dateFROM my_table;使用步骤：1.确定要格式化的日期列或日期值（可以是列名，也可以是NOW()等函数返回的当前时间）2.根据需要选择格式说明符组合成格式字符串注意事项：-格式字符串中，除了格式说明符以外的字符都会原样输出，因此可以添加分隔符（如-、/、空格）和文字（如“年”、“月”）。但注意，如果要输出%字符，需要用两个百分号（%%）转义。-格式说明符区分大小写，例如%Y和%y不同，%M和%m不同。示例：包含文字SELECT DATE_FORMAT('2024-01-01', '%Y年%m月%d日');->2024年01月01日因此，回答如下：</think>### MySQL中`DATE_FORMAT()`函数使用详解 `DATE_FORMAT()`是MySQL中用于将日期/时间值格式化为指定字符串的核心函数，通过**格式化说明符**实现灵活的时间展示。以下是详细使用方法： #### 基础语法 ```sql DATE_FORMAT(date, format) ``` - **date**：要格式化的日期/时间列或表达式（如`NOW()`、`CURDATE()`或表字段） - **format**：指定输出格式的字符串，由**格式化说明符**和普通字符组成 #### 常用格式化说明符 | 说明符 | 含义 | 示例输出 | |--------|--------------|---------------| | `%Y` | 四位年份 | 2024 | | `%y` | 两位年份 | 24 | | `%m` | 月份(01-12) | 07 | | `%c` | 月份(1-12) | 7 | | `%d` | 日期(01-31) | 15 | | `%e` | 日期(1-31) | 15 | | `%H` | 24小时制小时 | 14 | | `%h` | 12小时制小时 | 02 | | `%i` | 分钟(00-59) | 30 | | `%s` | 秒(00-59) | 45 | | `%p` | AM/PM | PM | | `%W` | 星期全称 | Monday | | `%a` | 星期缩写 | Mon | | `%M` | 月份全称 | July | | `%b` | 月份缩写 | Jul | #### 实际应用示例 1. **基础日期格式化** ```sql -- 输出：2024-07-15 SELECT DATE_FORMAT(NOW(), '%Y-%m-%d'); ``` 2. **包含文字的复杂格式** ```sql -- 输出：2024年07月15日 14:30:45 SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H:%i:%s'); ``` 3. **按月份聚合数据** ```sql -- 统计每月订单量 SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, COUNT(*) AS order_count FROM orders GROUP BY month; ``` 4. **12小时制时间显示** ```sql -- 输出：02:30 PM SELECT DATE_FORMAT('2024-07-15 14:30:45', '%h:%i %p'); ``` 5. **英文日期格式** ```sql -- 输出：Jul 15, 2024 SELECT DATE_FORMAT(NOW(), '%b %e, %Y'); ``` #### 注意事项 1. **普通字符处理**：非说明符字符（如`年`、`-`、`,`）直接输出 2. **大小写敏感**：`%M`（月份全称）和`%m`（数字月份）效果不同 3. **空值处理**：若`date`为`NULL`，结果返回`NULL` 4. **性能优化**：避免在`WHERE`条件中使用格式化函数，可能无法使用索引 > 示例参考：DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p') 输出类似 "Jul 15 2024 02:30 PM"[^3]
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值