mysql将数据按天,周,半月,月分组

最近有个需求,要求查出今天,一周,15天,一个月的数据

created是时间戳

按天分组:

SELECT SUM(income) AS income,
FROM_UNIXTIME(created, '%Y-%m-%d') AS create_data
FROM `income`
GROUP BY create_data
ORDER BY create_data DESC

按周分组

SELECT SUM(income) AS income,
FROM_UNIXTIME(created, '%Y-%U') AS create_data
FROM `income`
GROUP BY create_data
ORDER BY create_data DESC

按半月分组

如果时间为 2019-01-1,代表上半个月,2019-01-2代表下半个月

SELECT SUM(income) AS income,
IF(FROM_UNIXTIME(created, '%d') <= 15, FROM_UNIXTIME(created, '%Y-%m-1'), FROM_UNIXTIME(created, '%Y-%m-2')) as create_data
FROM `income`
GROUP BY create_data
ORDER BY create_data DESC

按月分组

SELECT SUM(income) AS income,
FROM_UNIXTIME(created, '%Y-%m') AS create_data
FROM `income`
GROUP BY create_data
ORDER BY create_data DESC