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 开始的秒数)时,查询指定时间段的数据可以采用两种方法:
- 将指定时间转换成时间戳,与表中的时间戳对比。
- 将表中的时间戳转换成时间格式,再进行对比。
性能比较:
从性能的角度来看,将指定时间转换成时间戳与表中的时间戳对比通常更高效。这是因为时间戳是一个整数值,而整数比较通常比日期时间格式的解析和比较更快。
当将指定时间转换成时间戳进行对比时,只需要将指定时间转换成时间戳的整数值,然后直接与表中的时间戳进行比较。这种方法避免了将表中的时间戳转换成时间格式的开销,以及日期时间解析和比较的开销。
相比之下,将表中的时间戳转换成时间格式,再进行对比涉及到解析时间戳为日期时间格式的过程,这可能会引入额外的计算和内存开销。
因此,为了获得更好的性能,通常建议将指定时间转换成时间戳进行对比,而不是将表中的时间戳转换成时间格式再进行对比。