Searching for the closest free time interval in mysql - why doesn't it work?

Question

I have a table called notes, and there I have three fields:

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.

id - field INT

start_time - field TIMESTAMP

duration - This is the INT field, which indicates the number of seconds, how much time each event takes.

I write SQL-query that retrieves 3 fields as input: duration, begin_timeand end_timeand I will return the field timestamp, which can only be new.

Based on a lot of questions, similar to mine, on StackOverflow (basically, this MySQL / PHP is to find the available time intervals ) I created a query:

SELECT (a.start_time + a.duration) AS free_after FROM notes a
WHERE NOT EXISTS ( SELECT 1 FROM notes b
WHERE b.start_time BETWEEN (a.start_time+a.duration) AND
(a.start_time+a.duration) + INTERVAL '$duration' SECOND) AND 
(a.start_time+a.duration) BETWEEN '$begin_time' AND '$end_time'

But when I run it like this:

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

, , , , :

     free_after 
2015-11-21 19:42:20

( :

 3  |  2015-10-21 19:42:05  |    15
 4  |  2015-10-21 19:42:35  |    15

15 ). , ?

==== EDIT:

show warnings, :

Truncated incorrect DOUBLE value: '2015-11-21 19:4...
Truncated incorrect DOUBLE value: '2015-11-21 19:4...
Truncated incorrect DOUBLE value: '2015-11-21 19:4...
Truncated incorrect DOUBLE value: '2015-11-21 19:4...
Incorrect datetime value: '0'

, , ?

===== 2

, @Richard ( btw!), :

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 ) AND
(a.start_time + INTERVAL a.duration SECOND) BETWEEN '2015-11-21 19:41:30' AND '2015-11-21 19:43:50'

( ):

2015-10-21 19:42:50

( ). 2015-10-21 19:42:20... - , ? show warnings - : (

Answer 1

BETWEEN . , 15 , 15 , , 30 , , , .

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'

(.. 1 )

:

+---------------------+
| free_after          |
+---------------------+
| 2015-10-21 19:42:20 |
| 2015-10-21 19:42:50 |
+---------------------+
2 rows in set (0.00 sec)

Answer 2

: TIMESTAMP INT.

. : /DATETIME MySQL?

:

, SQL- , , , , .

SELECT (a.start_time + a.duration) AS free_after FROM notes a;

, , :

+----------------+
| free_after     |
+----------------+
| 20151021194150 |
| 20151021194165 |
| 20151021194220 |
| 20151021190250 |
+----------------+

DATETIME TIMESTAMP. , INTERVAL <value> SECOND TIMESTAMP, .

mysql> SELECT (a.start_time + INTERVAL a.duration SECOND) AS free_after FROM notes a;
+---------------------+
| free_after          |
+---------------------+
| 2015-10-21 19:41:50 |
| 2015-10-21 19:42:05 |
| 2015-10-21 19:42:20 |
| 2015-10-21 19:02:50 |
+---------------------+
4 rows in set (0.00 sec)

. , .

, , . show warnings; mysql , " " " DOUBLE".

Answer 3

int . :

    SELECT notes.starttime+ INTERVAL notes.duration SECOND
FROM notes
WHERE notes.starttime+INTERVAL notes.duration SECOND<= '2015-11-21 19:41:30'
  AND NOT EXISTS (SELECT notes.id 
      FROM notes 
      WHERE notes.starttime <= '2015-11-21 19:43:50'
        AND notes.starttime+ INTERVAL notes.duration SECOND>='2015-11-21 19:43:50')
ORDER BY notes.starttime

Answer 4

LAG() ...:

• -. .
• -. ,

SQL:

SET @lag_time='0000-00-00 00:00:00';
SELECT TIMESTAMPADD( SECOND, duration, lastTime ) as fromTime,
       (diffTime - duration) freeSeconds,
       currTime toTime
FROM
  (SELECT @lag_time as LastTime, 
         IFNULL(TIMESTAMPDIFF(SECOND, @lag_time, start_time ),0) diffTime,
         @lag_time:=start_time currTime,
         duration       
  FROM notes) as TimeDiff
WHERE diffTime > duration
ORDER BY currTime;