CODEWITHSUNDEEP
phpmysqlsql
Filip
Filip

Reputation: 346

Mysql query logic (course before, after and between)

I have a course with many columns, but only three of them are required for this question:

----------------------------------------
| start_date | start_time  | end_time  |
----------------------------------------
| 2018-09-12 | 09:30       | 11:30     |
----------------------------------------

I need a query that includes courses with this criteria:

The first two ones I managed to write the query for, but the third criteria was now requested. So far I have this query (pseudo mysql query)

SELECT * 
FROM courses 
WHERE start_date = today AND 
    ((start_time >= now-30min AND start_time <= now+30min) OR 
     (end_time >= now-30min AND end_time <= now+30min))

Question is, how to write a query to satisfy all three requirements... I am blowing my mind for 1 hour and could not make it work, something is not working in my head.

Thanks.

Upvotes: 1

Views: 58

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Assuming courses do not go over the midnight boundary:

select c.*
from courses c
where start_date = curdate() and
      (start_time between curtime() - interval 30 minute and curtime() - interval 30 minute or
       end_time between curtime() - interval 30 minute and curtime() - interval 30 minute or
       ( start_time < curtime() and end_time > curtime() )
      )

The last condition is simply that the course started in the past and will end in the future.

Upvotes: 3

Related Questions