Mysql--日期，时间相关
一、日期，时间戳格式转换
在数据库中，时间的存储格式一般为时间戳，但这样对于使用人员不太方便，故在查询时可以先转换格式
1.1、FROM_UNIXTIME()
将时间戳转换为日期格式
> select from_unixtime(1654162618,'%Y-%m-%d %H:%i:%s'); +-----------------------------------------------+ | from_unixtime(1654162618,'%Y-%m-%d %H:%i:%s') | +-----------------------------------------------+ | 2022-06-02 17:36:58 | +-----------------------------------------------+ 1 row in set (0.00 sec)
1.2、UNIX_TIMESTAMP ()
将日期转换为时间戳格式
> select unix_timestamp('2022-06-06 17:45:20'); +---------------------------------------+ | unix_timestamp('2022-06-06 17:45:20') | +---------------------------------------+ | 1654508720 | +---------------------------------------+ 1 row in set (0.00 sec)
1.3、示例
查询5月25号到6月2号之间的数据
> select * from test where start_time >= UNIX_TIMESTAMP('2022-05-25 00:00:00') and start_time <= UNIX_TIMESTAMP('2022-06-02 23:59:59');
1.4、参数
-
%M 月名字(January……December)
-
%W 星期名字(Sunday……Saturday)
-
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）
-
%Y 年, 数字, 4 位
-
%y 年, 数字, 2 位
-
%a 缩写的星期名字(Sun……Sat)
-
%d 月份中的天数, 数字(00……31)
-
%e 月份中的天数, 数字(0……31)
-
%m 月, 数字(01……12)
-
%c 月, 数字(1……12)
-
%b 缩写的月份名字(Jan……Dec)
-
%j 一年中的天数(001……366)
-
%H 小时(00……23)
-
%k 小时(0……23)
-
%h 小时(01……12)
-
%I 小时(01……12)
-
%l 小时(1……12)
-
%i 分钟, 数字(00……59)
-
%r 时间,12 小时(hh:mm:ss [AP]M)
-
%T 时间,24 小时(hh:mm:ss)
-
%S 秒(00……59)
-
%s 秒(00……59)
-
%p AM或PM
-
%w 一个星期中的天数(0=Sunday ……6=Saturday ）
-
%U 星期(0……52), 这里星期天是星期的第一天
-
%u 星期(0……52), 这里星期一是星期的第一天
-
%% 一个文字“%”。
二、获取当前时间，日期
now()可换成时间表达式，截取时间字段里需要的数据
mysql> select now(); +---------------------+ | now() | +---------------------+ | 2022-06-15 10:46:47 | +---------------------+ 1 row in set (0.00 sec) mysql> select curdate(); +------------+ | curdate() | +------------+ | 2022-06-15 | +------------+ 1 row in set (0.00 sec) mysql> select curtime(); +-----------+ | curtime() | +-----------+ | 10:46:59 | +-----------+ 1 row in set (0.00 sec) mysql> select week(now()); +-------------+ | week(now()) | +-------------+ | 24 | +-------------+ 1 row in set (0.00 sec) mysql> select month(now()); +--------------+ | month(now()) | +--------------+ | 6 | +--------------+ 1 row in set (0.00 sec) mysql> select quarter(now()); +----------------+ | quarter(now()) | +----------------+ | 2 | +----------------+ 1 row in set (0.00 sec) mysql> select year(now()); +-------------+ | year(now()) | +-------------+ | 2022 | +-------------+ 1 row in set (0.00 sec) mysql>
三、时间日期函数
3.1 DATE_ADD()
作用：用于向日期添加指定的时间间隔
语法： DATE_ADD(date, INTERVAL num type)
- date：时间格式，时间表达式，存储时间的字段
- num：时间间隔值，正数为加，负数为减
- type：时间间隔的单位：
-
- day： 天
- hour： 时
- minute： 分钟
- second： 秒
- microsecond：毫秒
- week： 周
- month： 月
- quarter：季度
- year： 年
3.1.1 示例：
简单运算
mysql> select date_add(now(), interval 1 hour); +----------------------------------+ | date_add(now(), interval 1 hour) | +----------------------------------+ | 2022-06-14 17:09:28 | +----------------------------------+ 1 row in set (0.00 sec) mysql> select date_add('2022-06-07 23:54:12', interval -1 day); +--------------------------------------------------+ | date_add('2022-06-07 23:54:12', interval -1 day) | +--------------------------------------------------+ | 2022-06-06 23:54:12 | +--------------------------------------------------+ 1 row in set (0.00 sec) mysql>
作为执行时间的判断条件，获取前一天的数据
select from_unixtime(notify_time, '%Y-%m-%d %H:%i:%s') as 告警通知时间, date_add(from_unixtime(notify_time, '%Y-%m-%d %H:%i:%s'), interval -1 day) as 前一天 from alarm_lists where notify_time >= UNIX_TIMESTAMP(date_add(now(), interval -2 day)) and notify_time <= UNIX_TIMESTAMP(date_add(now(), interval -1 day));
3.2 DATE_SUB()
作用：从日期减去指定的时间间隔
此函数与本文3.1 的DATE_ADD() 可以看到互为相反函数，作用语法，支持的type值基本相同，唯一是DATE_ADD() num 为正数时是相加，DATE_SUB() num 为正数时是相减
可参考3.1，在此不做过多叙述
示例：
mysql> select date_sub(now(),interval 1 day); +--------------------------------+ | date_sub(now(),interval 1 day) | +--------------------------------+ | 2022-06-14 10:25:42 | +--------------------------------+ 1 row in set (0.00 sec) mysql> select date_sub(now(),interval -1 day); +---------------------------------+ | date_sub(now(),interval -1 day) | +---------------------------------+ | 2022-06-16 10:25:46 | +---------------------------------+ 1 row in set (0.00 sec)
3.3 DATEDIFF()
作用：返回两个日期之间的天数，day1 - day2
注：只有值的日期部分参与计算
示例：
mysql> select datediff('2022-06-05 15:00:00','2022-06-04 20:00:00'); +-------------------------------------------------------+ | datediff('2022-06-05 15:00:00','2022-06-04 20:00:00') | +-------------------------------------------------------+ | 1 | +-------------------------------------------------------+ 1 row in set (0.00 sec) mysql>
3.4 PERIOD_DIFF()
作用：返回两个日期之间的月数，day1 -day2
注：只有值的月数部分参与计算
示例：
mysql> SELECT PERIOD_DIFF(201903, 201803); +-----------------------------+ | PERIOD_DIFF(201903, 201803) | +-----------------------------+ | 12 | +-----------------------------+ 1 row in set (0.00 sec) mysql>
3.5 TIMEDIFF()
作用：返回两个时间之间的差值，date1 -date2
注：两值必须为相同类型的参数，否则返回NULL
示例：
mysql> select timediff('2022-06-05 15:00:00','2022-06-04 20:00:00'); +-------------------------------------------------------+ | timediff('2022-06-05 15:00:00','2022-06-04 20:00:00') | +-------------------------------------------------------+ | 19:00:00 | +-------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select timediff('15:00:00','14:55:30'); +---------------------------------+ | timediff('15:00:00','14:55:30') | +---------------------------------+ | 00:04:30 | +---------------------------------+ 1 row in set (0.00 sec) mysql>
3.6 TIMESTAMPDIFF()
作用：返回两个时间表达式之间的差值，datetime_expr2 - datetime_expr1
语法： TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
注：datetime_expr1,datetime_expr2 可以是不同的数据类型，如date或datetime
unit：结果的单位，整数型
-
- day： 天
- hour： 时
- minute： 分钟
- second： 秒
- microsecond：毫秒
- week： 周
- month： 月
- quarter：季度
- year： 年
示例：
mysql> select timestampdiff(minute,'2022-06-05 12:00:00','2022-06-05 12:30:31') as t1,timestampdiff(minute,'2022-06-05 12:00:00','2022-06-05') as t2,timestampdiff(minute,'2022-06-05 12:00:00','2022-06-05 00:00:00') as t3; +------+------+------+ | t1 | t2 | t3 | +------+------+------+ | 30 | -720 | -720 | +------+------+------+ 1 row in set (0.00 sec) mysql>
相差时间：
mysql> SELECT NOW() 当前日期,DATE_ADD(NOW(),INTERVAL - 800 DAY) 历史日期,TIMESTAMPDIFF(DAY,DATE_ADD(NOW(), INTERVAL - 800 DAY),NOW()) AS 相差天数; +---------------------+---------------------+--------------+ | 当前日期 | 历史日期 | 相差天数 | +---------------------+---------------------+--------------+ | 2022-06-15 09:54:02 | 2020-04-06 09:54:02 | 800 | +---------------------+---------------------+--------------+ 1 row in set (0.00 sec) mysql>
两值相减作为判断条件：
if(TIMESTAMPDIFF(minute,from_unixtime(start_time, '%Y-%m-%d %H:%i:%s'),from_unixtime(end_time, '%Y-%m-%d %H:%i:%s')) > 5 and TIMESTAMPDIFF(minute,from_unixtime(start_time, '%Y-%m-%d %H:%i:%s'),from_unixtime(end_time, '%Y-%m-%d %H:%i:%s')) <= 10,1,0) as '5~10min',
3.7 TO_DAYS()
作用：返回一个从0年开始到指定日期之间相隔的天数，常用作于查询指定时间段内的所有数据
注：TO_DAYS()不适用于公历日历（1582）出现之前的值，因为它不考虑日历更改时丢失的日期。在1582年之前的日期（可能在其他地区的其他年份），此功能的结果不可靠。
MySQL将日期中的两位数年份值转换为四位数形式 。例如， '2017-10-09'、 '17-10-09'和‘171009’被看作是相同的日期
示例：
mysql> select to_days(now()); +----------------+ | to_days(now()) | +----------------+ | 738686 | +----------------+ 1 row in set (0.00 sec) mysql>
查询今天的数据(从今天到当前执行时间)：
select from_unixtime(notify_time, '%Y-%m-%d %H:%i:%s') from alarm_lists where to_days(from_unixtime(notify_time, '%Y-%m-%d %H:%i:%s')) = to_days(now())
昨天：
SELECT from_unixtime(notify_time, '%Y-%m-%d %H:%i:%s') as 告警通知时间 FROM alarm_lists WHERE TO_DAYS(NOW()) - TO_DAYS(from_unixtime(notify_time, '%Y-%m-%d %H:%i:%s')) <= 1
3.8 DATE_FORMAT()
作用：以不同的格式显示日期/时间数据
语法： DATE_FORMAT(date,format)
支持的格式：参考本文1.4
示例：
mysql> select date_format(now(),'%b %d %Y %h:%i %p'); +----------------------------------------+ | date_format(now(),'%b %d %Y %h:%i %p') | +----------------------------------------+ | Jun 15 2022 10:56 AM | +----------------------------------------+ 1 row in set (0.00 sec) mysql> select date_format(now(),'%m-%d-%Y'); +-------------------------------+ | date_format(now(),'%m-%d-%Y') | +-------------------------------+ | 06-15-2022 | +-------------------------------+ 1 row in set (0.00 sec) mysql> select date_format(now(),'%d %b %y'); +-------------------------------+ | date_format(now(),'%d %b %y') | +-------------------------------+ | 15 Jun 22 | +-------------------------------+ 1 row in set (0.00 sec) mysql> select date_format(now(),'%d %b %Y %T:%f'); +-------------------------------------+ | date_format(now(),'%d %b %Y %T:%f') | +-------------------------------------+ | 15 Jun 2022 10:56:46:000000 | +-------------------------------------+ 1 row in set (0.00 sec) mysql>
MySQL [srestandard]> select date_format(now(),'%Y-%m-%d %H:%i:%S'); +----------------------------------------+ | date_format(now(),'%Y-%m-%d %H:%i:%S') | +----------------------------------------+ | 2023-07-11 18:37:36 | +----------------------------------------+ 1 row in set (0.00 sec)
四、查询指定时间段的数据
mysql 时间日期函数很多，多结合函数来判断时间范围，查询自己想要的数据，多学多练，熟能生巧
4.1 当前时间
以下都是截取到当前时间作为时间范围，实际运用中，可定时执行，例每天晚上 00:05:00 执行，查询从几天前的 00:05:00 到今天的数据
今天：
select * from 表名 where to_days(时间字段名) = to_days(now());
昨天：
SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1
本周：
select * FROM 表名 WHERE YEARWEEK(date_format(start_time,'%Y-%m-%d')) = YEARWEEK(now());
上周：
SELECT * FROM 表名 WHERE YEARWEEK(date_format(start_time,'%Y-%m-%d')) = YEARWEEK(now())-1;
近30天：
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)
本月：
SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
上月：
SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1
本季度：
select * from 表名 where QUARTER(create_date)=QUARTER(now())
上季度：
select * from 表名 where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
本年：
select * from 表名 where YEAR(create_date)=YEAR(NOW());
去年：
select * from 表名 where year(create_date)=year(date_sub(now(),interval 1 year));
4.2 指定时间
昨天：
select * from t1 where notify_time >= unix_timestamp(date_sub(curdate(),interval 1 day)) and notify_time <= unix_timestamp(curdate())
前天：
notify_time >= unix_timestamp(date_sub(curdate(),interval 2 day)) and notify_time <= unix_timestamp(date_sub(curdate(),interval 1 day))
五、补充
5.1 获取当天零点，9点的时间，时间戳
5.1.1 获取当天零点，9点的时间
MySQL [srestandard]> select date_format(curdate(),'%Y-%m-%d %H:%i:%s'); +--------------------------------------------+ | date_format(curdate(),'%Y-%m-%d %H:%i:%s') | +--------------------------------------------+ | 2023-04-13 00:00:00 | +--------------------------------------------+ 1 row in set (0.00 sec) MySQL [srestandard]> select date_add(curdate(), interval 9 hour); +--------------------------------------+ | date_add(curdate(), interval 9 hour) | +--------------------------------------+ | 2023-04-13 09:00:00 | +--------------------------------------+ 1 row in set (0.00 sec) MySQL [srestandard]>
5.1.2 获取昨天零点，9点的时间
#昨日零点 select date_format(date_sub(curdate(),interval 1 day),'%Y-%m-%d %H:%i:%s'); #昨日9点 select date_add(date_sub(curdate(),interval 1 day), interval 9 hour);
5.1.3 获取当天零点，9点的时间戳
#零点 select unix_timestamp(curdate()); #9点 select unix_timestamp(date_add(curdate(), interval 9 hour));
昨天零点的时间戳
select unix_timestamp(date_sub(curdate(),interval 1 day));
昨天9点的时间戳
select unix_timestamp(date_add(date_sub(curdate(),interval 1 day), interval 9 hour));
5.2 数据插入时自动更新时间
DEFAULT CURRENT_TIMESTAMP：这个子句用于指定一个字段的默认值为当前的日期和时间。当插入一行但没有为该字段提供值时，MySQL 将自动使用当前的日期和时间填充该字段ON UPDATE CURRENT_TIMESTAMP：这个子句用于指定一个字段在更新时自动更新为当前的日期和时间。当更新一行并更改该字段时，MySQL 将自动使用当前的日期和时间更新该字段
例：
建表：
CREATE TABLE `table_name` ( `id` int(11) NOT NULL AUTO_INCREMENT, `column1` varchar(255) DEFAULT NULL, `column2` varchar(255) DEFAULT NULL, `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4
插入两条数据：
MySQL [sredev]> insert into table_name (column1,column2) values ('t1','t1'); Query OK, 1 row affected (0.23 sec) MySQL [sredev]> select * from table_name; +----+---------+---------+---------------------+------------+ | id | column1 | column2 | created_at | updated_at | +----+---------+---------+---------------------+------------+ | 1 | t1 | t1 | 2023-12-11 15:06:02 | NULL | +----+---------+---------+---------------------+------------+ 1 row in set (0.00 sec) MySQL [sredev]> insert into table_name (column1,column2) values ('t2','t2'); Query OK, 1 row affected (0.09 sec) MySQL [sredev]> select * from table_name; +----+---------+---------+---------------------+------------+ | id | column1 | column2 | created_at | updated_at | +----+---------+---------+---------------------+------------+ | 1 | t1 | t1 | 2023-12-11 15:06:02 | NULL | | 2 | t2 | t2 | 2023-12-11 15:06:09 | NULL | +----+---------+---------+---------------------+------------+ 2 rows in set (0.00 sec)
更新两条数据：
MySQL [sredev]> update table_name set column1 = 't3'; Query OK, 2 rows affected (0.19 sec) Rows matched: 2 Changed: 2 Warnings: 0 MySQL [sredev]> select * from table_name; +----+---------+---------+---------------------+---------------------+ | id | column1 | column2 | created_at | updated_at | +----+---------+---------+---------------------+---------------------+ | 1 | t3 | t1 | 2023-12-11 15:06:02 | 2023-12-11 15:08:08 | | 2 | t3 | t2 | 2023-12-11 15:06:09 | 2023-12-11 15:08:08 | +----+---------+---------+---------------------+---------------------+ 2 rows in set (0.00 sec)
5.3 表字段为时间戳格式时，查询指定数据
在 MySQL 中，当一个表字段是时间戳形式（即 UNIX 时间戳，表示从 1970 年 1 月 1 日 00:00:00 UTC 开始的秒数）时，查询指定时间段的数据可以采用两种方法：
- 将指定时间转换成时间戳，与表中的时间戳对比。
- 将表中的时间戳转换成时间格式，再进行对比。
性能比较：
从性能的角度来看，将指定时间转换成时间戳与表中的时间戳对比通常更高效。这是因为时间戳是一个整数值，而整数比较通常比日期时间格式的解析和比较更快。
当将指定时间转换成时间戳进行对比时，只需要将指定时间转换成时间戳的整数值，然后直接与表中的时间戳进行比较。这种方法避免了将表中的时间戳转换成时间格式的开销，以及日期时间解析和比较的开销。
相比之下，将表中的时间戳转换成时间格式，再进行对比涉及到解析时间戳为日期时间格式的过程，这可能会引入额外的计算和内存开销。
因此，为了获得更好的性能，通常建议将指定时间转换成时间戳进行对比，而不是将表中的时间戳转换成时间格式再进行对比。
