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-value，只需将 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`)