select * from member where DATEDIFF(now(),mem_reg_time) = 0; // 查询今天注册的会员
select * from member where DATEDIFF(now(),mem_reg_time) = 1; // 查询昨天注册的会员
select * from member where DATEDIFF(now(),mem_reg_time) < N; // 查询最近N天注册的会员
今天
SELECT * FROM signn WHERE TO_DAYS(signDate) = TO_DAYS(NOW());
昨天
SELECT * FROM signn WHERE TO_DAYS(NOW()) - TO_DAYS(signDate) = 1;
昨天和今天
SELECT * FROM signn WHERE TO_DAYS(NOW()) - TO_DAYS(signDate) <= 1;
7天
SELECT * FROM 表名 WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(时间字段名)
SELECT * FROM signn WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(signDate);
近30天
SELECT * FROM 表名 WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(时间字段名)
SELECT * FROM signn WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(signDate);
本月
SELECT * FROM 表名 WHERE DATE_FORMAT(时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
SELECT * FROM signn WHERE DATE_FORMAT(signDate, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')
上一月
SELECT * FROM 表名 WHERE PERIOD_DIFF( DATE_FORMAT( NOW( ) , '%Y%m' ) , DATE_FORMAT( 时间字段名, '%Y%m' ) ) =1
SELECT * FROM signn WHERE PERIOD_DIFF(DATE_FORMAT(NOW(), '%Y%m'), DATE_FORMAT(signDate, '%Y%m')) = 1
查询一周:
SELECT * FROM table WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(column_time);
SELECT * FROM signn WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(signDate);
查询一个月:
SELECT * FROM TABLE WHERE DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <= DATE(column_time);
SELECT * FROM signn WHERE DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <= DATE(signDate);
查询本季度数据
SELECT * FROM `ht_invoice_information` WHERE QUARTER(create_date)=QUARTER(NOW());
SELECT * FROM signn WHERE QUARTER(signDate) = QUARTER(NOW());
查询上季度数据
SELECT * FROM `ht_invoice_information` WHERE QUARTER(create_date)=QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER));
SELECT * FROM signn WHERE QUARTER(signDate) = QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER));
查询本年数据
SELECT * FROM `ht_invoice_information` WHERE YEAR(create_date)=YEAR(NOW());
SELECT * FROM signn WHERE YEAR(signDate) = YEAR(NOW());
查询上年数据
SELECT * FROM `ht_invoice_information` WHERE YEAR(create_date)=YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR));
SELECT * FROM signn WHERE YEAR(signDate) = YEAR(DATE_SUB(NOW(), INTERVAL 1 YEAR));