All geek questions in one place

Finding Free Times in MySQL

I have a table as follows:

mysql> DESCRIBE student_lectures; +------------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | course_module_id | int(11) | YES | MUL | NULL | | | day | int(11) | YES | | NULL | | | start | datetime | YES | | NULL | | | end | datetime | YES | | NULL | | | cancelled_at | datetime | YES | | NULL | | | lecture_type_id | int(11) | YES | | NULL | | | lecture_id | int(11) | YES | | NULL | | | student_id | int(11) | YES | | NULL | | | created_at | datetime | YES | | NULL | | | updated_at | datetime | YES | | NULL | | +------------------+----------+------+-----+---------+----------------+

I essentially want to find a time when the lecture does not happen - so for this I think the request is to group the overlapping lectures together (for example, 9 AM and 10 AM, 11-11 lectures will be shown as a 9 AM lecture 11 am). There may be more than two back-to-back lectures.

Currently I got this:

 SELECT l.start, l2.end FROM student_lectures l LEFT JOIN student_lectures l2 ON ( l2.start = l.end ) WHERE l.student_id = 1 AND l.start >= '2010-04-26 09:00:00' AND l.end <= '2010-04-30 19:00:00' AND l2.end IS NOT NULL AND l2.end != l.start GROUP BY l.start, l2.end ORDER BY l.start, l2.start

What returns:

 +---------------------+---------------------+ | start | end | +---------------------+---------------------+ | 2010-04-26 09:00:00 | 2010-04-26 11:00:00 | | 2010-04-26 10:00:00 | 2010-04-26 12:00:00 | | 2010-04-26 10:00:00 | 2010-04-26 13:00:00 | | 2010-04-26 13:15:00 | 2010-04-26 16:15:00 | | 2010-04-26 14:15:00 | 2010-04-26 16:15:00 | | 2010-04-26 15:15:00 | 2010-04-26 17:15:00 | | 2010-04-26 16:15:00 | 2010-04-26 18:15:00 | ...etc...

The result I am looking for from this will be as follows:

 +---------------------+---------------------+ | start | end | +---------------------+---------------------+ | 2010-04-26 09:00:00 | 2010-04-26 13:00:00 | | 2010-04-26 13:15:00 | 2010-04-26 18:15:00 |

Any help appreciated, thanks!

+4
source share
2 answers

ax came up with the best answer I've seen so far - http://explainextended.com/2009/06/13/flattening-timespans-mysql/

0
source

Something like this should work.

SELECT l2.start, l.end FROM student_lectures l LEFT JOIN student_lectures l2 ON l2.end BETWEEN l.start AND l.end WHERE l.start BETWEEN '2010-04-26 00:00:00' AND '2010-04- 26 23:59:59 'GROUP BY l.start ORDER BY l.start

+1
source

Source: https://habr.com/ru/post/1309261/

More articles:


All Articles