get count of rows from start and end time

Question

i have table in which have two columns. one column(callstart) is showing that call start time. Another column(callend) which is showing that call end time.

Now i would like to make result for single day that will be in where condition like how many calls are running on that time. How many calls has been started on that time.

Table :

+-------------+-------------+------+-----+---------------------+-------+
| Field       | Type        | Null | Key | Default             | Extra |
+-------------+-------------+------+-----+---------------------+-------+
| id          | int(11)     | NO   |     | NULL                |       |
| user_id     | int(11)     | YES  |     | 0                   |       |
| callstart   | datetime    | NO   |     | 0000-00-00 00:00:00 |       |
| callend     | datetime    | NO   |     | 0000-00-00 00:00:00 |       |
+-------------+-------------+------+-----+---------------------+-------+
4 rows in set (0.00 sec)

Expected result :

+----+-----------+---------------------+----+-----+
| id | user_id   | date                | cc | cps |
+----+-----------+---------------------+----+-----+
|  1 |         3 | 2018-04-02 19:47:27 |  0 |   1 |
|  2 |         3 | 2018-04-02 19:48:52 |  1 |   1 |
|  3 |         3 | 2018-04-02 19:48:53 |  1 |   0 |
|  4 |         3 | 2018-04-02 19:48:54 |  1 |   0 |
|  5 |         3 | 2018-04-02 19:48:55 |  1 |   0 |
+----+-----------+---------------------+----+-----+

In above result :

cc : concurrent calls running on particular time. cps : calls which has been started on particular time.

Note :

  1. mysql version is 5.5.

  2. In above table,Data will be available only for single day( A call which has been ended on day upto call has been started on a day).

  3.I have make it working with php script.But its take long time to manage 6 months record.So i would like to make query which can gives result for every single day.

Fiddle : http://sqlfiddle.com/#!9/4c042/5

Expecting result on time : 2018-08-20 12:20:34 needs to show two rows

accountid | callstart           | cc |cps|
--------------------------------------------
56        | 2018-08-20 12:20:24 | 1  | 1 |
56        | 2018-08-20 12:20:25 | 1  | 0 |
56        | 2018-08-20 12:20:26 | 1  | 0 |
56        | 2018-08-20 12:20:27 | 1  | 0 |
56        | 2018-08-20 12:20:28 | 1  | 0 |
56        | 2018-08-20 12:20:29 | 1  | 0 |
56        | 2018-08-20 12:20:30 | 1  | 0 |
56        | 2018-08-20 12:20:31 | 1  | 0 |
56        | 2018-08-20 12:20:32 | 1  | 0 |
56        | 2018-08-20 12:20:33 | 1  | 0 |
56        | 2018-08-20 12:20:34 | 3  | 2 |
56        | 2018-08-20 12:20:35 | 3  | 0 |
56        | 2018-08-20 12:20:36 | 3  | 0 |
56        | 2018-08-20 12:20:37 | 3  | 0 |
56        | 2018-08-20 12:20:38 | 3  | 0 |
56        | 2018-08-20 12:20:39 | 3  | 0 |
56        | 2018-08-20 12:20:40 | 3  | 0 |
56        | 2018-08-20 12:20:41 | 2  | 0 |
56        | 2018-08-20 12:20:42 | 2  | 0 |
56        | 2018-08-20 12:20:43 | 2  | 0 |
56        | 2018-08-20 12:20:44 | 2  | 0 |
56        | 2018-08-20 12:20:45 | 2  | 0 |
56        | 2018-08-20 12:20:46 | 2  | 0 |
56        | 2018-08-20 12:20:47 | 2  | 0 |
56        | 2018-08-20 12:20:48 | 2  | 0 |
56        | 2018-08-20 12:20:49 | 2  | 0 |
56        | 2018-08-20 12:20:50 | 0  | 0 |
58        | 2018-08-20 12:20:29 | 1  | 1 |
58        | 2018-08-20 12:20:30 | 1  | 0 |
58        | 2018-08-20 12:20:31 | 1  | 0 |
58        | 2018-08-20 12:20:32 | 1  | 0 |
58        | 2018-08-20 12:20:33 | 1  | 0 |
58        | 2018-08-20 12:20:34 | 3  | 2 |
58        | 2018-08-20 12:20:35 | 3  | 0 |
58        | 2018-08-20 12:20:36 | 3  | 0 |
58        | 2018-08-20 12:20:37 | 3  | 0 |
58        | 2018-08-20 12:20:38 | 3  | 0 |
58        | 2018-08-20 12:20:39 | 3  | 0 |
58        | 2018-08-20 12:20:40 | 3  | 0 |
58        | 2018-08-20 12:20:41 | 3  | 0 |
58        | 2018-08-20 12:20:42 | 3  | 0 |
58        | 2018-08-20 12:20:43 | 3  | 0 |
58        | 2018-08-20 12:20:44 | 3  | 0 |
58        | 2018-08-20 12:20:45 | 3  | 0 |
58        | 2018-08-20 12:20:46 | 3  | 0 |
58        | 2018-08-20 12:20:47 | 3  | 0 |
58        | 2018-08-20 12:20:48 | 3  | 0 |
58        | 2018-08-20 12:20:49 | 2  | 0 |
58        | 2018-08-20 12:20:50 | 2  | 0 |
58        | 2018-08-20 12:20:51 | 2  | 0 |
58        | 2018-08-20 12:20:52 | 2  | 0 |
58        | 2018-08-20 12:20:53 | 2  | 0 |
58        | 2018-08-20 12:20:54 | 2  | 0 |
58        | 2018-08-20 12:20:55 | 2  | 0 |
58        | 2018-08-20 12:20:56 | 2  | 0 |
--------------------------------------------

