SQL Count records within a month using a unix timestamp

Question

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.

You want to count Jan/2010 and Jan/2011 separately, right? — Salman Arshad
– Salman Arshad, Commented May 28, 2011 at 10:25

Bjoern · Accepted Answer · 2011-05-28 10:24:43Z

7

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;

edited May 28, 2011 at 10:24

answered May 28, 2011 at 10:18

Bjoern

16.3k4 gold badges47 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Salman Arshad · Accepted Answer · 2011-05-28 10:21:53Z

3

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

answered May 28, 2011 at 10:21

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Collectives™ on Stack Overflow

SQL Count records within a month using a unix timestamp

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related