mysql 时间查询

 

 

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));