CODEWITHSUNDEEP
mysqldatabasedatetimetimegroup-by
Lee
Lee

Reputation: 149

MySQL data count in 10 minute intervals

I have a table that records the datetime a student leaves along with their details.

What I want to do is count how many students leave between two set times listing all the times at 10 minute intervals.

This script will count the number of students who leave but only if students leave in the 10 minute interval being counted, it just skips any 10 minutes that have no students leaving.

I need it to return all the time intervals between the datetimes given with 0 if no students leave in that time interval so that no time is missed.

SELECT
    FROM_UNIXTIME(
        CEILING(
            UNIX_TIMESTAMP(`time_left`) / 600
        ) * 600
    ) AS timeslice,
    COUNT(*) AS mycount
FROM
    tbl_student_log
WHERE
    `time_left` >= '2016-02-01 12:00:00'
AND `time_left` < '2016-02-01 17:00:00'
GROUP BY
    timeslice

SQL Fiddle

Test Database:

CREATE TABLE `tbl_student_log` (
`id`  int(5) UNSIGNED NOT NULL AUTO_INCREMENT ,
`student_id`  int(5) NOT NULL ,
`time_left`  datetime NULL ,
PRIMARY KEY (`id`)
);

INSERT INTO `tbl_student_log` VALUES (1, 2, '2016-02-01 12:06:24');
INSERT INTO `tbl_student_log` VALUES (2, 4, '2016-02-01 12:13:42');
INSERT INTO `tbl_student_log` VALUES (3, 5, '2016-02-01 12:14:01');
INSERT INTO `tbl_student_log` VALUES (4, 8, '2016-02-01 14:07:25');

SQL

SELECT
    FROM_UNIXTIME(
        CEILING(
            UNIX_TIMESTAMP(`time_left`) / 600
        ) * 600
    ) AS timeslice,
    COUNT(*) AS mycount
FROM
    tbl_student_log
WHERE
    `time_left` >= '2016-02-01 12:00:00'
AND `time_left` < '2016-02-01 17:00:00'
GROUP BY
    timeslice

Output from above data is:

12:10 = 1  
12:20 = 2  
14:10 = 1

Actual output I need is:

12:00 = 0  
12:10 = 1  
12:20 = 2  
12:30 = 0  
12:40 = 0  
...  
14:10 = 1  
...  
17:00 = 0

With all times between dates returning a value

Upvotes: 1

Views: 2795

Answers (2)

Monty Khanna
Monty Khanna

Reputation: 1120

Ohh... Man, your question was difficult, After spending 5 hours on this query. I found solution using query.

Steps :

1.) you have to create procedure. Click here

2.) Download it and import "generate_series.sql" in your table.
3.) You have to run two queries :
    3.1) 1st you have to call procedure.
    3.2) 2nd you have to write another query.

After importing generate_series.sql in your table, you have to write query like this : 

CALL generate_series_date_minute('2016-02-01 11:50', '2016-02-01 17:00:00', 10);

SELECT (s.series + interval 10 MINUTE) AS timeslice, count(t.time_left) AS mycount
FROM series_tmp AS s LEFT JOIN tbl_student_log AS t
ON true
AND t.time_left > s.series
AND t.time_left <= (s.series + interval 10 MINUTE)
GROUP BY s.series limit 31

Note : if you want to change interval from 10 to 15 or 20, or any then you have to change in your query and procedure, You also have to write from and to date in procedure. You can even change procedure (See documentation)

Example : generate_series_date_minute(from, to, interval);

OUTPUT : 

timeslice                   mycount
2016-02-01 12:00:00           0
2016-02-01 12:10:00           1
2016-02-01 12:20:00           2
2016-02-01 12:30:00           0
2016-02-01 12:40:00           0
2016-02-01 12:50:00           0
2016-02-01 13:00:00           0
2016-02-01 13:10:00           0
2016-02-01 13:20:00           0
2016-02-01 13:30:00           0
2016-02-01 13:40:00           0
2016-02-01 13:50:00           0
2016-02-01 14:00:00           0
2016-02-01 14:10:00           1
2016-02-01 14:20:00           0
2016-02-01 14:30:00           0 
2016-02-01 14:40:00           0
2016-02-01 14:50:00           0
2016-02-01 15:00:00           0
2016-02-01 15:10:00           0
2016-02-01 15:20:00           0
2016-02-01 15:30:00           0
2016-02-01 15:40:00           0
2016-02-01 15:50:00           0
2016-02-01 16:00:00           0
2016-02-01 16:10:00           0
2016-02-01 16:20:00           0
2016-02-01 16:30:00           0
2016-02-01 16:40:00           0
2016-02-01 16:50:00           0
2016-02-01 17:00:00           0

Upvotes: 1

Lee
Lee

Reputation: 149

I have managed to resolve my query using PHP as well as MySQL as I haven't been able to find a pure MySQL solution.

First I setup an array of the times using the time interval as the key and setting a default value to 0

$times       = array();
$start_time  = "2016-02-01 12:00:00";
$end_time    = "2016-02-01 17:00:00";
$interval    = "+10 minutes";

$current = $start_time;
while( strtotime($current) <= strtotime($end_time) )
{
    $times[date('H:i', strtotime($current))] = 0;
    $current = date("Y-m-d H:i", strtotime($interval, strtotime($current)));
}

Then using the result from my query I updated the array with the values to the fields that were not 0

foreach($result as $row)
{
    $times[date('H:i', strtotime($row['timeslice']))] = $row['mycount'];
}

Not sure if anyone can come up with a pure MySQL solution.

Upvotes: 1

Related Questions