从datetime x min前和datetime x min之前的mysql表中选择 [英] select from mysql table between datetime x min ago and datetime x min ago
问题描述
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默认'0000-00-00 00 :00:00',
PRIMARY KEY(id)
);
我想要一个返回用户名的查询
已经在线上最近15分钟
。
并且查询已上线的的用户,最后60分钟,但不是最近15分钟。
。所以查询不返回相同的值。我不知道该怎么办
对于您的第一个查询:
SELECT username
FROM online
WHERE time> NOW() - INTERVAL 15 MINUTE
您的第二个:
SELECT username
FROM online
WHERE TIME BETWEEN NOW() - INTERVAL 60 MINUTE AND NOW() - INTERVAL 15 MINUTE
这两个查询都假定每个用户只在在线表中出现一次(如果确实如此,您应该添加一个如果一个用户名可以在表中出现多次,那么只需要在第一个查询的SELECT之后添加DISTINCT,但是您需要一个您的第二个查询略有不同的方法:
SELECT DISTINCT username
FROM online
WHERE time>现在() - 间隔60分钟
和不存在
(
SELECT *
FROM online
WHERE时间> NOW() - INTERVAL 15 MINUTE
)
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
.
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.
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).
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 username
FROM online
WHERE time > NOW() - INTERVAL 60 MINUTE
AND NOT EXISTS
(
SELECT *
FROM online
WHERE time > NOW() - INTERVAL 15 MINUTE
)
这篇关于从datetime x min前和datetime x min之前的mysql表中选择的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!