mysql查询时间相关方法

查询当月第一天

SELECT DATE_FORMAT(NOW(), '%Y-%m-01') AS first_day_of_month;
SELECT DATE_FORMAT(CURDATE(), '%Y-%m-01') AS first_day_of_month;
1
2

查询当月最后一天

SELECT LAST_DAY(CURRENT_DATE) AS last_day_of_month;;
SELECT LAST_DAY(NOW()) AS last_day_of_month
1
2

查询当天数据

SELECT COUNT(*) AS "当天入群客户数" FROM customer_group_member_5 WHERE to_days(join_time)=to_days(now());
1

查询本周数据

SELECT count(*) AS "本周进群人数" FROM customer_group_member_5 WHERE YEARWEEK(join_time) = YEARWEEK(CURDATE());
1

注意：
YEARWEEK函数是获取本年本周

WEEK函数是获取所有年份的本周

查询近6个月数据

SELECT count(*) AS "近6个月进群客户数" from customer_group_member_5 WHERE join_time between date_sub(now(),interval 6 month) and now() ORDER BY join_time DESC;
1

查询近3个月数据同理

查询本月数据

SELECT count(*) AS "本月进群人数" FROM customer_group_member_5 WHERE MONTH(join_time) = MONTH(CURDATE());
1

查询本年数据

SELECT count(*) AS "本月进群人数" FROM customer_group_member_5 WHERE YEAR(join_time) = YEAR(CURDATE());
1