title: Postgres 9.x Joining on Overlaping Date & Time Range
body: <p>I have several tables that have a <code>TIMESTAMP WITH TIME ZONE</code> column along with some additional information. I need to be able to join these tables such that all of the information on each row is "valid" as of a given date and time window (labeled <code>begin</code> and <code>end</code> in the desired result set).</p>
  
  <h2>My current approach:</h2>
  
  <p><strong>Option #1</strong></p>
  
  <ol>
  <li>Create a unique list of times.</li>
  <li>Convert each point in time into a window of time for the unique list (the "valid" window of time) and for each original table. [<code>LEAD(...) OVER (...)</code>]</li>
  <li>Join the original tables to the unique list of times.</li>
  </ol>
  
  <p><strong>Option #2</strong></p>
  
  <ol>
  <li>Convert each point in time (<code>TIMESTAMP WITH TIME ZONE</code>) into a window of time for each table. [<code>LEAD(...) OVER (...)</code>]</li>
  <li>Join the tables where windows overlap.</li>
  <li>Return <code>GREATEST(foo.start, bar.start)</code> &amp; <code>LEAST(foo.stop, bar.stop)</code> from each window to find true "valid" window.</li>
  </ol>
  
  <h2>For example:</h2>
  
  <p><strong>Table: foo</strong></p>
  
  <pre><code>  fooid  |  description  |       datetime
  ---------|---------------|-----------------------
      1    |   Varsion 1   |  2010-01-01 00:00:00  
      2    |   Varsion 2   |  2010-07-01 00:00:00 
  </code></pre>
  
  <p><strong>Table: bar</strong></p>
  
  <pre><code>  barid  |  fooid  |  description  |       datetime
  ---------|---------|---------------|-----------------------
      1    |    1    |   Varsion A   |  2010-01-01 00:00:00
      2    |    1    |   Varsion B   |  2010-02-01 00:00:00
      3    |    1    |   Varsion C   |  2010-03-01 00:00:00
      4    |    1    |   Varsion D   |  2010-04-01 00:00:00
      5    |    1    |   Varsion E   |  2010-05-01 00:00:00
      6    |    1    |   Varsion F   |  2010-06-01 00:00:00
      7    |    2    |   Varsion A   |  2010-07-01 00:00:00
      8    |    2    |   Varsion B   |  2010-08-01 00:00:00
      9    |    2    |   Varsion C   |  2010-09-01 00:00:00
      10   |    2    |   Varsion D   |  2010-10-01 00:00:00
      11   |    2    |   Varsion E   |  2010-11-01 00:00:00
      12   |    2    |   Varsion F   |  2010-12-01 00:00:00
  </code></pre>
  
  <p><strong>Simplified Desired Results</strong></p>
  
  <pre><code>        begin          |          end          |  fooid  |   foo_desc    |       foostart        |        foostop        |  barid  |   bar_desc   |       foostart        |        foostop        
  -----------------------|-----------------------|---------|---------------|-----------------------|-----------------------|---------|--------------|-----------------------|-----------------------
           ...           |         ...           |   ...   |      ...      |         ...           |         ...           |   ...   |     ...      |         ...           |         ...           
    2010-05-01 00:00:00  |  2010-06-01 00:00:00  |    1    |   Varsion 1   |  2010-01-01 00:00:00  |  2010-07-01 00:00:00  |    5    |  Varsion E   |  2010-05-01 00:00:00  |  2010-06-01 00:00:00
    2010-06-01 00:00:00  |  2010-07-01 00:00:00  |    1    |   Varsion 1   |  2010-01-01 00:00:00  |  2010-07-01 00:00:00  |    6    |  Varsion F   |  2010-06-01 00:00:00  |        infinity
    2010-07-01 00:00:00  |  2010-08-01 00:00:00  |    2    |   Varsion 2   |  2010-07-01 00:00:00  |       infinity        |    7    |  Varsion A   |  2010-07-01 00:00:00  |  2010-08-01 00:00:00
    2010-08-01 00:00:00  |  2010-09-01 00:00:00  |    2    |   Varsion 2   |  2010-07-01 00:00:00  |       infinity        |    8    |  Varsion B   |  2010-08-01 00:00:00  |  2010-09-01 00:00:00
           ...           |         ...           |   ...   |      ...      |         ...           |         ...           |   ...   |     ...      |         ...           |         ...           
  </code></pre>
  
  <hr>
  
  <h2>My Question:</h2>
  
  <p>What is the best way to achieve this? I have created a <a href="http://sqlfiddle.com/#!1/57254/5" rel="nofollow">fiddle</a> showing two different solutions, and I would like to hear ideas on each as well as possible solutions not in the fiddle.</p>
  
  <h2>Update #1:</h2>
  
  <p>In the example, there are only two tables that need to be joined... however, in some cases I may need to join several tables 3, 4, or more.</p>
  
  <h2>Update #2:</h2>
  
  <p>With <strong>Option #1</strong>, my problem is that when I have large result sets I find that the initial sub query can be large and postgres can't use an index. This causes a large performance hit. On the other hand, I find it to be the most accurate as I can <code>LEFT OUTER JOIN</code> against it and get pertinent <code>NULL</code> data back.</p>
  
  <p>With <strong>Option #2</strong>, the query planner is able to use the indexes on the <code>TIMESTAMP WITH TIME ZONE</code> columns; however, joining more then two tables becomes more complex in the <code>FROM</code> clause. I can move the <code>(table1.start, table1.stop) OVERLAPS (table2.start, table2.stop)</code> into the <code>WHERE</code> clause but then I loose the pertinent <code>NULL</code> data.</p>
  
  <p>All of this makes me wonder if there is a better way...</p>