MySQL GROUP BY 年份怪异

Question

给定这张表:

CREATE TABLE `items` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`type` char(15) DEFAULT NULL,
`ext_id` varchar(255) DEFAULT NULL,
`timestamp` int(11) DEFAULT NULL,
`title` varchar(255) DEFAULT NULL,
`txt` text,
`url` varchar(255) DEFAULT NULL,
`longitude` float(9,6) DEFAULT NULL,
`latitude` float(9,6) DEFAULT NULL,
`meta` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=41695 DEFAULT CHARSET=utf8;

数据范围从 2005 年到 2013 年，所有数据都在 (int) timestamp 列中，此查询:

SELECT DATE_FORMAT(`timestamp`, '%Y') as 'year',
COUNT(id) as 'total'
FROM items
GROUP BY DATE_FORMAT(`timestamp`, '%Y')

返回这个:

'year' => null
'COUNT(*)' => string '38710' (length=5)

'year' => string '2011' (length=4)
'COUNT(*)' => string '45' (length=2)

'year' => string '2012' (length=4)
'COUNT(*)' => string '67' (length=2)

'year' => string '2013' (length=4)
'COUNT(*)' => string '90' (length=2)

为什么我得不到正确的结果？当我遍历 SELECT * FROM items , 我在打印时得到了正确的日期 <?php print date('Y-m-d H:i', $item->timestamp); ?> , 所以里面有日期。

Answer 1

DATE_FORMAT 想要一个 MySQL 日期类型作为它的参数，而不是一个表示 UNIX 时间戳的整数。如果您将日期存储为 UNIX 时间戳(顺便说一句，您通常不应该这样做)，那么您需要使用 FROM_UNIXTIME() 对其进行转换。还有一个返回年份的 YEAR() 函数，比使用 DATE_FORMAT 更简洁一些。

简而言之，您需要 YEAR(FROM_UNIXTIME(timestamp))