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
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.
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