SELECT now(); #现在是多少时间----2018-07-07 21:48:29
SELECT to_days(now()); #从公元0年到今天是多少天----737247
SELECT yearweek(now()); #本年度的第几个星期----201826
SELECT quarter(now()); #当前日期是本年度的第几个季度----3
我们以此表bookable为例子
一.查询bookable中为当天的数据
1.传统方法：SELECT * FROM bookable WHERE DATE_FORMAT( bdate , '%Y-%m-%d' )
= DATE_FORMAT(NOW(), '%Y-%m-%d' );
2.第一种简写：SELECT * FROM bookable WHERE bdate >= DATE_FORMAT(NOW() , '%Y-%m-%d' );
3.函数方法：SELECT * FROM bookable WHERE DATEDIFF( bdate , NOW()) = 0;
注意：返回当天时间的mysql函数有curdate() , current_date() , current_date , now()几种。
其中now()获取的时间格式为：0000-00-00 00:00:00；而其他几种是0000-00-00，返回的时间无时分秒。
二.其他的语句
-- 查询今天的信息:
select * from bookable where to_days(bdate) = to_days(now());
-- 查询昨天的信息:
select * from bookable where to_days(now()) - to_days(bdate) = 1;
-- 查询近 7 天的信息:
select * from bookable where date_sub(curdate(), INTERVAL 7 DAY) <= date(bdate);
-- 查询本周的信息:
select * from bookable where YEARWEEK(date_format(bdate,'%Y-%m-%d')) = YEARWEEK(now());
-- 查询上周的信息:
select * from bookable where YEARWEEK(date_format(bdate,'%Y-%m-%d')) = YEARWEEK(now()) -1;
-- 查询近 30 天的信息:
select * from bookable where date_sub(curdate(), INTERVAL 30 DAY) <= date(bdate);
-- 查询本月的信息:
select * from bookable where date_format(bdate,'%Y%m') = date_format(curdate(),'%Y%m');
-- 查询上一月的信息:
SELECT * FROM bookable WHERE PERIOD_DIFF(date_format(now( ),'%Y%m' ), date_format(bdate, '%Y%m' ))=1;
-- 查询距离当前现在 6 个月的信息:
select * from bookable where bdate between date_sub(now(),interval 6 month) and now();
-- 查询本季度的信息:
select * from bookable where QUARTER(bdate)=QUARTER(now());
-- 查询上季度的信息:
select * from bookable where QUARTER(bdate)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
-- 查询本年的信息:
select * from bookable where YEAR(bdate) = YEAR(now());
-- 查询去年的信息:
select * from bookable where YEAR(bdate) = YEAR(date_sub(now(),interval 1 year));