我试图在过去7天内获得注册用户，因此我有了用户表
+-----+------------+--------------+
| ID | USERNAME | ADDED |
+-----+------------+--------------+
| 1 | Vlad | 1347386878 |
+-----+------------+--------------+
| 2 | Test | 1347386578 |
+-----+------------+--------------+
我尝试了以下sql，但输出为空，没有错误。。。我需要一些从今天到7天前的东西
SELECT date(added), COUNT(id) AS num_registered
FROM users
WHERE added < CURDATE()
AND added > CURDATE() - INTERVAL 7 DAYS
GROUP BY date(added) LIMIT 1, 7
有什么建议吗？
编辑：
$mysql_query = mysql_query('SELECT added, DATE(added), COUNT(id) AS num_reg FROM users_test WHERE added < (UNIX_TIMESTAMP() - (7 * 24 * 60 * 60)) GROUP BY DATE(added) LIMIT 1, 7') or die(mysql_error());
while($row = mysql_fetch_array($mysql_query))
{
$month = date('F', $row['added']);
$day = date('j', $row['added']);
$textbuilder .= '
<li>
<a href="#" title="'.$month.' '.$day.', '.$row['num_reg'].' registered">
<span class="label">'.$day.'</span>
<span class="count" style="height: 20%">('.$row['num_reg'].')</span>
</a>
</li>';
}
表格：
CREATE TABLE IF NOT EXISTS `users_test` (
`id` int(10) NOT NULL,
`username` varchar(60) NOT NULL,
`added` int(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `users_test` (`id`, `username`, `added`) VALUES
(1, 'Test', 1347303641),
(2, 'Test1', 1347217241),
(3, 'Test2', 1347130841),
(4, 'Test3', 1347044441);
ADDED是UNIX时间戳值，所以只需执行以下操作：
SELECT
DATE(Added), Count()
FROM
Users
WHERE
Added > ( UNIX_TIMESTAMP() - ( 7 * 24 * 60 * 60 ) )
GROUP BY
DATE(Added)
LIMIT
1, 7
请注意，这在处理闰秒的情况下不起作用，因为它使用了一天的近似定义24*60*60，但我想你可以接受这一点。
您需要使用FROM_UNIXTIME()将时间戳转换为datetime。由于有大量的转换正在进行，因此使用派生表会更容易：
SELECT DATE(Added), COUNT(id) AS num_registered FROM
(SELECT id, username, FROM_UNIXTIME(added) AS added FROM users) u
WHERE added < CURDATE()
AND added > CURDATE() - INTERVAL 7 DAYS
GROUP BY DATE(added) LIMIT 1, 7
SELECT FROM_UNIXTIMESTAMP(added, , '%b %D, %Y'), COUNT(id) AS num_registered
FROM users
WHERE added > unix_timestamp() - ( 7 - 86400 )
GROUP BY FROM_UNIXTIMESTAMP(added, , '%b %D, %Y')