MySQL日期时间类型 常用的查询、转换、计算

测试表&测试数据

create table date_demo
(
    data_name   varchar(255) null,
    create_date date         null
);

INSERT INTO date_demo (data_name, create_date) VALUES ('苹果', '2022-06-01');
INSERT INTO date_demo (data_name, create_date) VALUES ('香蕉', '2022-11-11');
INSERT INTO date_demo (data_name, create_date) VALUES ('橘子', '2022-11-20');
INSERT INTO date_demo (data_name, create_date) VALUES ('榴莲', '2022-12-09');
INSERT INTO date_demo (data_name, create_date) VALUES ('葡萄', '2022-12-01');
INSERT INTO date_demo (data_name, create_date) VALUES ('西瓜', '2022-11-30');
INSERT INTO date_demo (data_name, create_date) VALUES ('草莓', '2022-09-16');

Unix 时间戳函数

select unix_timestamp(); -- 1667232000
select unix_timestamp('2022-11-01'); -- 1667232000
select unix_timestamp('2022-11-01 12:00:00'); -- 1667275200
select from_unixtime(1667232000); -- 2022-11-01 00:00:00
select from_unixtime(1667232000, '%Y-%m-%d'); -- 2022-11-01

日期范围查询

select *
from date_demo
where date_format(create_date, '%Y%m%d') between '20221101' and '20221201'

DATE_FORMAT这种写法，会使索引失效，数据量大时查询速度比较慢

与今天的日期作比较

select * from date_demo where  create_date>=curdate()
select * from date_demo where  date(create_date)>=now()

常用日期查询

-- 今天
select * from date_demo where to_days(create_date) = to_days(now());
-- 昨天
select * from date_demo where to_days(now()) - to_days(create_date) = 1;
-- 近7天
select * from date_demo where date_sub(curdate(),interval 7 day) <= create_date;
-- 近30天
select * from date_demo where date_sub(curdate(), interval 30 day) <= date(create_date);
-- 本月
select * from date_demo where date_format(create_date,'%y%m') = date_format(curdate(),'%y%m');
select * from date_demo where date_format(create_date,'%y-%m')=date_format(now(),'%y-%m');
-- 上个月
select * from date_demo where period_diff( date_format( now( ) ,'%y%m') , date_format( create_date,'%y%m') ) =1;
select * from date_demo where date_format(create_date,'%y-%m')=date_format(date_sub(curdate(), interval 1 month),'%y-%m');
-- 本季度
select * from date_demo where quarter(create_date)=quarter(now());
-- 上季度
select * from date_demo where quarter(create_date)=quarter(date_sub(now(),interval 1 quarter));
-- 本年
select * from date_demo where year(create_date)=year(now());
-- 上一年
select * from date_demo where year(create_date)=year(date_sub(now(),interval 1 year));
-- 距离当前6个月
select * from date_demo where create_date between date_sub(now(),interval 6 month) and now();
-- 本周
select * from date_demo where yearweek(date_format(create_date,'%y-%m-%d')) = yearweek(now());
-- 上周
select * from date_demo where yearweek(date_format(create_date,'%y-%m-%d')) = yearweek(now())-1;
-- 指定时间段
select * from date_demo where create_date between  '2022-1-1 00:00:00'  and '2023-1-1 00:00:00';
select * from date_demo where create_date >='2022-1-1 00:00:00'  and create_date < '2023-1-1 00:00:00';

常用获取函数

-- 获取当前日期时间
select now(); --2022-11-01 16:30:12
-- 获取当前日期
select curdate(); --2022-11-01
-- 获得当前时间戳
select current_timestamp, current_timestamp(); --2022-11-01 16:30:12

常用转换函数

-- 日期时间转字符串
select date_format('2022-11-01 16:30:12', '%Y%m%d%H%i%s'); --20221101163012
-- 字符串转日期时间
select str_to_date('2022-11-01' , '%Y-%m-%d');  --2022-11-01
-- 转天数；给定一个日期date, 返回一个从年份0开始计算的天数
select to_days(now());
-- 创建日期、时间
select makedate(2022,31); --2022-01-31
select maketime(8,26,13); --08:26:13
-- 秒的互转
select time_to_sec('00:01:05'); -- 65
select sec_to_time(321); -- 00:05:21

-- 时间戳转换
select timestamp('2022-11-01'); -- 2022-11-01 00:00:00
select timestamp('2022-11-01 08:00:00', '01:01:01'); -- 2022-11-01 09:01:01
select timestamp('2022-11-01 08:00:00', '10 01:01:01'); -- 2022-11-11 09:01:01

常用计算函数

set @dt = now(); -- 定义变量（需要与语句一起执行）

-- 增加时间（date_add）
select date_add(@dt, interval 1 day); -- 加一天
select date_add(@dt, interval -1 day); -- 加-1天也就是减1天
select date_add(@dt, interval 1 hour); -- 加一小时
select date_add(@dt, interval '01:10:20' hour); -- 加1小时10分20秒
select date_add(@dt, interval 1 minute); -- ...
select date_add(@dt, interval 1 second);
select date_add(@dt, interval 1 microsecond);
select date_add(@dt, interval 1 week);
select date_add(@dt, interval 1 month);
select date_add(@dt, interval 1 quarter);
select date_add(@dt, interval 1 year);
-- 减去时间（date_sub）
select date_sub(@dt, interval 1 day); -- 加一天
···

-- 两个日期相减 date1 - date2，返回相差天数。
select datediff('2022-01-11', '2022-01-11'); -- 0
select datediff('2022-01-11', '2022-01-30'); -- -19
select datediff('2022-01-11', '2022-01-01'); -- -10
-- 两个日期相减 time1 - time2，返回 time 差值。
select timediff('2022-01-11 01:00:00', '2022-01-10 18:00:00'); -- 07:00:00
select timediff('16:00:06', '00:00:00'); -- 16:00:06

-- 时间戳加减
select timestampadd(day, 1, '2022-11-01 08:00:00'); -- 2022-11-02 08:00:00
select date_add('2022-11-01 08:00:00', interval 1 day); -- 2022-11-02 08:00:00
select timestampdiff(year,'2022-11-01','2001-01-01'); -- -20
select timestampdiff(day ,'2022-11-01','2001-01-01'); -- -7547
select timestampdiff(hour,'2022-11-01 12:00:00','2022-11-01 00:00:00'); -- -12