DATE（时间戳）返回错误的月份

Question

Here is my query:

$chart_result = mysql_query("SELECT * 
                             FROM (SELECT DATE(timestamp) AS ts, 
                                          YEAR(timestamp) AS year, 
                                          MONTH(timestamp) AS month, 
                                          DAY(timestamp) AS day, 
                                          SUM(paymentgross) AS sales 
                                   FROM stats_ans_1 
                                   GROUP BY DATE(timestamp) DESC 
                                   LIMIT 10
                                  ) AS TBL 
                             GROUP BY ts");

and here is the timestamp:

2012-09-12 19:45:23

MONTH(timestamp) is absolutely determined that the timestamp is in October not September - which is obviously wrong. Have mercy on my poor brain cells!

Answer 1

You can use DATE_FORMAT function to return month in string format:

SELECT DATE_FORMAT('2012-09-12 19:45:23', '%M') AS `month`;

Also you don't need to use GROUP BY twice, try this query:

SELECT DATE(timestamp) AS ts,
       YEAR(timestamp) AS year,
       MONTH(timestamp) AS month,
       DAY(timestamp) AS day,
       SUM(paymentgross) AS sales
FROM stats_ans_1
GROUP BY DATE(timestamp) DESC
LIMIT 10;