如何选择MySQL中当前时间戳内10分钟的所有记录？

I have timestamps in a table column called "last_seen" like so:

我在一个名为“last_seen”的表列中有时间戳，如下所示：

2012-01-25 18:46:42
2012-01-23 08:19:04
2012-01-23 08:19:04

etc...

How can I get all records where the timestamp is within 10 minutes of the current timestamp (in the most efficient way)?

如何获取时间戳在当前时间戳的10分钟内的所有记录（以最有效的方式）？

2 个解决方案

#1

The most efficient way would be to compare your timestamp (and only timestamp, without using any functions on it) to an expression that can be calculated as constant for every row, this way mysql can use index defined on your timestamp column.

最有效的方法是将时间戳（和时间戳，不使用其上的任何函数）与可以计算为每行的常量的表达式进行比较，这样mysql可以使用在timestamp列上定义的索引。

SELECT * FROM myTable
WHERE last_seen >= NOW() - INTERVAL 10 MINUTE

You can always try EXPLAIN SELECT ... to see if an index can be used to find rows satisfying your WHERE condition without the need to check every row in the table.

您始终可以尝试EXPLAIN SELECT ...以查看索引是否可用于查找满足WHERE条件的行，而无需检查表中的每一行。

#2

Your question asks for records within 10 minutes of the current timestamp, but I will take that to mean no more than ten minutes in the past (not the future):

您的问题要求在当前时间戳的10分钟内记录，但我认为这意味着过去（不是将来）不超过十分钟：

SELECT col1, col2, col3 
FROM table
WHERE DATE_ADD(last_seen, INTERVAL 10 MINUTE) >= NOW();

This adds 10 minutes to last_seen and compares that against the current time. If the value is greater, last_seen is less than ten minutes ago.

这为last_seen增加了10分钟，并将其与当前时间进行了比较。如果值更大，则last_seen小于十分钟前。

See the documentation on DATE_ADD() for an explanation of how it is used.

有关如何使用它的说明，请参阅DATE_ADD（）上的文档。

#1

SELECT * FROM myTable
WHERE last_seen >= NOW() - INTERVAL 10 MINUTE