title: split a record into multiple records based on watched program category
body: <p>I recently started using postgres and I am coming from oracle background. Just curious to know if the query I have written can be implemented in a better way in postgres.</p>
  
  <p>Problem Details:</p>
  
  <p>I have two tables:</p>
  
  <ol>
  <li>usage_detail</li>
  <li>Program_info</li>
  </ol>
  
  <p>Usage_detail has information about any user watching a channel. For example user A had its session length for 1 hour 10 mins 0 seconds, starting at 1:15 PM today</p>
  
  <pre><code>User  start_time           end_time
  A     2016-10-31 13:15:00  2016-10-31 14:25:00
  </code></pre>
  
  <p>Program_info table contains scheduled program detail and corresponding category.</p>
  
  <p>For example:</p>
  
  <pre><code>Program_id program_category  week_day   start_time  end_time
           1 News              Monday     13:00       13:30
           2 Sports            Monday     13:30       14:30
  </code></pre>
  
  <p>The output I am looking for is:</p>
  
  <pre><code>User  program_category   start_time           duration (in seconds)
     A  News               2016-10-31 13:15:00       900
     A  Sports             2016-10-31 13:30:00      3300
  </code></pre>
  
  <p>My current approach:</p>
  
  <p>I divided the start_time and end_time duration into 30 minutes interval(because program category may change in every 30 mins). Like for the example I mentioned, I first created 3 records (from 1:15 pm to 1:30 pm, 1:30 pm to 2:00pm, 2:00 pm to 2:25 pm) then summed the duration based on program_category.</p>
  
  <p>I wrote a little less readable code, which generates multiple records out of one record dynamically without using postgres' array and unnest functionality. </p>
  
  <p>Can anyone suggest what could be the best way to approach this problem using either Array/unnest or any other functionality available in postgres? I am not looking for exact code, just the direction would do.</p>