如何在 MySQL 中选择当前时间戳内 10 分钟内的所有记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9013494/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to select all records that are 10 minutes within current timestamp in MySQL?
提问by TK123
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 分钟内的所有记录（以最有效的方式）？
回答by piotrm
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 就可以使用在时间戳列上定义的索引。
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 条件的行，而无需检查表中的每一行。
回答by Michael Berkowski
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 分钟内进行记录，但我认为这意味着过去不超过 10 分钟（不是未来）：
SELECT col1, col2, col3
FROM table
WHERE DATE_ADD(last_seen, INTERVAL 10 MINUTE) >= NOW();
This adds 10 minutes to last_seenand compares that against the current time. If the value is greater, last_seenis less than ten minutes ago.
这将增加 10 分钟last_seen并将其与当前时间进行比较。如果该值更大，last_seen则小于十分钟前。
See the documentation on DATE_ADD()for an explanation of how it is used.
有关如何使用它的说明，请参阅 上的文档DATE_ADD()。
回答by Abdulaziz Hamdan
In case the method didn't work for some.
如果该方法对某些人不起作用。
The 'where' condition in answers above didn't work for me until i added the timestamp column inside date() function (suggested by @karam qubsi) :
上面答案中的“where”条件对我不起作用，直到我在 date() 函数中添加了时间戳列（@karam qubsi建议）：
SELECT * FROM myTable
WHERE date(last_seen)>= NOW() - INTERVAL 10 MINUTE
SELECT * FROM myTable
WHERE date(last_seen)>= NOW() - 间隔 10 分钟
Only then i started getting correct results.
直到那时我才开始得到正确的结果。
回答by Ramblin
For anyone wanting to extend the question beyond just one table and find any entries in any table in a database with date_modified within the last (say) 5 minutes, try this script.
对于任何想要将问题扩展到一个表之外并在最后（例如）5 分钟内使用 date_modified 在数据库中的任何表中查找任何条目的任何人，请尝试使用此脚本。
Adjust the entries for database (I used a database called mgmt2go) and save the file below to an executable .sql file.
调整数据库的条目（我使用了一个名为 mgmt2go 的数据库）并将下面的文件保存到一个可执行的 .sql 文件中。
Adapt as you want for your situation, but if you only change the database setting, it will do the same for your database.
根据您的情况进行调整，但如果您只更改数据库设置，它将对您的数据库执行相同的操作。
I run this SQL script from a command line and output the results to a file so I can refer to the output when I am debugging a system that writes all settings to a MySQL (actually MariaDB) database. I needed to figure out what was being affected when i made changes through the system user interface.
我从命令行运行此 SQL 脚本并将结果输出到文件，以便在调试将所有设置写入 MySQL（实际上是 MariaDB）数据库的系统时可以参考输出。我需要弄清楚当我通过系统用户界面进行更改时会受到什么影响。
# For this setup, it is best to send the output to a file
# and then read the file to see what records in what tables are found
# Save this file as list_tables.sql (or whatever you want to save it as)
# and use it by entering, at the CLI
# mysql --user=UserName --password=UserPassword -vv 2>&1 < path_to_executable_file/list_tables.sql > path_to_output_file/test.output.txt
# If you want the filename to include the time period,
# concatentate the bash date function with the filename
# ie: "mgmt2goDbaseChangesSince_"$(date +%Y-%m-%d_%H:%M:%S --date -'5 min')
# so the full sql comand would now look like
# mysql --user=UserName --password=UserPassword -vv 2>&1 < path_to_executable_file/list_tables.sql > "path_to_output_file/mgmt2goDbaseChangesFrom_"$(date +%Y-%m-%d_%H:%M:%S --date -'5 min')"_to_"$(date +%H:%M:%S)
# Set the delimeter so you can distingish the delimiter surrounding the Procedure
# (as an intact entity to be passed to the server)
# from the statements inside the Procedure calls
DELIMITER $$
# In case a previous script run created the procedure,
# delete it before running this script again
DROP PROCEDURE IF EXISTS `mgmt2go`.rc_list_tables_procedure$$
# Create a procedure to identify all the tables in the database
# (in this case, I have a database named mgmt2go)
# that contain multiple fields (id, date_modified) against which you want to search
CREATE PROCEDURE `mgmt2go`.rc_list_tables_procedure( IN rc_cutoff_date DATETIME )
BEGIN
# Always DECLARE in sequence: Variables, Cursors, Handler
# Variables
DECLARE rc_last_record BOOLEAN DEFAULT FALSE;
DECLARE rc_table_name varchar(64);
# Cursor
DECLARE rc_table_list CURSOR FOR SELECT DISTINCT `TABLE_NAME`
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `INFORMATION_SCHEMA`.`COLUMNS`.`TABLE_SCHEMA` = 'mgmt2go'
AND `INFORMATION_SCHEMA`.`COLUMNS`.`COLUMN_NAME`
IN ( 'id' , 'date_modified' )
GROUP BY TABLE_NAME HAVING COUNT( DISTINCT COLUMN_NAME ) = 2
ORDER BY `INFORMATION_SCHEMA`.`COLUMNS`.`TABLE_NAME`;
# Handler
DECLARE CONTINUE HANDLER FOR NOT FOUND SET rc_last_record = TRUE;
# Activate the LOOP
OPEN rc_table_list;
rc_for_each_loop: LOOP
# Test if the previous iteration of the LOOP fetched the last record
# and if so, exit the LOOP
FETCH FROM rc_table_list INTO rc_table_name;
IF rc_last_record = TRUE THEN
LEAVE rc_for_each_loop;
END IF;
# Eliminate the entries in the job_queue table
# since they are just temporary system entries
IF rc_table_name = 'job_queue' THEN
ITERATE rc_for_each_loop;
END IF;
# Find all records in all tables with a date_modified field
# greater than or equal to the specified value in the CALL
# We cannot directly use a variable in a SELECT statement
# So we prepare a SELECT statement by concatenation
# and then use the Prepare and Execute functions to run the SELECT
SET @rc_query_for_modified := CONCAT(
'SELECT "' , rc_table_name , '" , `id` ,`date_modified` '
'FROM `mgmt2go`.`' , rc_table_name , '` '
'WHERE '
'TIMESTAMP( `mgmt2go`.`' , rc_table_name , '`.`date_modified` )'
' >= "' , rc_cutoff_date , '" '
);
PREPARE rc_test_modified_statement FROM @rc_query_for_modified;
EXECUTE rc_test_modified_statement;
DEALLOCATE PREPARE rc_test_modified_statement;
END LOOP rc_for_each_loop;
CLOSE rc_table_list;
END$$
# Reset the delimiter back to the default
DELIMITER ;$$
# Invoke the Procedure, providing the cutoff date = date record more current than
# You can provide an absolute time using
# CALL `mgmt2go`.rc_list_tables_procedure( "2019-01-02 16:49:28" );
# Or you can provide a time n (Seconds/Minutes/...) before a stated time (like NOW()
CALL `mgmt2go`.rc_list_tables_procedure( DATE_SUB( NOW() , INTERVAL 5 MINUTE ) );
# If you are also trying to see if any files have changed in the last n minutes
# and want to NOT search in a particular child directory (/cache in this case)
# at the CLI enter
# find path_to_directory -path path_to_child_directory_to_exclude -prune , -type f -mmin -120
# or
# find path_to_directory -mmin -120 -iname "*.*" | egrep -v "(child_directory_to_exclude)"
# where -120 is the number of minutes to look back (120 in this case)
# -mmin is minutes since modification and -cmin is minutes since creation
# -mtime is days since modification and -ctime is days since creation
