Unix时间戳不会工作

Question

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

Answer 1

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