title: Efficient SELECT from table of boolean changes
body: <p>I'm trying to figure out an efficient query for a project I'm working on.</p>
  
  <p>We're recording a switch state into a table, each time it changes, a row is added with the new value (0 or 1).</p>
  
  <p>Here's a <em>simplified</em> structure of the table:</p>
  
  <pre><code> day | hour | state 
  -----+------+-------
    10 |    1 |     1  # day 10
    10 |    6 |     0 
    10 |   21 |     1 
  
    11 |    3 |     0  # day 11
    11 |    6 |     1
  
    13 |   13 |     0  # day 13
  ....
  </code></pre>
  
  <p>Now we need to make a daily overview, something like this:</p>
  
  <pre><code>Day 11 : Switch was on during 0-3, 6-24
  </code></pre>
  
  <p><br></p>
  
  <p><code>SELECT * FROM log WHERE day = 11</code> will give us only [3,0] and [6,1]. From those we can guess that it started ON and ended ON, but how about day 12?</p>
  
  <p><code>SELECT * FROM log WHERE day = 12</code> gives nothing, obviously - there's no clue to guess from.</p>
  
  <p>What is an efficient and reliable way to get the starting and ending state for a given day? Something like <strong>"Select one entry before day 12 and one after day 12"</strong>?</p>