Anyone please share some suggestion.

Yeah sure,Right now am using query : select count(id) as count,user_id from table where callstart <= '".$datetime."' AND end_stamp >='".$datetime."' and accountid > 0 and group by accountid . For datetime that is i am using from php script . — Ankit Doshi, Aug 18 '18 at 21:46

Ary Jazz · Answer 1 · 2018-08-20T06:22:21.807

1

EDIT: I didn't understand the question before, but seeing the expected results clarified it for me. In order to have a row for every second during a time window, you need to create a table with all the times you want to show.

Something like this:

CREATE TABLE `time_window` (
  `second` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `time_window` (`second`) VALUES
('2018-08-20 00:00:00'),
('2018-08-20 00:00:01'),
 ...
('2018-08-20 23:59:59');

Then, you will need to run the following query:

SELECT 
  b.accountid,
  a.second,
  SUM(CASE WHEN b.callstart <= a.second
      AND b.callend >= a.second THEN 1 ELSE 0 END) AS cc,
  SUM(CASE WHEN b.callstart = a.second THEN 1 ELSE 0 END) AS cps
FROM time_window a
LEFT JOIN custom_data b 
ON 1=1
GROUP BY 1 ,2
ORDER BY b.accountid, a.second

Here's the SQLFiddle

Hope it helps

edited Aug 20 '18 at 06:22

answered Aug 18 '18 at 22:00

Ary Jazz

1,576
1
16
25

Thanks for answer but still its not giving me accurate result – Ankit Doshi Aug 19 '18 at 17:02
In above query output comes is if on particular time two user found then sum of both user is coming on 1st row and second row showing like multiply by that user count Example :user a 1 cc,1 cps user b 2 cc,2 cps then 1st row showing of above query result is user a cc 3, cps 3 and user b cc:6,cps :6 – Ankit Doshi Aug 19 '18 at 18:15
@Ankit - I made a mistake on the JOIN already fixed i and updated the answer. It should work. Since no data was published my testing was limited to a few rows. Hope now it helps – Ary Jazz Aug 19 '18 at 20:07
Really appriciated for help. Sorry about i didn't add test data. i have make fiddle in which i am expecting result of query is on time 2018-08-20 12:20:34 accountid :58 cc : 3 cps :2 ,accountid :56 cc : 3 cps :2 Fiddle link : http://sqlfiddle.com/#!9/4c042/5 – Ankit Doshi Aug 20 '18 at 02:53
I have added expected result as well in body – Ankit Doshi Aug 20 '18 at 03:15
@AnkitDoshi, thanks for adding the data. Now I understand what are you trying to do. Updated the answer – Ary Jazz Aug 20 '18 at 06:28
Thanks for great help.Is it possible to create table with datetime for entire month instead of manually data ? If possible, would you like to share it ? – Ankit Doshi Aug 20 '18 at 07:28
I think you can. Check this previous question https://stackoverflow.com/questions/26981901/mysql-insert-with-while-loop – Ary Jazz Aug 20 '18 at 07:35

get count of rows from start and end time

1 Answers1