title: Dealing with times and after midnight
body: <p>How would you deal with times and after midnight in PHP / MySQL?</p>
  
  <p>Use <code>INT</code> store minutes or use <code>TIME</code> type field?</p>
  
  <p>Consider the SQL query below:</p>
  
  <pre><code>SELECT * FROM opening_hours  
          WHERE week_day = WEEKDAY(NOW()) + 1 
                AND open_hour =&lt; date_format(now(),'%H:%i') 
                AND close_hour &gt;= date_format(now(),'%H:%i')
  </code></pre>
  
  <p><code>open_hour</code> / <code>close_hour</code> fields are <code>TIME</code> type field.</p>
  
  <p>Suppose that open_time is "18:00", close_time is "02:00", current time is "22:41". We have a separate DB record for the close_time (cause it's after midnight), but we will never get it in the result, because the close_time "02:00" is NOT greater than the current time, "22:41".</p>
  
  <p>Also if current time is "01:00", we will get the NEXT day values, because the weekday doesn't match.</p>
  
  <p><strong>So what is the solution?</strong></p>
  
  <p>Would you rather store these values in <code>INT</code> (minutes), so the php is able to process these values directly, without any conversion?</p>
  
  <p>For example...</p>
  
  <p>Current time:</p>
  
  <pre><code>// w = Day of the week, H = 24-hour format, i = Minutes
  $timearr = explode(':',date("w:H:i"));
  $currenttime = ($timearr[0]) * 1440 + $timearr[1] * 60 + $timearr[2] 
  </code></pre>
  
  <p>In the database, the stored open/close time values in minutes.</p>
  
  <p>Now suppose, that the current time is "Sun, 01:00" (first day of week), executing  the above coversion this value is 60; and the open/close times for last day of week (Saturday) are set to "17:00" and "02:00" (which is actually Sunday), stored in the DB as 9660 and 10200 (Saturday, 26:00). In this case, the above query will not find the record we need (Sat, 17:00, 02:00), because we probably don't have any open_time less than "02:00" (120). To solve this, we convert "Sun, 01:00" to "Sat, 25:00", by adding 7*1440 (a whole week) to the $currenttime, which will result 10140. Then DB query like this:        </p>
  
  <pre><code>SELECT open_time,clos_time FROM open_hours 
      WHERE (open_time &lt;= $currenttime 
           AND close_time &gt;= $currenttime)
           OR (open_time &lt;= $currenttime +10080
           AND close_time &gt;= $currenttime + 10080);
  </code></pre>
  
  <p>Or what is alternative and neater solution?</p>