title: PostgreSQL - Create second by second View
body: <p>I have a table, called <code>tb_records</code> which is a record of occurences. The sample values are:</p>
  
  <pre><code>id  from        to
  1   01:00:00    01:00:03
  2   01:00:05    01:00:07
  3   01:00:50    01:00:51
  </code></pre>
  
  <p>What I would like to do is to generate the second by second sequence in PostgreSQL. So the result would be </p>
  
  <pre><code>id  from        to          time
  1   01:00:00    01:00:03    01:00:00
  1   01:00:00    01:00:03    01:00:01
  1   01:00:00    01:00:03    01:00:02
  1   01:00:00    01:00:03    01:00:03
  2   01:00:05    01:00:07    01:00:05
  2   01:00:05    01:00:07    01:00:06
  2   01:00:05    01:00:07    01:00:07
  3   01:00:50    01:00:51    01:00:50
  3   01:00:50    01:00:51    01:00:51
  </code></pre>
  
  <p>Usually, in Python or R, the <em>basic</em> algorithm I would choose (without using any library or package):</p>
  
  <ol>
  <li>Calculate the difference of time in seconds for each <code>id</code> (<code>diff</code>)</li>
  <li>For each id, replicate the rows by <code>diff</code></li>
  <li>Create the column <code>time</code> with initial values as column <code>from</code></li>
  <li>For each <code>id</code> and row, increment column <code>time</code> by one second</li>
  </ol>
  
  <p>I am confused as to how to do it in SQL, since I am accustomed to create those steps sequentially.</p>