PHP Mysql Get Gap between time range

Question

Im kinda new here in stackoverflow. Anyway here is my problem, hope someone could help me on this. I have a table in mysql see below

Start Time         End Time         Date
 9:00               10:00           2016-02-26
 9:00               10:00           2016-02-25
 11:00              12:00           2016-02-25
 12:00              13:00           2016-02-25
 13:00              14:00           2016-02-25
 15:00              16:00           2016-02-25

what I want to get is the gap between those time with specific date. See below for my desired output:

Start Time         End Time        Date
 10:00               11:00         2016-02-25
 14:00               15:00         2016-02-25

Please if someone know the answer, it would be a big help. Thanks

ie you want the endtime-starttime ? – safin chacko Feb 25 '16 at 06:39 — safin chacko, Feb 25 '16 at 06:39

Giorgos Betsos · Accepted Answer · 2016-02-25T13:06:31.530

1

You can use the following query:

SELECT gap_start, gap_end
FROM (
  SELECT (SELECT EndTime
         FROM mytable AS t2
         WHERE t2.EndTime < t1.StartTime
         ORDER BY t2.EndTime DESC LIMIT 1) AS gap_start,
         StartTime AS gap_end
  FROM mytable AS t1) AS t
WHERE gap_start IS NOT NULL

The correlated subquery used will fetch the StartTime of the records that immediately precedes the current record. Here it is assumed that there is always a gap between consecutive records.

Demo here

Edit:

The query can be modified like as follows in order to handle the addition of a Date field:

SELECT gap_start, gap_end, `Date`
FROM (
  SELECT (SELECT EndTime
          FROM mytable AS t2
          WHERE t2.`Date` <= t1.`Date` AND t2.EndTime <= t1.StartTime
          ORDER BY t2.`Date` DESC, t2.EndTime DESC LIMIT 1) AS gap_start,
         StartTime AS gap_end,
         `Date`
  FROM mytable AS t1) AS t
WHERE gap_start IS NOT NULL AND gap_start < gap_end

Demo here

edited Feb 25 '16 at 13:06

answered Feb 25 '16 at 06:42

Giorgos Betsos

71,379
9
63
98

Thanks a lot bro. Works pretty well. you saved my day. Thanks for quick response. Have a nice day :)))))) – Martin Primicias Feb 25 '16 at 06:47
sorry bro but I have to unaccept the answer. Its might be simple solution for you guys but big problem for me. I found out that it keeps on showing the times which is less the StartTime. please look at revise example above for more details. Thanks much – Martin Primicias Feb 25 '16 at 12:42
Thanks again @GiorgosBetsos, I didnt knew that's how simple it is. thanks thanks thanks :) – Martin Primicias Feb 25 '16 at 13:45

PHP Mysql Get Gap between time range

1 Answers1