title: Storing Time Ranges in MySQL and Determine if Current Time is within Time Range
body: <p>I have a database table storing opening hours of a shop as a time range in TIME format. Eg if the shop's opening hours are '9am-5pm', there will be 2 columns 'hours_open' and 'hours_close' where I store 9:00 in 'hours_open' and 17:00 in 'hours_close'. To determine if the shop is currently open or closed, I will run the query:</p>
  
  <pre><code>SELECT * 
    FROM shop 
   WHERE CAST(NOW() AS TIME) BETWEEN hours_open AND hours_close;
  </code></pre>
  
  <p>What I need to do now, is to accommodate shops with opening hours that go past midnight, like 9pm - 3am. Additionally, the shops can have different opening hours on different days, eg: Mon-Thurs: 9pm-3am, Fri-Sun: 9pm-5am.</p>
  
  <p>Now I am stumped. How should I proceed? I can have a pair of 'hours_open' and 'hours_close' for each day, eg: 'mon_hours_open', 'mon_hours_close', 'tue_hours_open', 'tue_hours_close' ...</p>
  
  <p>But how do I handle opening hours that go past midnight like 9pm-5am?</p>
  
  <p>Any insights? I'm using PHP with Codeigniter framework if that helps. </p>