本周统计:
SELECT * FROM tablename WHERE YEARWEEK(FROM_UNIXTIME(create_time/1000,'%Y-%m-%d')- INTERVAL 1 DAY) = YEARWEEK(now());
本月统计:
SELECT * FROM tablename WHERE FROM_UNIXTIME(create_time/1000,'%Y-%m')=date_format(now(),'%Y-%m');
当日统计:
SELECT * FROM table_name WHERE to_days(createtime) = to_days(now());
当年统计:
SELECT * FROM table_name WHERE YEAR(createtime) = YEAR(now());
当前日期:
CURDATE() //date 类型
当前日期前两天:
DATE_SUB(CURDATE(),INTERVAL 2 DAY) //date 类型
统计图表的聚合sql:(统计今年每个月的数据)
SELECT sum(t.amount) AS amountSum, SUBSTRING_INDEX(
GROUP_CONCAT(
date_format(t.create_date, '%m')
ORDER BY
t.create_date ASC
),
',',
1
) AS createDateStr
FROM tablename1 t where YEAR (t.create_date) = YEAR (now()) GROUP BY date_format(t.create_date, '%Y-%m')