MySQL里获取当前week、month、quarter,以及date(),date_format()和str_to_date(),DATEDIFF()操作

本文围绕MySQL数据库的时间操作展开,介绍了按年、季度、月、周、天查询数据的SQL语句,如使用year、quarter等函数。还讲解了DATE、str_to_date、date_format、DATEDIFF等时间函数的用法,包括参数和示例,为数据库时间相关操作提供参考。

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

1.查询某年的数据

1.1 select * from oa_item_info where created like '2018-%';

1.2 select * from oa_item_info where left(created,4)='2018';

1.3 select * from oa_item_info where year(created)='2018';

今年的数据:

select * from oa_item_info where year(created)=year(now());

上一年的数据:

select * from oa_item_info where year(created)=year(date_sub(now(),interval 1 year));

date_sub()函数:date_sub 


2.查询某季度的数据

select QUARTER(created)  as quartername ,created from oa_item_info ;

先看一下quarter函数返回的数据,第一列是quartername,第二列是created

1-3月返回1,4-6月返回2,7到9月返回3,10到12月返回4

并不是搜索本季度的数据:

select * from oa_item_info where QUARTER(created)=QUARTER(now());

这条sql语句返回的是所有年份,当前季度的数据,比如现在是4月,会把所有年份4到6月的数据都检索出来

搜索本季度的数据:

加上本年的限制条件

 select * from oa_item_info where QUARTER(created)=QUARTER(now()) and year(created)=year(now());

3.查询某月的数据

 select month(created) as monthname,created from oa_item_info;

看一下返回数据:第一列是monthname,第二列是created

所有年份2月的数据

 select * from oa_item_info where month(created)=2;

加上年份的限定条件就可以了

4.查询某周的数据

 select week(created) as weekname,created from oa_item_info ;

看一下返回数据:第一列是weekname,第二列是created

返回的是一年中的第几周

本周的数据:

 select  created   from oa_item_info where week(created)=week(now()) and year(created)=year(now());

select * from oa_item_info where YEARWEEK(date_format(created,'%Y-%m-%d')) = YEARWEEK(now()) ;

看一下week和yearweek的区别:

数据库中加入两条周数一致的日期:

select week('2017-04-20');

 
select week('2018-04-25');

看一下yearweek的返回值:

select yearweek('2018-04-25');

看一下搜索结果:

select  created   from oa_item_info where week(created)=week(now());

week把两条年份不一样的都搜出来了

 select created  from oa_item_info where YEARWEEK(date_format(created,'%Y-%m-%d')) = YEARWEEK(now()) ;

select created  from oa_item_info where YEARWEEK(created) = YEARWEEK(now()) ;

不用date_format函数也可以

yearweek只搜出了今天的

值得注意的是,他们默认都是从周日开始算的,需要从周一开始计算时,需要加入第二个参数1:week(created,1)

date_format

上一周的数据:

select  *   from oa_item_info where YEARWEEK(date_format(created,'%Y-%m-%d')) = YEARWEEK(now())-1;

5.查询某天的数据

今天的数据:

 select  created from oa_item_info where to_days(created) = to_days(now());

to_days();返回从0年开始的天数;

 select to_days(now())  ;

from_days();根据天数,返回日期;

select from_days(737173)  ;

昨天的数据:

这是很多博文的语句,看一下,现在是24号,会搜出今天和昨天数据

 select  created from oa_item_info where to_days(now())-to_days(created)<=1 ;


 select  created from oa_item_info where to_days(now())-to_days(created)=1 ;

搜出的是昨天的:


总结:

1.year(),从时间字段获取年

2.quarter(),从时间字段获取季度

3.month(),从时间字段获取月

4.week(),从时间字段获取周

5.yearweek(),从时间字段获取年和周

6.date_sub(), 从时间字段减去指定时间间隔

7.date_format(),时间格式化

8.to_days(),返回从0年开始的天数;

9.from_days(),根据天数,返回日期;
 

-------------------------------------------------------------

1、DATE() 函数:返回日期或日期时间表达式的日期部分;

2、str_to_date()函数:按照指定日期或时间显示格式 将字符串转换为日期或日期时间类型;

3、date_format()函数:按照指定日期或时间显示格式 输出日期或日期时间;

1、date(datestring)

datestring是合法的日期表达式

如:

SELECT date('2017-02-09 15:25:46.635')
FROM dual;
-->'2017-02-09'

2、date_format(datestring,format)

datestring参数是合法的日期。format 规定日期/时间的输出格式。

如:


SELECT STR_TO_DATE('2017-02-09 15:25:46.635','%Y-%m-%d')
FROM dual;
-->'2017-02-09'

