title: mysql open hours past midnight
body: <p>I'm building a api where i have a list of drinking bars that are open past midnight.
  I'm having problems finding out if they are currently open or closed.</p>
  
  <p>Lets say for example that one of these bars has these opening hours:</p>
  
  <pre><code>Monday 
  open time = 20:00
  closed time = 05:00
  
  Tuesday 
  open time = 20:00
  closed time = 04:00
  </code></pre>
  
  <p>If I want to know if a bar is currently open and the current time 04:00 (monday going on tuesday), this would result in that the current bar is closed because its tuesday and the closed time = 04:00</p>
  
  <p>But what the actual result should be is that the bar is open until 05:00 (monday going on tuesday)</p>
  
  <p>I have a list of 20+ bars where i need to know if they are currenly open or closed.
  Can this be accomplished in mysql or php? how should I setup my db table?</p>
  
  <p>I have this as my current setup</p>
  
  <p>Table Name : openhours
  Fields : </p>
  
  <pre><code> id          int(11)
   bar_id      int(11)
   open_time   time
   close_time  time
   day         tinyint(1)
  </code></pre>
  
  <p>table records:</p>
  
  <pre><code> id: 1
   bar_id: 1
   open_time: 20:00:00
   closed_time: 05:00:00
   day: 0
  
   id: 2
   bar_id: 1
   open_time: 20:00:00
   closed_time: 04:00:00
   day: 1
  </code></pre>
  
  <p>this query works fine for open days that dont go past midnight</p>
  
  <pre><code>SELECT `open_time`, `closed_time`, IF(CURTIME() BETWEEN `open_time` 
  AND `closed_time`,'open','closed') AS `status` FROM `openhours` 
  WHERE `day` = DATE_FORMAT(NOW(), '%w')
  </code></pre>
  
  <p>But how do i go about the past midnight issue?</p>
  
  <p>it tried something like this but it is not quite right</p>
  
  <pre><code>  SELECT open_time, close_time, day, (CASE WHEN ((open_time &lt;= close_time 
    AND open_time &lt;= CURTIME() AND close_time &gt;= CURTIME()) OR (open_time &gt;= close_time 
    AND (CURTIME() &lt;= close_time OR CURTIME() &gt;= open_time))) THEN 'open' ELSE 'closed' 
    END) AS status FROM openhours WHERE bar_id = 2 and CASE WHEN (day = WEEKDAY(NOW())    
    AND (CURTIME() &lt; open_time)) THEN CASE WHEN (day = (WEEKDAY(NOW()) - 1) 
    AND (CURTIME() &lt; close_time)) THEN day = (WEEKDAY(NOW()) - 1) 
    ELSE day = WEEKDAY(NOW()) END ELSE day = WEEKDAY(NOW()) END
  </code></pre>
  
  <p>Thanks in advance!</p>