返回过去 24 小时内添加的所有行的 MySQL 查询

Question

我有一个包含下一列的表格:

ip(varchar 255), index(bigint 20), time(timestamp)

每次在此处插入内容时，时间列都会获取当前时间戳。我想运行一个查询，返回过去 24 小时内添加的所有行。这是我尝试执行的:

SELECT ip, index FROM users WHERE ip = 'some ip' AND TIMESTAMPDIFF(HOURS,time,NOW()) < 24

而且它不起作用。

Answer 1

HOURS 应该是 HOUR。请参阅 TIMESTAMPADD 的文档查看 unit 参数的有效值:

TIMESTAMPADD(unit,interval,datetime_expr)

Adds the integer expression interval to the date or datetime expression datetime_expr. The unit for interval is given by the unit argument, which should be one of the following values: FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

所以你的查询应该是:

SELECT ip, index
FROM   users
WHERE  ip = 'some ip'
AND    TIMESTAMPDIFF(HOUR, time, NOW()) < 24

另请注意，此查询将无法按时利用索引(如果存在)。按如下方式重写查询可能会更有效:

SELECT ip, index
FROM   users
WHERE  ip = 'some ip'
AND    time > NOW() - interval 1 day