CODEWITHSUNDEEP
phpmysqlsql
AlwaysStudent
AlwaysStudent

Reputation: 1374

Unix timestamp wont work

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

Upvotes: 0

Views: 63

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

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

Upvotes: 1

Related Questions