mysql----与时间相关的sql(一)

与时间相关的sql语句总结

今天

1、select * from 'table_name' where to_days(release_time) = to_days(now());`
2、select * from 'table_name' where date(release_time) = curdate(); 
3、select * from 'table_name' where release_time >= date_format(now(),'%Y-%m-%d');
4、select * from 'table_name' where DATEDIFF(release_time,now()) =0

备注：返回当天时间的mysql函数有CURDATE()、CURRENT_DATE()、CURRENT_DATE、NOW()几种；其中NOW()获取的日期格式为0000-00-00 00:00:00的时间；CURDATE()、CURRENT_DATE()、CURRENT_DATE是获取日期格式为0000-00-00的时间，所以返回的时间无时分秒；

昨天

1、select * from 'table_name' where to_days(release_time) = to_days(now()) -1;
2、select * from 'table_name' where to_days(now()) - to_days(release_time) = 1;

7天 (根据需要可换成月、年，以及最近多少天数据)

select * from 'table_name' where date_sub(curdate(), interval 7 DAY) <= date(release_time);

查询当前这周的数据

SELECT * FROM 'table_name' where YEARWEEK(date_format(release_time,'%Y-%m-%d')) = YEARWEEK(now()); 

查询上周的数据

SELECT * FROM 'table_name' where YEARWEEK(date_format(release_time,'%Y-%m-%d')) = YEARWEEK(now()) -1; 

查询当前月份的数据

1、select *  from 'table_name' where date_format(release_time,'%Y-%m')=date_format(now(),'%Y-%m') 
2、select  * from 'table_name' where date_format(release_time,'%Y%m') = date_format(curdate(),'%Y%m') ;

查询上个月的数据

1、select * from 'table_name' where date_format(release_time,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')
2、select * from 'table_name' where PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( release_time, '%Y%m' ) ) =1 

查询距离当前现在6个月的数据

select * from 'table_name'where release_time between date_sub(now(),interval 6 month) and now();

查询本季度数据

select * from 'table_name' where QUARTER(release_time)=QUARTER(now());

查询上季度数据

select * from 'table_name' where QUARTER(release_time)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

查询本年数据

select * from 'table_name' where year(release_time)=year(NOW());

查询上年数据

select * from 'table_name' where year(release_time)=year(date_sub(now(),interval 1 year));