SQL使用unix时间戳对一个月内的记录进行计数

Question

我试图返回每个月内的记录数，并按月/年对结果进行分组。

架构看起来像这样：

id    title    timestamp

我一直在找，但没能得到预期的结果。谢谢。

Answer 1

格式化时间戳，然后按其分组。

按月份分组：

SELECT DATE_FORMAT(t.timestamp, "%Y-%m") AS "_Month", COUNT(*)
FROM yourtable as t
GROUP BY _Month;

按年份分组：

SELECT DATE_FORMAT(t.timestamp, "%Y") AS "_Year", COUNT(*)
FROM yourtable as t
GROUP BY _Year;

如果时间戳字段存储为unixtime值，只需将FROM_UNIXTIME()包裹在字段周围即可：

SELECT DATE_FORMAT(FROM_UNIXTIME(t.timestamp), "%Y") AS "_Year", COUNT(*)
FROM yourtable as t
GROUP BY _Year;

Answer 2

SELECT YEAR(`timestamp`) AS Y, MONTH(`timestamp`) AS M, COUNT(1) AS C
FROM that_table
GROUP BY YEAR(`timestamp`), MONTH(`timestamp`)
ORDER BY YEAR(`timestamp`), MONTH(`timestamp`)