从日期时间 x 分钟前和日期时间 x 分钟前之间的 mysql 表中选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4838120/
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
select from mysql table between datetime x min ago and datetime x min ago
提问by ganjan
I think I summed nicely it up in the title. I want to select online users from a specific time to another specific time. My table look like this:
我想我在标题中很好地总结了它。我想选择从特定时间到另一个特定时间的在线用户。我的桌子看起来像这样：
CREATE TABLE online (
id bigint(20) NOT NULL auto_increment,
`username` varchar (16) NOT NULL,
`ip` varchar(39) NOT NULL default '',
`time` datetime NOT NULL default '0000-00-00 00:00:00' ,
PRIMARY KEY (id)
);
I want a query that return the username's that have been online the last 15 minutes.
我想要一个查询，返回username已在线的last 15 minutes.
And a query for the users that have been online the last 60 minutes, but not the last 15 minutes. So the query's don't return the same values. This I don't know how to do.
并查询已在线的用户last 60 minutes, but not the last 15 minutes。所以查询不会返回相同的值。这个我不知道怎么办。
回答by Mark Byers
For your first query:
对于您的第一个查询：
SELECT username
FROM online
WHERE time > NOW() - INTERVAL 15 MINUTE
And for your second:
对于你的第二个：
SELECT username
FROM online
WHERE time BETWEEN NOW() - INTERVAL 60 MINUTE AND NOW() - INTERVAL 15 MINUTE
Both these queries assume that each user only appears once in the online table (and if this is indeed the case you should add a UNIQUE constraint to enforce that).
这两个查询都假设每个用户在在线表中只出现一次（如果确实是这种情况，您应该添加一个 UNIQUE 约束来强制执行）。
If a username can appear more than once in the table you just need to add DISTINCT after SELECT for your first query, but you need a slightly different approach for your second query:
如果一个用户名可以在表中出现多次，您只需要在 SELECT 后为您的第一个查询添加 DISTINCT，但您的第二个查询需要稍微不同的方法：
SELECT DISTINCT username
FROM online
WHERE time > NOW() - INTERVAL 60 MINUTE
AND NOT EXISTS
(
SELECT *
FROM online
WHERE time > NOW() - INTERVAL 15 MINUTE
)
回答by OMG Ponies
Use DATE_SUBto subtract time from the DATETIME returned from NOW():
使用DATE_SUB从 NOW() 返回的 DATETIME 中减去时间：
last 15 minutes
最后 15 分钟
SELECT o.*
FROM ONLINE o
WHERE o.time >= DATE_SUB(NOW(), INTERVAL 15 MINUTE)
last 60 minutes, but not the last 15 minutes
最后 60 分钟，但不是最后 15 分钟
SELECT o.*
FROM ONLINE o
WHERE o.time BETWEEN DATE_SUB(NOW(), INTERVAL 60 MINUTE)
AND DATE_SUB(NOW(), INTERVAL 15 MINUTE)
Duplicate handling costs extra.
重复处理需要额外费用。
