Mysql时间查询与分析

目录

关于mysql的时间查询与分析

关于mysql的时间查询与分析

实例：以 user_test 为表名 create_time 为时间字段
-- 查询今天
select * from user_test where TO_DAYS(create_time) = to_days(now());

SELECT * FROM user_test WHERE TO_DAYS(NOW()) - TO_DAYS(create_time) = 0;

SELECT* FROM user_test WHERE DATEDIFF(create_time,NOW())=0

-- 本周 YEARWEEK(now(),1) 后面的1表示从周几开始 0表示这周从周日开始算 以此类推
select * from user_test where YEARWEEK(date_format(create_time,'%Y-%m-%d'),1) = YEARWEEK(now(),1);

-- 7天内
SELECT * FROM user_test where date_sub(curdate(), interval 7 day) <= date(CREATE_TIME);

-- n天内
SELECT * FROM user_test where date_sub(curdate(), interval n day) <= date(CREATE_TIME);

-- 本月
SELECT * FROM user_test WHERE DATE_FORMAT(create_time, '%Y%m') = DATE_FORMAT( CURDATE( ) , '%Y%m')


-- 查询5分钟前的数据
select * from user_test where create_time between date_add(now(), interval - 300 SECOND) and NOW()

-- 查询昨天的所有数据
SELECT* FROM user_test WHERE DATEDIFF(create_time,NOW())=-1

-- 查询未来第n天的所有数据//当n为负数时，表示过去第n天的数据
SELECT * FROM user_test  WHERE DATEDIFF(create_time,NOW())=n

-- 查询过去n天内所有数据（包含当天）
SELECT * FROM user_test WHERE DATEDIFF(create_time,NOW())<=0 AND DATEDIFF(create_time,NOW())>-n

-- 查询过去n天内所有数据（不包含当天）
SELECT * FROM user_test WHERE DATEDIFF(create_time,NOW())<0 AND DATEDIFF(create_time,NOW())>-n

-- 上一月
SELECT * FROM user_test WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) ,date_format( create_time, '%Y%m' ) ) =1

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

-- 查询上季度数据
select * from user_test where QUARTER(create_time)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

-- 查询距离当前现在6个月的数据
select * from user_test where create_time between date_sub(now(),interval 6 month) and now();

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

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

-- 全表更新 增加3天
UPDATE user_test SET create_time =DATE_ADD(create_time,INTERVAL 3 DAY);

-- 全表更新 減少3天
UPDATE user_test SET create_time=DATE_SUB(create_time,INTERVAL 3 DAY);