Mysql : Count Posts and Group by date

Question

Hello , I am not very good at sql , generally I use php to do my complicated tasks , But in this task , there are lots of data , so using php for counting posts is very slow. So I want a sql which counts post by date , but my date column in table is php's time stamp (int).I will crate post number x date chart

Answer 1

For not knowing SQL you seem to have a decent grasp on the terminology.

Something like this should work:

  SELECT FROM_UNIXTIME(post_date, '%Y %D %M') AS POST_DATE,
         COUNT(post_id) AS POST_COUNT
    FROM posts
GROUP BY POST_DATE

Answer 2

Not sure about MySQL, but in postgres you could do something like (for a table named posts with at least the columns id and php_timestamp):

SELECT COUNT(id), php_timestamp - (php_timestamp % 86400) AS base_date
FROM posts
GROUP BY php_timestamp - (php_timestamp % 86400)

Of course, an index on the expression php_timestamp - (php_timestamp % 86400) would be highly recommended (not sure you can create one in MySQL though). This groups by the date in UTC.