使用unix时间戳记在一个月内使用SQL Count记录 [英] SQL Count records within a month using a unix timestamp

问题描述

我想在每个月内返回记录数，并按月/年分组结果。

  id标题时间戳
  

我一直在搜索，但我不能得到结果，因为我的预期。谢谢。

解决方案

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

按月分组：

  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; 
  

I'm trying to return the count of records within each month and group the result by month / year.

Schema looks something like this:

id    title    timestamp

I've been searching around but I can't get the result as I expect it. Thanks.

解决方案

Format the timestamp, then group by it.

Group by Month:

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

Group by Year:

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

If the timestamp-field is stored as a unixtime-value, just wrap FROM_UNIXTIME() around the field:

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

这篇关于使用unix时间戳记在一个月内使用SQL Count记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！