mysql查询-日期操作

本文简单讲解我们在编写sql时会使用到的有关日期的sql写法：

首先要介绍常用的日期类的函数，其次在根据函数来演示常见的日期操作的写法。

以下函数或者关键字加0（+0）后会将日期时间、日期、时间、字符串转换为数字！

目录

一、常用函数： - 1 -

1、获取当前日期和时间 - 1 -

2、 获取当前日期 - 1 -

3、 获取当前时间 - 1 -

4、 返回指定日期/时间表达式的日期部分或将文本转为日期格式 - 2 -

5、 返回日期时间中的年、月、日、时、分、秒。 - 2 -

6、 返回日期时间对应的季节、一年的第几天、月份名称、星期几的索引和星期名称。 - 2 -

7、 根据日期时间返回时间戳 - 2 -

8、 根据时间戳返回日期时间 - 3 -

9、 返回日期的天数（从0开始计算） - 3 -

10、 格式化日期（日期时间转换为字符串） - 3 -

11、 将字符串转换为日期时间 - 3 -

12、 返回两个日期时间的时间差 - 4 -

13、 返回两个日期的相差天数 - 4 -

14、 返回两个日期时间的间隔时间 - 4 -

15、 对日期时间进行加减运算 - 4 -

二、 操作篇： - 5 -

一、常用函数：

1、获取当前日期和时间

使用now()函数可以获取当前系统的日期和时间

例：select now(); --2022-08-17 14:13:20

select now()+0; --20220817141320

获取当前日期

使用current_date关键字或者curdate()、current_date()函数可以获取当前系统的日期

例：select curdate(); --2022-08-17

select curdate()+0; --20220817

select current_date; --2022-08-17

select current_date(); --2022-08-17

获取当前时间

使用current_time关键字或者curtime()、current_time()函数可以获取当前系统的时间

例：select curtime(); --14:13:20

select curtime()+0; --141320

select current_time; --14:13:20

select current_time(); --14:13:20

返回指定日期/时间表达式的日期部分或将文本转为日期格式

使用date()函数可以获取日期部分

例：select date('2022-7-8'); --2022-07-08

select date('2022-7-8')+0; --20220708

select date(now()); --2022-07-08

*这里返回的是日期格式！

返回日期时间中的年、月、日、时、分、秒。

①year()函数返回年份1000~9999

例：select year('2022-8-17 11:30:48'); --2022

②month()函数返回月份1~12

例：select month('2022-8-17 11:30:48'); --8

③day()/dayofmonth()函数返回第几日1~31

例：select day('2022-8-17 11:30:48'); --17

④hour()函数返回小时0~23

例：select hour('2022-8-17 11:30:48'); --11

⑤minute()函数返回分0~59

例：select minute('2022-8-17 11:30:48'); --30

⑥second()函数返回秒0~59

例：select second('2022-8-17 11:30:48'); --48

返回日期时间对应的季节、一年的第几天、月份名称、星期几的索引和星期名称。

①quarter()函数返回季节1~4 （春、夏、秋、东）

例：select quarter('2022-8-22 16:07:51'); --3

②dayofyear()函数返回一年的第几天1~366

例：select dayofyear('2022-8-22 16:07:51'); --234

③monthname()函数返回月份对应的名称（英文）

例：select monthname('2022-8-22 16:07:51'); --August （8月）

④weekday()函数返回星期索引(0=星期一，1=星期二, ……6= 星期天)

例：select weekday('2022-8-22 16:07:51'); --0 （星期一）

⑤dayofweek()函数返回星期索引(1=星期天，2=星期一, ……7=星期六)。这些索引值对应于ODBC标准。

例：select dayofweek('2022-8-22 16:07:51'); --2 （星期一）

⑥dayname()函数返回星期的名称（英文）

例：select dayname('2022-8-22 16:07:51'); --Monday （星期一）

根据日期时间返回时间戳

函数：unix_timestamp([date])

函数说明：返回一个unix时间戳(从'1970-01-01 00:00:00'开始的秒数,date默认值为当前时间)

参数说明：date 日期时间(此参数不填则为系统时间)

例：select unix_timestamp(); --1660895587

select unix_timestamp('2022-8-19 15:53:01'); --1660895581

根据时间戳返回日期时间

函数：from_unixtime(unix_timestamp)

函数说明：以'yyyy-mm-dd hh:mm:ss'或yyyymmddhhmmss格式返回时间戳的值(根据返回值所处上下文是字符串或数字)

参数说明：unix_timestamp 时间戳

例：select from_unixtime(1660895581); --2022-08-19 15:53:01

select from_unixtime(1660895581)+0; --20220819155301

返回日期的天数（从0开始计算）

函数：to_days(datetime)

函数说明：返回0到指定日期的天数

参数说明：datetime 日期/日期时间

例：select to_days(now()); --738754

select to_days('2022-8-22'); --738754

格式化日期（日期时间转换为字符串）

函数：date_format(date,format)

函数说明： 根据格式串format 格式化⽇期或⽇期和时间值date，返回结果串（一般情况用户获取日期的年月日和时间）

