SQL 如何查询最近5分钟的记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23228133/
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 can query last 5 minutes of records?
提问by user3559774
How I can to see last 5 mins record before the current time through sql query how i can do this. The format of time stamp is
我如何通过 sql 查询查看当前时间之前的最后 5 分钟记录我如何做到这一点。时间戳的格式为
03/25/2014 14:00:00
I used this query for the same
我用这个查询相同
SELECT Time stamp FROM TABLE
WHERE S >1 AND SUBSTRING((Time stamp,15,2)-5)
is this fine of any other way to do the same
用其他方式做同样的事情好吗？
回答by Gordon Linoff
If your timestamp is a date column, you can simply do:
如果您的时间戳是日期列，您可以简单地执行以下操作：
select t.*
from table t
where t.timestamp >= sysdate - 5/(24*60)
Things are a bit more interesting if timestampis a character column. Then you need to translate it to a date/time:
如果timestamp是字符列，事情会更有趣。然后您需要将其转换为日期/时间：
select t.*
from table t
where to_date(t.timestamp, 'MM/DD/YYYY HH24:MI:SS') >= sysdate - 5/(24*60)
回答by a_horse_with_no_name
select *
from the_table
where timestamp_column <= timestamp '2014-03-25 14:00:00' - interval '5' minute;
This assumes that timestamp_columnis defined with the data type timestamp.
这假设timestamp_column是用数据类型定义的timestamp。
If it isn't you should stop nowand re-define your table to use the correct data type.
如果不是，你应该停止，现在，重新定义你的表来使用正确的数据类型。
The expression timestamp '2014-03-25 14:00:00'is a (ANSI SQL) timestamp literal.
该表达式timestamp '2014-03-25 14:00:00'是一个 (ANSI SQL) 时间戳文字。
It's equivalent to to_timestamp('2014-03-25 14:00:00', 'yyyy-mm-dd hh24:mi:ss')but I prefer the ANSI literal because it's less typing and works across multiple DBMS.
它等同于to_timestamp('2014-03-25 14:00:00', 'yyyy-mm-dd hh24:mi:ss')但我更喜欢 ANSI 文字，因为它的输入更少并且可以跨多个 DBMS 工作。
回答by juergen d
If you are using MySQL and your timestampcolumn is of data type datetimeyou can do
如果您使用的是 MySQL 并且您的timestamp列是数据类型，datetime您可以这样做
SELECT Timestamp
FROM your_table
WHERE Timestamp >= now() - interval 5 minute
回答by xQbert
Here's an example of how to get 5 minutes ago in oracle. subtracting from a timestamp in increments of 1 where 1 is a day. so 5 minutes would be 5/(24hours*60min) of a day.
这是如何在 oracle 中获取 5 分钟前的示例。以 1 为增量从时间戳中减去，其中 1 是一天。所以 5 分钟是一天的 5/（24 小时 * 60 分钟）。
SELECT sysdate, sysdate-(5/(24*60)) as min_Ago5 from dual
