如何在MySQL当前时间戳中选择10分钟内的所有记录? [英] How to select all records that are 10 minutes within current timestamp in MySQL?

问题描述

我在名为"last_seen"的表列中有时间戳，如下所示:

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

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

etc...

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

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

推荐答案

最有效的方法是将您的时间戳(并且只有时间戳，不使用任何函数)与一个表达式进行比较，该表达式可以计算为每个常量行，这样mysql可以使用在timestamp列上定义的索引.

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.

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

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

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.

这篇关于如何在MySQL当前时间戳中选择10分钟内的所有记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！