mysql - 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;