在MySql中根据工作日和特定时间选择记录

Question

I need to filter out specific events based on specific criteria, e.g.

• [x] Mo
  • [x] 12:00
  • [  ] 14:00
• [x] Tue
  • [  ] 12:00
  • [x] 15:30
• [x] Fri
  • [  ] 10:00
  • [  ] 11:30
  • [x] 17:15
  • [x] 18:00

which gives the following array in php:

array(
   0 => [[12, 00]],
   1 => [[15, 30]],
   4 => [[17, 15], [18, 00]]
)

The final array may be adjusted to the needs, however now I want to select from schedule_event table which has starts_at property that stores datetime.

Question is, is it possible to select specific events, so e.g. Monday will return those starting on 12:00 (Tuesday shouldn't), Friday will return 17:15 and 18:00.

So far I had this:

... WEEKDAY(se.startsAt) IN (:weekDays) ...

And :weekDays was simply array_keys of the given array. Now, I don't know how to actually include times now.

Answer 1

You have to create pairs of weekday - hour [- minute] conditions to achieve the expected outcome:

select ...
from ...
where WEEKDAY(se.startsAt)=0 and hour(se.startsAt)=12
   or WEEKDAY(se.startsAt)=1 and hour(se.startsAt)=15 and minute(se.startsAt)=30
   ...

Instead of using hour and minute functiond, you can also use a single date_format() call, but I'll leave that to you.