title: SQL solution to overlapping timeframes
body: <p>I have a group of machines. They run and stop occasionally. These run times are recorded into a postgres table automatically with timestamps <code>starttime</code> and <code>endtime</code>.</p>
  
  <p>I need to find the run time per machine in a 6 hour period. This is what I have so far: </p>
  
  <pre><code>SELECT machine, SUM(EXTRACT(EPOCH FROM (endtime - starttime)))
  FROM table
  WHERE 
        starttime &gt;= '2016-01-27 12:00:00'
        AND starttime &lt;= '2016-01-27 18:00:00'
  GROUP BY machine
  ORDER BY machine
  </code></pre>
  
  <p>So this works, I get the run time in seconds by machine over that time period. BUT it has a flaw - any run times that started before 12:00 do not get counted. And any run times that started in my time frame but don't end until after it have time counted that shouldn't be there.</p>
  
  <p>Is there a solution to ONLY extract the time that is inside the time frame? My initial thought would be to select all rows where:</p>
  
  <pre><code>endtime &gt;= '2016-01-27 12:00:00'
  </code></pre>
  
  <p>and somehow, in memory, set all the start times at <code>'2016-01-27 12:00:00'</code> 
  where the start time is earlier than that and:</p>
  
  <pre><code>starttime &lt;='2016-01-27 18:00:00'
  </code></pre>
  
  <p>and, again in memory without updating the database, set all end times to <code>'2016-01-27 18:00:00'</code> where the end time is later than that. and then run the extraction/summation query.</p>
  
  <p>But I'm struggling on how to implement something like this. I have a working solution in Java/Python this data is returned to, but they are iterative and take more time than I'd like. I'd really like to find an SQL solution to this if possible.</p>
  
  <p>Edit: To clarify, I need to count ALL run time that <strong>occurs within the time frame</strong> - if a run starts before the time frame only the portion of that run that occurs after the time frame should be counted.</p>