-1

Find out the number of concurrent users in some moment in time (f.ex. 12:00) of every day within provided dates. The table I have is id, login, logout as datetime

So I can get following: between 2018-04-01 and 2018-04-06 at noons there were 4 concurrent users at 2018-04-01 12:00, 5 at 2018-04-02 12:00 etc. 

--------------------------------------
id      login             logout  
--------------------------------------
1  2018-04-01 13:00      2018-04-02 13:00
2  2018-04-02 8:00       2018-04-04 16:00

make a query between two dates 2018-04-01 and 2018-04-03 for 12:00

-------------------------------------    
date             Number of conc.users 
--------------------------------------   
2018-04-01             0
2018-04-02             2
2018-04-03             1

That's what I got

set @DATE = '2018-04-01 12:00:00';
SELECT @DATE := DATE_ADD(@DATE, INTERVAL 1 DAY) AS DATE, COUNT(*) FROM Tasks WHERE start <= STR_TO_DATE(@DATE, '%Y-%m-%d %H:%i:%s') and end >= STR_TO_DATE(@DATE, '%Y-%m-%d %H:%i:%s')

I try to make a repeat query in order to dynamicly change the date, because it works this way

SELECT  '2018-04-01 12:00:00' AS DATE, COUNT(*) FROM Tasks WHERE start <= STR_TO_DATE(@DATE, '%Y-%m-%d %H:%i:%s') and end >= STR_TO_DATE(@DATE, '%Y-%m-%d %H:%i:%s')

but just for one date

Sergei Shumilin
  • 411
  • 5
  • 17
  • 1
    Add some sample table data and the expected result (as formatted text, not images.) Also show us your current query attempt. – jarlh Apr 09 '18 at 12:12
  • Hint: `COUNT(*)` in combination with `WHERE (login BETWEEN 2018-04-01 AND 2018-04-06) AND (logout BETWEEN 2018-04-01 AND 2018-04-06)` and `GROUP BY DATE(login)` might put you the correct way to get the results you need..But hard to say without example data.. – Raymond Nijland Apr 09 '18 at 12:15

1 Answers1

1

Something like this should do the trick:

SELECT * FROM users WHERE :date BETWEEN login AND logout

Edit: Since OP provided some sample data and the result he was expecting it was more clear what he intended to do.

I'm not sure if a SQL only solution is the most elegant way to do this, since you have to provide a list of all possible days you want to check like described here: Get a list of dates between two dates

But here is a way how it would work:

select a.selected_date, count(u.id) as count 
from users u
join (select * from 
 (select adddate('1970-01-01 12:00',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2018-04-02' and '2018-04-04') as a on a.selected_date >= login and a.selected_date <= logout
group by a.selected_date

So basically this joins all of your login sessions with all possible dates at 12:00 in the provided date range.

I also created a SQLfiddle here: http://sqlfiddle.com/#!9/4a375b/12

edefritz
  • 13
  • 3