SELECT STR_TO_DATE('2017-02-09 15:25:46','%Y-%m-%d %H:%i:%s')
FROM DUAL;
-->'2017-02-09 15:25:46'

SELECT STR_TO_DATE('2017-02-09 15:25','%Y-%m-%d %k:%i')
FROM DUAL;
-->'2017-02-09 15:25:00'

3、date_format(datestring,format)

datestring参数是合法的日期。format 规定日期/时间的输出格式。

如:

当前时间按月-日 时:分:秒显示:

SELECT DATE_FORMAT(NOW(),'%m-%d %h:%i %p')
FROM dual;
-->'02-09 06:00 PM'

当前时间按 年-月-日 时:分:秒 AM/PM显示:

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %h:%i:%s %p')
FROM dual;
-->'2017-02-09 06:00:35'

当前时间按 年 周 日 时:分:秒显示:

SELECT DATE_FORMAT(NOW(),'%Y %b %d %T')
FROM dual;
-->'2017 Feb 09 18:04:13'

可以使用的格式有:

 

格式描述
%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)
%pAM 或 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 位

4.DATEDIFF() 函数

DATEDIFF() 函数返回两个日期之间的时间。

语法

DATEDIFF(datepart,startdate,enddate)

startdate 和 enddate 参数是合法的日期表达式。

datepart 参数可以是下列的值:

datepart缩写
yy, yyyy
季度qq, q
mm, m
年中的日dy, y
dd, d
wk, ww
星期dw, w
小时hh
分钟mi, n
ss, s
毫秒ms
微妙mcs
纳秒ns

例子 1

使用如下 SELECT 语句:

SELECT DATEDIFF(day,'2008-12-29','2008-12-30') AS DiffDate

结果:

DiffDate
1

例子 2

使用如下 SELECT 语句:

SELECT DATEDIFF(day,'2008-12-30','2008-12-29') AS DiffDate

结果:

DiffDate
-1

参考:

https://www.2cto.com/database/201702/596860.html

https://blog.youkuaiyun.com/ymk0375/article/details/80059395

https://blog.youkuaiyun.com/youngqj/article/details/7071314

http://www.w3school.com.cn/sql/func_datediff.asp