参数说明： date 日期/日期时间 format 格式串

在format格式串中可用标志符：

%M	月名字(january……december)	%b	缩写的月份名字(jan……dec)
%Y	年，数字，4位	%y	年，数字，2位
%d	月份中的天数，数字（00,01,...31）	%e	月份中的天数，数字（0,1,...31）
%m	月，数字（01,02,...12）	%c	月，数字（1,2,...12）
%h	十二时制的小时（00,01,...12）	%k	二十四时制的小时(0,1,...23）
%i	分钟, 数字（00,01,...59）	%r	时间,12 小时(hh:mm:ss [ap]m)
%s	秒(00……59) %p am或pm	%j	一年中的天数（001,002,...366）
%w	一个星期中的天数（0=sunday(星期天) ...6=saturday(星期六) ）	%u	一年中的周数（1,2,...53）

例：

select date_format('2022-3-11 12:15:51','%Y-%m-%d'); --2022-03-11

select date_format('2022-3-11 12:15:51','%r@@@%M###%w'); --12:15:51 PM@@@March###5

select date_format(now(),'%k:%i:%s'); --9:21:50

将字符串转换为日期时间

函数：str_to_date(str,format)

函数说明：将指定的时间格式的字符串按照格式转换为日期时间类型的值。str要与format的格式保持一致，否则会报错。

参数说明： str 时间格式的字符串 format 格式串

在format格式串中可用标志符：

%Y	年，数字，4位	%s	秒(00……59) %p am或pm
%d	月份中的天数，数字（00,01,...31）	%e	月份中的天数，数字（0,1,...31）
%m	月，数字（01,02,...12）	%c	月，数字（1,2,...12）
%h	十二时制的小时（00,01,...12）	%k	二十四时制的小时(0,1,...23）
%i	分钟, 数字（00,01,...59）	%r	时间,12 小时(hh:mm:ss [ap]m)

例：

select str_to_date('2022-8-21','%Y-%m-%d'); --2022-08-21

select str_to_date('2022-8-1 1:20:51','%Y-%c-%e %r'); --2022-08-01 01:20:51

返回两个日期时间的时间差

函数：timestampdiff(type,expr1,expr2)

函数说明：返回起始日expr1和结束日expr2之间的时间差整数。时间差的单位由type指定

参数说明： type 时间差单位 expr1 起始日期/日期时间 expr2 结束日期/日期时间

type中的时间差单位如下：

second	秒	day	天
minute	分	month	月
hour	时	year	年

例：

select timestampdiff(hour,'2022-8-18 12:45:12','2022-8-18 16:12:51'); --3

select timestampdiff(day,'2022-8-18','2022-8-20'); --2

select timestampdiff(day,'2021-8-18','2022-8-20'); --367

select timestampdiff(day,'2022-8-20 17:45:51','2022-8-22 16:35:51'); --1 *这里为什么不是2?因为起始日期是从17:45:51开始计算的，结束日期的时分秒没超过这个时间就不足一天

返回两个日期的相差天数

函数：datediff(date1 ,date2)

函数说明：计算两个日期间隔的天数，即#date1 - date2

参数说明：date1 日期1 date2 日期2

例：select datediff('2022-8-31','2022-7-11'); --51

select datediff('2022-8-31 2:10:10','2022-7-11 4:10:10'); --51

返回两个日期时间的间隔时间

函数说明：timediff(time1,time2)

函数说明：计算两个时间的间隔时间，即time1-time2。注意的是两个时间的格式必须一致！

参数说明：time1 时间1 time2 时间2

例：select timediff('10:15:21','6:02:12'); --04:13:09

对日期时间进行加减运算

函数：① adddate(date,interval expr type) -加

② date_add(date,interval expr type) -加

③ subdate(date,interval expr type) -减

④ date_sub(date,interval expr type) -减

以上函数说明：其中，date是一个日期或日期时间的值；expr是对date进行加减法的一个表达式字符串或一个数字；type指明表达式expr应该如何被解释，是减去1天还是一年等。

type和expr的对应关系：

type值	含义	expr格式
second	秒	seconds
minute	分钟	minutes
hour	时	hours
day	天	days
month	月	months
year	年	years
Minute_second	分钟和秒	“minutes:seconds”
Hour_minute	小时和分钟	“hours:minutes”
day_hour	天和小时	“days-hours”
year_month	年和月	“years-months”
hour_second	小时，分钟和秒	“hours:minutes:seconds”
day_minute	天，小时和分钟	“days hours:minutes”
day_second	天，小时，分钟和秒	“days hours:minutes:seconds”

例：

select adddate('2022-8-19 15:22:12',interval 2 hour); --2022-08-19 17:22:12

select adddate('2022-8-19 15:22:12',interval '10-4' year_month); --2032-12-19 15:22:12

select date_add('2022-8-19 15:22:12',interval '2 2:12' day_minute); --2022-08-21 17:34:12

select date_sub('2022-8-19',interval 6 day); --2022-08-13

select subdate('2022-8-19 15:22:12',interval '6-14' day_hour); --2022-08-13 01:22:12

操作篇：

1、