MySQL concurrent select from rows

Question

I have a table that has start_date and end_date timestamps, the rows contain data from a radius accounting table.

Shortly once a user logs in, a row is inserted with the start_date timestamp, and once the user logs off, the end_date is populated with an UPDATE.

Here are couple of rows:

ID               |  start_date          |   end_date
22                2013-11-19 12:00:22      2013-11-20 14:20:22   (*)
23                2013-11-19 12:02:22      2013-11-20 15:20:22   (*)
23                2013-11-19 17:02:22      2013-11-20 20:20:22
24                2013-11-20 12:06:22      2013-11-20 15:20:22   *
25                2013-11-20 12:40:22      2013-11-20 15:23:22   *
26                2013-11-20 12:50:22      2013-11-20 17:23:22   *
27                2013-11-20 16:56:22      2013-11-20 17:29:22   
28                2013-11-20 17:58:22      2013-11-20 20:24:22

So in this case, for 2013-11-19 the max number of concurrent user is 2 (marked with (*) )(their times between start and end overlaps), for 2013-11-20 it is 3 (marked with *)

I am trying to write an SQL query to get the number of most concurrent users in a day (based on the start and end date), so a short result would be that on 2013-08-12, the most online at the same time is xx number.

I could do this in PHP by analyzing row by row, but I would like to keep it as an SQL query.

Take a look into `GROUP BY` and `DAY(date)` to extract the day part of a date, with `COUNT(*)` you can show the number of occurences. It's not possible to give a full query though (for me) without knowing the table structure. — skiwi, Dec 08 '13 at 21:29
Check out this post on StackOverflow: http://stackoverflow.com/questions/1764881/mysql-getting-data-for-histogram-plot Some ideas might come in handy. — GregD, Dec 08 '13 at 21:31

score 2 · Accepted Answer · answered Dec 09 '13 at 06:30

2

Try

select d, count(*) as user_count
from
(
   select start_date as d from your_table
   union all
   select end_date as d from your_table
) x
group by d
order by user_count desc

answered Dec 09 '13 at 06:30

juergen d

201,996
37
293
362

score 0 · Answer 2 · answered Dec 08 '13 at 21:38

You could calculate count of concurrent users on each datapoint you have (start_date / end_date) and then calculate the max out of that:

select max(cnt)
from (
  select q.t, count(*) as 'cnt'
  from (
    select start_date as 't'
    from log
    where start_date between YOUR_START_DATE and YOUR_END_DATE
    union
    select end_date
    from log
    where end_date between YOUR_START_DATE and YOUR_END_DATE
  ) as q
    join log l on q.t between l.start_date and l.end_date
  group by q.t
) a

MySQL concurrent select from rows

2 Answers2