Geek Answers Handbook

How to return the closest time intervals grouped by an entity, but only for one nearest available day, and not for every available day?

I have a table that stores available meetings for the teacher, for hours, with complete freedom to add an unlimited number of slots per day per teacher (until the slots overlap). An example of a simplified structure:

CREATE TABLE time_slots ( id int(10) unsigned NOT NULL AUTO_INCREMENT, teacher_id mediumint(8) unsigned NOT NULL, slot bigint(20) unsigned NOT NULL DEFAULT '0', );

The slot column stores the timestamp of the slot.

How can I show all available slots for the first available day for each teacher? Please note: show all time intervals of a given day and cannot show more than one day for the same teacher.

Sample data:

PS: using datetime just to make reading more understandable.

 +----+------------+------------------+ | id | teacher_id | slot | +----+------------+------------------+ | 1 | 1 | 2013-04-10 08:00 | | 2 | 1 | 2013-04-10 09:00 | | 3 | 1 | 2013-04-10 09:30 | | 4 | 1 | 2013-04-11 08:00 | | 5 | 1 | 2013-04-11 09:00 | | 6 | 1 | 2013-04-11 10:30 | | 7 | 2 | 2013-04-12 07:00 | | 8 | 2 | 2013-04-12 09:00 | | 9 | 2 | 2013-04-14 08:00 | +----+------------+------------------+

Expected Result:

Assuming the search is in progress : 2013-04-10 08:30 , the returned results should be:

 +----+------------+------------------+ | id | teacher_id | slot | +----+------------+------------------+ | 2 | 1 | 2013-04-10 09:00 | | 3 | 1 | 2013-04-10 09:30 | | 7 | 2 | 2013-04-12 07:00 | | 8 | 2 | 2013-04-12 09:00 | +----+------------+------------------+
  • Do not show ID 1, since it has already passed 08:00 from the 10th day / April.
  • Do not show id 4, 5, 6, because we found that the closest available slots for teacher = 1 are id 2 and 3.
  • Do not show id 9, because for teacher = 2, the closest available slots are 7 and 8, so do not return another day.

What i tried

It’s hard for me to find a request, I just came up with this basic request, but it does not make assumptions about receiving only the 1st available day, and, of course, it does not return all available slots for the given day, It simply returns 1 slot to the teacher:

 SELECT id, teacher_id, FROM_UNIXTIME(slot) FROM time_slots WHERE slot >= [Actual timestamp] GROUP BY DATE(FROM_UNIXTIME(slot)) ORDER BY slot, teacher_id ASC

Note. I use FROM_UNIXTIME here for debugging purposes only, of course, I will optimize indexes, etc. later.

+4
source share
2 answers

First you need a request to get the next day for each teacher, as this will be different for each teacher. Some psuedo-ish SQL do this here:

 SELECT MIN(DATE(slot)) as closestDay, teacher_id FROM time_slots WHERE slot >= NOW() GROUP BY teacher_id

Then grab this query and use the results to show all available slots for each day.

 SELECT id, time_slots.teacher_id, FROM_UNIXTIMESTAMP(slot) FROM time_slots JOIN ( SELECT MIN(DATE(slot)) as closestDay, teacher_id FROM time_slots WHERE slot >= NOW() GROUP BY teacher_id ) a ON a.teacher_id = time_slots.teacher_id AND DATE(time_slots.slot) = closestDay WHERE time_slots.slot >= NOW()

It has clearly not been tested, but should give you a general idea.

+2
source

No connection required:

 drop table if exists time_slots; CREATE TABLE time_slots ( id int(10) unsigned NOT NULL AUTO_INCREMENT primary key, teacher_id mediumint(8) unsigned NOT NULL, slot datetime ); insert into time_slots values ('1','1','2013-04-10 08:00'), ('2','1','2013-04-10 09:00'), ('3','1','2013-04-10 09:30'), ('4','1','2013-04-11 08:00'), ('5','1','2013-04-11 09:00'), ('6','1','2013-04-11 10:30'), ('7','2','2013-04-12 07:00'), ('8','2','2013-04-12 09:00'), ('9','2','2013-04-14 08:00'); select * from time_slots where (teacher_id, date(slot)) in (select teacher_id, date(min(slot)) from time_slots where slot >= '2013-04-10 08:30' group by teacher_id) having slot >= '2013-04-10 08:30' ;
+1
source

More articles:


All Articles