MySQL Query to find consecutive available times of variable lenth
- by Armaconn
I have an events table that has user_id, date ('2013-10-01'), time ('04:15:00'), and status_id; What I am looking to find is a solution similar to http://stackoverflow.com/questions/2665574/find-consecutive-rows-calculate-duration but I need I need two additional components:
1) Take date into consideration, so 10/1/2013 at 11:00 PM - 10/2/2013 at 3:00AM. Feel free to just put in a fake date range (like '2013-10-01' to '2013-10-31')
2) Limit output to only include when there are 4+ consecutive times (each event is 15 minutes and I want it to display minimum blocks of an hour, but would also like to be able to switch this restriction to 1.5 hours or some other duration if possible).
SUMMARY - Looking for a query that provides the start and end times for a set of events that have the same user_id, status_id, and are in a continuous series based on date and time. For which I can restrict results based on date range and minimum series duration.
So the output should have: user_id, date_start, time_start, date_end, time_end, status_id, duration
CREATE TABLE `events` (
`event_id` int(11) NOT NULL auto_increment COMMENT 'ID',
`user_id` int(11) NOT NULL,
`date` date NOT NULL,
`time` time NOT NULL,
`status_id` int(11) default NULL,
PRIMARY KEY (`event_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1568 ;
INSERT INTO `events` VALUES(1, 101, '2013-08-14', '23:00:00', 2);
INSERT INTO `events` VALUES(2, 101, '2013-08-14', '23:15:00', 2);
INSERT INTO `events` VALUES(3, 101, '2013-08-14', '23:30:00', 2);
INSERT INTO `events` VALUES(4, 101, '2013-08-14', '23:45:00', 2);
INSERT INTO `events` VALUES(5, 101, '2013-08-15', '00:00:00', 2);
INSERT INTO `events` VALUES(6, 101, '2013-08-15', '00:15:00', 1);
INSERT INTO `events` VALUES(7, 500, '2013-08-14', '23:45:00', 1);
INSERT INTO `events` VALUES(8, 500, '2013-08-15', '00:00:00', 1);
INSERT INTO `events` VALUES(9, 500, '2013-08-15', '00:15:00', 2);
INSERT INTO `events` VALUES(10, 500, '2013-08-15', '00:30:00', 2);
INSERT INTO `events` VALUES(11, 500, '2013-08-15', '00:45:00', 1);
Desired output
row |user_id | date_start | time_start | date_end | time_end | status_id | duration
1 |101 |'2013-08-14'| '23:00:00' |'2013-08-15'|'00:15:00'| 2 | 5
2 |101 |'2013-08-15'| '00:00:15' |'2013-08-15'|'00:30:00'| 1 | 1
3 |500 |'2013-08-14'| '00:23:45' |'2013-08-15'|'00:15:00'| 1 | 2
4 |500 |'2013-08-15'| '00:00:15' |'2013-08-15'|'00:45:00'| 2 | 2
5 |500 |'2013-08-15'| '00:00:45' |'2013-08-15'|'01:00:00'| 2 | 1
*except that rows 2 and 5 wouldn't appear if duration had to be greater than 30 minutes
Thanks for any help that you can provide! And please let me know if there is anything I can further clarify!!
