select from mysql table between datetime x min ago and datetime x min ago

Question

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.

Answer 1

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 )