CODEWITHSUNDEEP
mysqldatetime
Aries
Aries

Reputation: 301

Query the Next Time Interval

Im trying to query the upper available "time range" from a mySQL table

+----+----------+---------+
| id | timefrom | timeto  | 
+----+----------+---------+
|  0 | 08:30    | 10:30   |
|  7 | 15:00    | 16:00   |
|  2 | 17:00    | 17:30   |
|  8 | 18:00    | 21:00   |
+----+----------+---------+

the query result would be the next available time range which is 10:30 to 15:00

edit1: id is not in sequence

thanks!

Upvotes: 2

Views: 190

Answers (2)

inhan
inhan

Reputation: 7470

I think I would use something like

SELECT
    t1.*,
    MIN(t2.`timefrom`) AS 'next (timefrom)',
    TIMEDIFF(MIN(t2.`timefrom`),t1.`timeto`) AS 'interval'
FROM `the_table` t1
LEFT OUTER JOIN `the_table` t2
ON t2.`timefrom` > t1.`timeto`
GROUP BY t1.`timefrom`,t1.`timeto`
#HAVING `next` IS NOT NULL

Uncommenting the last line is the same with using INNER JOIN instead of LEFT OUTER JOIN here. The reason I chose LOJ is because I wanted to see all records in the table, but it's up to you, of course.

Upvotes: 1

fthiella
fthiella

Reputation: 49049

I think you need this:

select t1.`timeto`, min(t2.`timefrom`)
from
  yourtable t1 inner join yourtable t2
  on t1.`timeto`<t2.`timefrom`
group by t1.`timeto`
having
  not exists (select null from yourtable t3
              where t1.`timeto`<t3.`timeto`
              and min(t2.`timefrom`)>t3.`timefrom`)

(this will work only if intervals don't overlap)

Upvotes: 2

Related Questions