年，月，日相关的sql总结

1.此处涉及到多个日期函数

根据创建时间按照年月日分组

  SELECT * FROM user GROUP BY DATE_FORMAT(create_time,'%Y%u%d');

根据年(2901)查询 统计每个月数据

SELECT  COUNT(1) COUNT ,DATE_FORMAT(create_time,'%Y-%m') TIME FROM user  WHERE  DATE_FORMAT(create_time,'%Y') = '2019' GROUP BY DATE_FORMAT(create_time,'%Y-%m');

根据月每天（统计2019年3月份数据）

SELECT  COUNT(1)  COUNT ,DATE_FORMAT(create_time,'%Y-%m-%d') TIME FROM user  WHERE DATE_FORMAT(create_time,'%Y-%m') = '2019-03'  GROUP BY DATE_FORMAT(create_time,'%Y-%m-%d');

根据天查询统计每小时数据

SELECT  COUNT(1)  COUNT ,create_time TIME FROM user  WHERE DATE_FORMAT(create_time,'%Y-%m-%d') = '2019-03-11' GROUP BY create_time;

本周

SELECT * FROM user WHERE WEEKOFYEAR(create_date)=WEEKOFYEAR(NOW()); 

本月

SELECT * FROM user WHERE MONTH(create_date)=MONTH(NOW()) AND YEAR(create_date)=YEAR(NOW()); 

本年

SELECT * FROM user WHERE YEAR(create_date)=YEAR(NOW()); 

当天

 SELECT * FROM user WHERE TO_DAYS(create_time) = TO_DAYS(NOW()); 

统计当天（TO_DAYS）

SELECT COUNT(1)  COUNT ,create_time TIME FROM user  WHERE  TO_DAYS(create_time) =  TO_DAYS('2019-03-11') GROUP BY create_time;

统计本周（WEEKOFYEAR）

SELECT COUNT(1)  COUNT ,DATE_FORMAT(create_time,'%Y-%m-%d') TIME FROM user WHERE  WEEKOFYEAR(create_time) = WEEKOFYEAR( '2019-03-11' ) GROUP BY  TO_DAYS(create_time); 

统计本月（MONTH）

SELECT COUNT(1)  COUNT ,DATE_FORMAT(create_time,'%Y-%m-%d') TIME FROM user WHERE  MONTH(create_time)=MONTH('2019-03-25') AND YEAR(create_time)=YEAR('2019-03-25') GROUP BY  TO_DAYS(create_time);

统计本年（YEAR）

SELECT COUNT(1) AS COUNT ,DATE_FORMAT(create_time,'%m') AS TIME  FROM user  WHERE pay_type = 1 AND YEAR(create_time)=YEAR('2019-03-25')  GROUP BY  MONTH(create_time); 

统计这个月

SELECT * FROM tb_month_card_settlement WHERE MONTH(last_charging_time)=MONTH(NOW()) AND YEAR(last_charging_time)=YEAR(NOW());

上個月（DATE_FORMAT）

SELECT * FROM tb_month_card_settlement WHERE 	DATE_FORMAT(last_charging_time, '%Y-%m') = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m');

查询上个月（DATE_FORMAT）

SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m');

当前日期

SELECT CURDATE()

当前时间

SELECT NOW()