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