DATE_SUB()和DATE_ADD()函数，实现日期增减DATE_SUB(NOW(),INTERVAL 30 MINUTE) 当前时间减30分钟DATE_SUB(NOW(),INTERVAL 1 day) 当前时间减1天DATE_SUB(NOW(),INTERVAL 1 hour)当前时间减1小时 DATE_SUB(NOW(),INTERVAL 1 second)当前时间减1秒DATE_SUB(NOW(),INTERVAL 1 week) 当前时间减1星期DATE_SUB(NOW(),INTERVAL 1 month) 当前时间减1个月 DATE_SUB(NOW(),INTERVAL 1 quarter)当前时间减1季度DATE_SUB(NOW(),INTERVAL 1 year) 当前时间减1年
-- 1、查询当天的数据AND TO_DAYS(t1.create_time)=TO_DAYS(NOW());AND DATEDIFF( NOW(), t1.create_time ) = 0;-- 2、查询昨天的数据AND TO_DAYS(NOW())-TO_DAYS(t1.create_time)=1;AND DATEDIFF( NOW(), t1.create_time ) = 1;-- 3、查询当周的数据AND YEARWEEK(DATE_FORMAT(t1.create_time,'%Y-%m-%d'))=YEARWEEK(NOW());-- 4、查询上周的数据AND YEARWEEK(DATE_FORMAT(t1.create_time,'%Y-%m-%d'))=YEARWEEK(NOW())-1;-- 5、查询最近7天的数据AND DATE_SUB(CURDATE(),INTERVAL 7 DAY)<=DATE(t1.create_time);AND DATEDIFF( NOW(), t1.create_time ) <= 7;-- 6、查询当月的数据AND DATE_FORMAT(t1.create_time,'%Y%m')=DATE_FORMAT(CURDATE(),'%Y%m');-- 7、查询上月的数据AND PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'),DATE_FORMAT(t1.create_time,'%Y%m'))=1;-- 8、查询本季度的数据AND QUARTER(t1.create_time)=QUARTER(now());-- 9、查询上季度的数据AND QUARTER(t1.create_time)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));-- 10、查询当年的数据AND YEAR(t1.create_time) =YEAR(NOW());-- 11、查询上年的数据AND year(t1.create_time)=year(date_sub(now(),interval 1 year)); AND YEAR(t1.create_time) = (YEAR(NOW()) - 1);-- 12、上年同天AND to_days(t1.create_time) = to_days(date_sub(now(),interval 1 year));-- 12、上年同周AND YEARWEEK(date_format(t1.create_time,'%Y-%m-%d'),1) = YEARWEEK(date_sub(now(),interval 1 year),1);-- 12、上年同月AND date_format(t1.create_time,'%Y%m') = date_format((now() - INTERVAL 1 YEAR ),'%Y%m');
对日期范围搜索的大致有三种方式：
1、between and
t1.create_time BETWEEN '-08-01' AND '-09-01'
2、DATEDIFF(date1,date2)
SELECT DATEDIFF('-08-01','-09-01');-31
SELECT DATEDIFF('-10-01','-09-01');-30
3、TIMESTAMPDIFF(interval，datetime1，datetime2)
FRAC_SECOND毫秒
SECOND秒
MINUTE分钟
HOUR小时
DAY天
WEEK星期
MONTH月
QUARTER季度
YEAR年
SELECT TIMESTAMPDIFF(day,'-08-01','-09-01');31
SELECT TIMESTAMPDIFF(day,'-10-01','-09-01');-30
计算结果与DATEDIFF方式的正负相反
