Need a query to determine who is logged in during a timeframe

Question

I have a table with user_id, session_start and session_end values.

I have a list of times in a file. For example, the list will look like the following:

2015-08-20 00:00:00
2015-08-20 00:15:00
2015-08-20 00:30:00
2015-08-20 00:45:00

So want to find all users who are logged in between that time and 15 minutes later, so all users logged in between (2015-08-20 00:00:00 and 2015-08-20 00:15:00, etc., etc.)

Session table looks like the following:

user_id    ----      session_start    ----     session_end
123               2015-08-20 00:01:00     2015-08-20 00:14:49
456               2015-08-20 00:17:31     2015-08-20 00:29:30

Query assistance would be quite helpful.

`select ... where '$time_from_file' between session_start and session_end`? — Marc B, Aug 24 '15 at 20:53
Bit confused - do you have a specific language you're trying to do this in or direct to MySQL? You probably should just leverage Python or PHP etc to iterate through the file and generate the query and results... — CogitoErgoSum, Aug 24 '15 at 20:54
If the user logs in at say, 3:00 in the morning, it also needs to capture those users who have a session_start of 1:00 AM and a session end of 19:00 — user3299633, Aug 24 '15 at 20:55
Try printing out the file and holding it up in front of the DB. — Hogan, Aug 24 '15 at 20:57

score 0 · Answer 1 · edited May 23 '17 at 11:43

Query:

SELECT user_id 
FROM table 
WHERE '$time' >= session_start 
AND '$time' <= session_end;

where table is the name of your table, and $time is one line value from your file in DATETIME format (YYYY-MM-DD HH:MM:SS)

You can loop through each line of your file like:

$handle = fopen("inputfile.txt", "r");
if ($handle) {
    while (($line = fgets($handle)) !== false) {
        // process the line read.
    }

    fclose($handle);
} else {
    // error opening the file.
}

Second example copied from How to read a file line by line in php

score 0 · Answer 2 · answered Aug 24 '15 at 20:59

Something like this

select user_id from mytable where session_start <= '2015-08-20 00:15:00' 
    AND session_end >= '2015-08-20 00:00:00'

This is saying give me records where: The user started the session before the ending time and was still logged in after the beginning time.

score 0 · Answer 3 · answered Aug 24 '15 at 21:01

If you're only looking for people who actually logged in during that period, you can use this query:

select * from session
where session_start >= '2015-08-20 00:00:00'
    and session_start < '2015-08-20 00:15:00';

If you're looking for people who were connected over that period, use this one:

select * from session
where session_start <= '2015-08-20 00:15:00'
    and session_end >= '2015-08-20 00:00:00';

Need a query to determine who is logged in during a timeframe

3 Answers3