一、日期,时间戳格式转换
在数据库中,时间的存储格式一般为时间戳,但这样对于使用人员不太方便,故在查询时可以先转换格式
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)