title: Simple temporal view?
body: <p>How can I write a query to compute the end date per ID in postgres? I am able to do this in-memory with Python, but I would rather keep it simple and just create a view.</p>
  
  <p>My table appends any new combination of system_1_id and system_2_id along with the date of the file the data was from (I am reading a snapshot mapping file which is sent a few times per week). It looks like this:</p>
  
  <pre><code>system_1_id   system_2_id  start_date  is_current
  123456         05236       2016-06-01    False
  123456         98899       2017-01-03    False
  123456         05236       2017-04-15    True
  </code></pre>
  
  <p>To:</p>
  
  <pre><code>system_1_id   system_2_id   start_date    end_date
  123456            05236     2016-06-01   2017-01-02
  123456            98899     2017-01-03   2017-04-14
  123456            05236     2017-04-15   
  </code></pre>
  
  <ul>
  <li>Note that there can only be one system_2_id assigned to a system_1_id at a time, but they can be recycled and even reassigned at a later date.</li>
  <li>The end date is simply just 1 day less than the next row date for the same ID</li>
  </ul>
  
  <p>My goal is eventually to be able to join other tables to the data and pull the accurate ids per date:</p>
  
  <p><code>where t1.system_2_id = t2.system_2_id and t1.report_date &gt;= t2.start_date and t1.report_date &lt;= t2.end_date</code>  </p>
  
  <p>A simple temporal table without worrying about triggers or rules or using an extension.</p>