Geek Answers Handbook

Time loop in SQL Quires.

In my rails app, the app charts for each restaurant are currently opening and closing. how

Monday 5:00 AM to 11:45 PM
Tuesday 6:00 AM to 11:00 PM
Wednesday 9:00 AM to 8:00 PM
Thursday 11:00 AM to 11:45 PM
Friday 5:00 AM to 11:45 PM
Saturday 5:00 AM to 11:45 PM

I saved the above time in a database with a 24 hour database time format. It works great. like when I try to get information about the restaurant’s open time due to 24-hour time, I can request data from the database. But now my client wants the closing time to be 2:00 AM, 2:30 AM, as the restaurant closes definitely after midnight. How can I manage in SQL Quires? How can I check for a certain period of time a restaurant is open or not?

I am currently executing the following query:

SELECT 
COUNT(*)
FROM
`sechedules`
WHERE
`sechedules`.`sechedulable_id` = 16
    AND `sechedules`.`sechedulable_type` = 'Operation'
    AND (day = 'Wednesday')
    AND (opening_time <= '21:28:23'
    and closing_time >= '02:28:23'
    and is_closed = 0);

I can change the cycle of the day. It can start from 5:00 to 4:59. But how to manage in comparison with databases, I do not get the best solution.

+4
1

. . .

select weekday, 
       start, 
       stop,
       timediff(
           if (TIMESTAMPDIFF(MINUTE,start,stop)<0,stop+INTERVAL 24 HOUR, stop), 
           start) duration 
  from sechedules

, - 24 , , .

, , .

select count(*) cnt
 where sechedulable_id = 16
   and day = 'Wednesday'
   and start <= '21:28:23'
   and '21:28:23' <= if(TIMESTAMPDIFF(MINUTE,start,stop)<0,stop+INTERVAL 24 HOUR, stop)
+2

More articles:


All Articles