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

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开发者_C百科'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
)

Use DATE_SUB to subtract time from the DATETIME returned from NOW():

last 15 minutes

SELECT o.*
  FROM ONLINE o
 WHERE o.time >= DATE_SUB(NOW(), INTERVAL 15 MINUTE) 

last 60 minutes, but not the last 15 minutes

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.