September 8, 2011 16:47

If you have a table in MySQL with a Unix timestamp column (int 11) and you'd like to group results by date, you can run this simple query.

SELECT
    COUNT(`id`) AS `total`,
    DATE(FROM_UNIXTIME(`my_timestamp`)) AS `my_date`
FROM `my_table`
GROUP BY `my_date` DESC

If you want to group by month and year, ignoring the date, you can modify the query like this:

SELECT
    COUNT(`id`) AS `total`,
    MONTH(FROM_UNIXTIME(`my_timestamp`)) AS `my_month`,
    YEAR(FROM_UNIXTIME(`my_timestamp`)) AS `my_year`,
FROM `my_table`
GROUP BY `my_year`, `my_month` DESC

MySQL