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
– skiwi, Commented Dec 8, 2013 at 21:29
Check out this post on StackOverflow: stackoverflow.com/questions/1764881/… Some ideas might come in handy. — GregD
– GregD, Commented Dec 8, 2013 at 21:31

juergen d · Accepted Answer · 2013-12-09 06:30:37Z

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 9, 2013 at 6:30

juergen d

205k40 gold badges304 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

slaakso · Accepted Answer · 2013-12-08 21:38:15Z

0

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

answered Dec 8, 2013 at 21:38

slaakso

9,1452 gold badges19 silver badges29 bronze badges

Collectives™ on Stack Overflow

MySQL concurrent select from rows

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related