title: Perform this hours of operation query in PostgreSQL
body: <p>I'm in the RoR stack and I had to write some actual SQL to complete this query for all records that are "open", meaning that the current time is within the specified hours of operation. In the <code>hours_of_operations</code> table two <code>integer</code> columns <code>opens_on</code> and <code>closes_on</code> store a weekday, and two <code>time</code> fields <code>opens_at</code> and <code>closes_at</code> store the respective time of the day.</p>
  
  <p>I made a query that compares the current date and time to the stored values but I'm wondering if there is a way to cast to some sort of date type and have PostgreSQL do the rest?</p>
  
  <p>The meat of the query is:</p>
  
  <pre><code>WHERE (
   (
  
   /* Opens in Future */
   (opens_on &gt; 5 OR (opens_on = 5 AND opens_at::time &gt; '2014-03-01 00:27:25.851655'))
   AND (
   (closes_on &lt; opens_on AND closes_on &gt; 5)
   OR ((closes_on = opens_on)
   AND (closes_at::time &lt; opens_at::time AND closes_at::time &gt; '2014-03-01 00:27:25.851655'))
   OR ((closes_on = 5)
   AND (closes_at::time &gt; '2014-03-01 00:27:25.851655' AND closes_at::time &lt; opens_at::time)))
   OR
  
   /* Opens in Past */
   (opens_on &lt; 5 OR (opens_on = 5 AND opens_at::time &lt; '2014-03-01 00:27:25.851655'))
   AND
   (closes_on &gt; 5)
   OR
   ((closes_on = 5)
   AND (closes_at::time &gt; '2014-03-01 00:27:25.851655'))
   OR (closes_on &lt; opens_on)
   OR ((closes_on = opens_on)
   AND (closes_at::time &lt; opens_at::time))
   )
  
   )
  </code></pre>
  
  <p>Th reason for such dense complexity is that an hour of operation may wrap around the end of the week, for example, starting at noon on Sunday and going through 6 AM Monday. Since I store values in UTC, there are many cases in which local time of the user could wrap in a very strange way. The query above ensures that you could enter ANY two times of the week and we compensate for the wrapping.</p>