Before i used datetime for post created time from database,
SELECT MONTH(created)+1, count(*)
FROM post_comments
WHERE YEAR(created) = YEAR(CURDATE())
group by MONTH(created)
ORDER BY MONTH(created)
and now i am using unix timestamp. How I need to make a change in the above code, to work it unix timestamp.
Before the created time looks like this: 2018-04-28 09:03:02
and now the created time looks like this: 1524921263
You can convert everything to dates using FROM_UNIXTIME():
SELECT MONTH(FROM_UNIXTIME(created))+1, count(*)
FROM post_comments
WHERE YEAR(FROM_UNIXTIME(created)) = YEAR(CURDATE())
GROUP BY MONTH(FROM_UNIXTIME(created))
ORDER BY MONTH(FROM_UNIXTIME(created));
I find it odd that you are adding 1 to the month in the SELECT.
You can change the WHERE to:
WHERE created >= UNIX_TIMESTAMP(DATE(CONCAT(YEAR(CURDATE), '-01-01')))
In general, it is a good idea to avoid the use of functions on columns. This is less important when you are selecting a significant number of rows (unless the column is a clustered index).
WHERE created without YEAR(FROM_UNIXTIME(created)) ?