SQL Count records within a month using a unix time

2019-02-20 11:23发布

问题:

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.

回答1:

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;


回答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`)