mysql的各种日期查询

mysql的各种日期查询

表data_record
时间字段creat_time, 格式如2019-10-09 21:59:35

-- 1 、 查看当天日期 2021-03-24
select current_date();

-- 2、 查看当天时间 14:38:26
select current_time();

-- 3、查看当天时间日期 2021-03-24 14:38:26
select current_timestamp();

-- 当前时间 2021-03-24 14:38:26
SELECT NOW();

-- 时间段，N天内记录
select * from data_record WHERE TO_DAYS(NOW()) - TO_DAYS(create_time) <= 6;

-- 时间段，查询一周
select * from data_record where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(create_time);

-- 时间段，查询一个月
select * from data_record where DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <= date(create_time);

-- unix时间戳（单位秒）转为日期，结果如：2021-03-01 12:03:05， 用得较多
select FROM_UNIXTIME(1614571385,'%Y-%m-%d %H:%i:%s');

-- unix时间戳转为年月格式%Y-%m
select FROM_UNIXTIME(1614571385,'%Y-%m');

-- 日期转为unix时间戳，结果如：1614571385, 单位秒
select UNIX_TIMESTAMP('2021-03-01 12:03:05');

-- 今天年月日，2021-03-24
SELECT CURDATE();

-- TO_DAYS的用法，738238
SELECT TO_DAYS(NOW());

-- QUARTER的用法 1
SELECT QUARTER(now());

-- YEARWEEK的用法 202112  年+第几个周
SELECT YEARWEEK(now());
SELECT YEARWEEK(now())-1;
SELECT YEARWEEK("2021-01-20 14:38:26");

-- WEEK用法，13
SELECT WEEK(now());

-- 月 3
SELECT MONTH(NOW())

-- 按月统计 2021-03
SELECT DATE_FORMAT(NOW(),'%Y-%m')

-- mysql按月统计
SELECT MONTH(NOW())

-- 距离现在的6个月时间 2020-09-24 15:47:22
SELECT date_sub(now(),interval 6 month);

-- 本月第一天
select date_add(curdate(), interval - day(curdate()) + 1 day);

-- 本月最后一天
select last_day(curdate());

-- 上月第一天
select date_add(curdate()-day(curdate())+1,interval -1 month);

-- 上月最后一天
select last_day(date_sub(now(),interval 1 month));

-- 下月第一天
select date_add(curdate()-day(curdate())+1,interval 1 month);

-- 下月最后一天
select last_day(date_sub(now(),interval -1 month));

-- 数据取两个月份中间的记录，传入unix时间戳
SELECT * FROM data_info WHERE DATE_FORMAT(create_time,'%Y-%m') between FROM_UNIXTIME(1612152185,'%Y-%m') and FROM_UNIXTIME(1616558585,'%Y-%m');

-- 上一个季度
SELECT QUARTER(DATE_SUB(now(),interval 1 QUARTER));

-- 4、查询当天记录
select * from data_record where to_days(create_time) = to_days(now());

-- 5、查询昨天记录
SELECT * FROM data_record WHERE TO_DAYS(NOW())-TO_DAYS(create_time) <= 1;

-- 6、查询7天的记录
SELECT * FROM data_record where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(create_time);

-- 7、查询近30天的记录
SELECT * FROM data_record where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(create_time);

-- 8、查询本月的记录
SELECT * FROM data_record WHERE DATE_FORMAT(create_time, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m');

-- 9、查询上一月的记录
SELECT * FROM data_record WHERE PERIOD_DIFF(date_format(now(), '%Y%m') , date_format(create_time, '%Y%m')) =1

-- 10、查询本季度数据
select * from data_record where QUARTER(create_time)=QUARTER(now());

-- 11、查询上季度数据？？？ 有问题 不是上一个季度的
select * from data_record where QUARTER(create_time)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

-- 12、查询本年数据
select * from data_record where YEAR(create_time)=YEAR(NOW());

-- 13、查询上年数据
select * from data_record where year(create_time)=year(date_sub(now(),interval 1 year));

-- 14、查询当前这周的数据
SELECT * FROM data_record WHERE YEARWEEK(date_format(create_time,'%Y-%m-%d')) = YEARWEEK(now());

-- 15、查询上周的数据
SELECT * FROM data_record WHERE YEARWEEK(date_format(create_time,'%Y-%m-%d')) = YEARWEEK(now())-1;

-- 16、查询当前月份的数据
select * from data_record where date_format(create_time,'%Y-%m')=date_format(now(),'%Y-%m')

-- 17、查询距离当前现在6个月的数据，时间间隔 2020-09-24 15:47:22， 2021-03-24 15:48:37
select * from data_record where create_time between date_sub(now(),interval 6 month) and now();