I have a sql query to find the closest free time after a certain date. How can I get it to also search for a date before that date?

Question

This is a continuation of my previous question. Request to search for the closest free time interval in mysql - why does it not work?

Basically, I have a table:

id  |      start_time       |  duration
 1  |  2015-10-21 19:41:35  |    15
 2  |  2015-10-21 19:41:50  |    15
 3  |  2015-10-21 19:42:05  |    15
 4  |  2015-10-21 19:42:35  |    15
etc.

and contains the start_time event and its duration. I asked for help finding the closest time interval in which an event can be placed between existing events. @Richard came up with a great answer to https://stackoverflow.com/a/166649/ and suggested a query:

SELECT (a.start_time + INTERVAL a.duration SECOND) AS free_after FROM notes a
WHERE
NOT EXISTS ( SELECT 1 FROM notes b WHERE b.start_time
BETWEEN (a.start_time + INTERVAL a.duration SECOND) AND
(a.start_time + INTERVAL a.duration SECOND) + INTERVAL 15 SECOND - INTERVAL 1 MICROSECOND) AND
(a.start_time + INTERVAL a.duration SECOND) BETWEEN '2015-10-21 19:41:30' AND '2015-10-21 19:43:50'

which works great.

Now I was wondering if it was possible to find a suitable date not only between existing dates, but also immediately before them.

: begin_date 2015-10-21 16:00:00 end_date 2015-10-21 21:00:00. @Richard 2015-10-21 19:42:20. , 2015-10-21 19:41:20 , ?

Answer 1

date_sub order by, 1 .

:

SELECT date_sub(start_time, interval duration second) as free_before FROM `notes` where start_time>'2015-10-21 16:00:00' order by start_time asc limit 1

@Richard. , , :

select * from (SELECT date_sub(start_time, interval duration second) as free_times FROM `notes` where start_time>'2015-10-21 16:00:00' order by start_time asc limit 1) a
union
(SELECT (a.start_time + INTERVAL a.duration SECOND) AS free_times FROM notes a
WHERE
NOT EXISTS ( SELECT 1 FROM notes b WHERE b.start_time
BETWEEN (a.start_time + INTERVAL a.duration SECOND) AND
(a.start_time + INTERVAL a.duration SECOND) + INTERVAL 15 SECOND - INTERVAL 1 MICROSECOND) AND
(a.start_time + INTERVAL a.duration SECOND) BETWEEN '2015-10-21 19:41:30' AND '2015-10-21 19:43:50')

Edit:

. , , , ( -, )

10 →

SELECT date_sub(start_time, interval 10 second) as free_times FROM `notes` where start_time>'2015-10-21 16:00:00' order by start_time asc limit 1

15 →

SELECT date_sub(start_time, interval 15 second) as free_times FROM `notes` where start_time>'2015-10-21 16:00:00' order by start_time asc limit 1

.

Answer 2

?

:

select * from notes;
+----+---------------------+----------+
| id | start_time          | duration |
+----+---------------------+----------+
|  1 | 2015-11-17 10:10:10 |       15 |
|  2 | 2015-11-17 10:20:40 |       15 |
|  3 | 2015-11-17 10:30:00 |       15 |
+----+---------------------+----------+

:

select (start_time - interval 15 second) as earlier_date
    from notes
    where start_time > '2015-11-17 10:15:00' 
       AND start_time < '2015-11-17 10:25:00' 
    order by start_time 
    limit 1;

+---------------------+
| earlier_date        |
+---------------------+
| 2015-11-17 10:20:25 |
+---------------------+

: , ( ). as-is , .

Answer 3

Take the base table and insert a fake row, which is the minimum start time and subtracts 15 seconds.

So, instead of taking notes, use a subquery like this:

    SELECT 
            MIN(start_time) - INTERVAL 15 seconds AS start_time,
            0 AS duration
    FROM notes

    UNION ALL

    SELECT start_time, duration
    FROM notes