<think>好的,用户现在想了解MySQLDATE_FORMAT函数的用法。之前他们问了关于时间格式转换的问题,我详细介绍了CASTSTR_TO_DATE的使用,以及可能出现的错误。现在他们可能想更深入地格式化日期输出,比如将日期转换成特定的字符串形式,比如“年-月-日”或者其他格式。 首先,我需要确认DATE_FORMAT的基本用法。DATE_FORMAT函数主要用于将日期或时间值按照指定的格式转换为字符串。用户可能需要知道如何指定格式字符串,比如%Y代表四位年份,%m代表两位月份,等等。 接下来,用户可能的实际应用场景是什么?他们可能在生成报表、数据导出或者前端显示时需要不同的日期格式。比如,将日期显示为“2024年10月02日”或者“02-Oct-2024”这样的格式。这时候,DATE_FORMAT就非常有用。 然后,我需要考虑常见的格式符号,比如%Y、%y、%m、%d、%H、%i、%s,以及其他的修饰符,比如%b代表缩写月份名称,%M代表完整月份名称,%W代表星期几的全称等等。可能用户需要一些例子来理解这些符号的用法。 用户可能还会遇到一些问题,比如格式符号的大小写问题,比如%h是12小时制,而%H是24小时制,或者%p用于AM/PM,这些需要特别注意。此外,如果日期中有毫秒部分,DATE_FORMAT可能无法直接处理,需要结合其他函数,比如先转换成带有毫秒的类型,再提取。 另外,用户可能想知道如何处理NULL值,或者如果输入的日期格式不正确会发生什么。这时候可能需要提醒用户确保输入的日期是有效的,或者在转换前进行验证。 还要注意不同MySQL版本是否有差异,比如某些格式符号是否在旧版本中不被支持。比如,%f代表微秒,但需要MySQL 5.6.4及以上版本支持小数秒。 可能用户在使用DATE_FORMAT时遇到的常见错误包括格式符号拼写错误,比如将%m写成%M,导致月份变成名称而不是数字;或者忘记引号导致语法错误。需要给出一些错误示例解决方法。 最后,总结一下DATE_FORMAT的用法,强调格式字符串的灵活性,以及如何结合其他函数来处理更复杂的需求,比如结合CURDATE()获取当前日期并格式化。给出一些实用的例子,比如生成报表中的日期格式,日志记录中的时间戳格式等。</think>### ▌MySQL DATE_FORMAT 函数详解 `DATE_FORMAT()` 是 MySQL 中用于将日期/时间类型 **格式化为指定字符串** 的核心函数,其语法为: ```sql DATE_FORMAT(date, format) ``` 其中: - `date`:需格式化的日期/时间值(支持 `DATE`、`DATETIME`、`TIMESTAMP` 类型) - `format`:由 **格式说明符** 组成的字符串,定义输出样式 --- ### ▌1. 常用格式说明符速查表 | 说明符 | 作用 | 示例(输入 `2024-10-02 18:20:25`) | |--------|-----------------------|-------------------------------------| | `%Y` | 四位年份 | 2024 | | `%y` | 两位年份 | 24 | | `%m` | 两位月份(01-12) | 10 | | `%c` | 月份(1-12) | 10 | | `%d` | 两位日期(01-31) | 02 | | `%e` | 日期(1-31) | 2 | | `%H` | 24小时制小时(00-23) | 18 | | `%h` | 12小时制小时(01-12) | 06(需搭配 `%p`) | | `%i` | 分钟(00-59) | 20 | | `%s` | 秒(00-59) | 25 | | `%f` | 微秒(000000-999999) | 000000(需原数据含小数秒) | | `%W` | 星期全名 | Wednesday | | `%a` | 星期缩写 | Wed | | `%M` | 月份全名 | October | | `%b` | 月份缩写 | Oct | | `%p` | AM/PM | PM | | `%%` | 转义字符`%` | % | --- ### ▌2. 典型应用场景 #### 场景一:标准化日期输出 ```sql SELECT DATE_FORMAT('2024-10-02', '%Y-%m-%d') AS formatted_date; -- 输出 → 2024-10-02(保持原格式) ``` #### 场景二:中文友好格式 ```sql SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H时%i分') AS chinese_format; -- 输出 → 2024年10月02日 18时20分 ``` #### 场景三:带星期/月份的复杂格式 ```sql SELECT DATE_FORMAT('2024-10-02', '%W, %M %e, %Y') AS full_format; -- 输出 → Wednesday, October 2, 2024 ``` #### 场景四:12小时制时间 ```sql SELECT DATE_FORMAT('18:20:25', '%h:%i:%s %p') AS twelve_hour; -- 输出 → 06:20:25 PM ``` --- ### ▌3. 高频问题与解决方案 #### 问题1:原数据不含时间部分时的默认值 ```sql -- 输入纯日期(无时间)时,时间部分按 00:00:00 处理 SELECT DATE_FORMAT('2024-10-02', '%H:%i:%s') AS default_time; -- 输出 → 00:00:00 ``` #### 问题2:处理 NULL 值 ```sql -- 若 date 参数为 NULL,结果返回 NULL SELECT DATE_FORMAT(NULL, '%Y-%m-%d') AS null_test; -- 输出 → NULL ``` #### 问题3:格式说明符错误 ```sql -- ❌ 混淆大小写(如 %M 误写为 %m) SELECT DATE_FORMAT('2024-10-02', 'Month: %m'); -- 输出 → Month: 10(非预期结果,正确应为 Month: October) ``` --- ### ▌4. 进阶技巧 #### 技巧一:动态拼接格式 ```sql SET @format = CONCAT('%Y', '-', '%m', '-', '%d'); SELECT DATE_FORMAT(CURDATE(), @format) AS dynamic_format; -- 输出 → 2024-10-02 ``` #### 技巧二:提取季度 ```sql -- 结合 QUARTER 函数 SELECT DATE_FORMAT(NOW(), '%Y-Q%q') AS quarter; -- 输出 → 2024-Q4(假设当前时间为10月) ``` #### 技巧三:跨时区转换(需配置时区) ```sql SET time_zone = '+00:00'; SELECT DATE_FORMAT(CONVERT_TZ(NOW(), 'SYSTEM', 'America/New_York'), '%Y-%m-%d %H:%i:%s') AS ny_time; ``` --- ### ▌总结 | 操作需求 | 推荐格式字符串 | |------------------------|------------------------------------| | 标准日期 | `'%Y-%m-%d'` | | 带中文的时间 | `'%Y年%m月%d日 %H时%i分'` | | 含星期/月份的详细格式 | `'%W, %M %e, %Y'` | | 12小时制时间 | `'%h:%i:%s %p'` | | 文件命名友好格式 | `'%Y%m%d_%H%i%s'` | 通过灵活组合格式说明符,可满足从简单日期显示到复杂报表生成的各种需求。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值