Unix时间戳替代datetime后，如何修改帖子评论统计的SQL语句？

No problem at all—since you've switched from storing created as a datetime to a Unix timestamp (integer representing seconds since epoch), we just need to add a conversion step to turn that timestamp back into a date format that MySQL's YEAR() and MONTH() functions can work with.

Modified Query #

Here's the updated version of your SQL:

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

What Changed? #

• Everywhere you previously used created directly with date functions, we now wrap it in FROM_UNIXTIME(created). This function converts your Unix timestamp integer into a standard datetime string that YEAR() and MONTH() can parse correctly.
• Your original logic (adding 1 to the month number, grouping/ordering by month) stays exactly the same—we're just adding the conversion layer for the new timestamp format.

Optional Optimization (Avoid Repeating Functions) #

If you want to clean up the query and avoid calling FROM_UNIXTIME() multiple times, you can use a subquery or column alias (MySQL supports referencing aliases in GROUP BY/ORDER BY in most versions):

SELECT month_num + 1, count(*)
FROM (
    SELECT MONTH(FROM_UNIXTIME(created)) AS month_num
    FROM post_comments
    WHERE YEAR(FROM_UNIXTIME(created)) = YEAR(CURDATE())
) AS comment_months
GROUP BY month_num
ORDER BY month_num

Quick Note on Timestamp Format #

Just double-check: your example timestamp (1524921263) is second-level (which matches what FROM_UNIXTIME() expects). If you ever switch to millisecond-level timestamps (e.g., 1524921263000), you'd need to divide by 1000 first: FROM_UNIXTIME(created / 1000).

内容的提问来源于stack exchange，提问作者AlwaysStudent