title: In SQL, compute time differences between start & end timestamps, across multiple overlapping converations
body: <p>Struggling to come up with a proper title for this post. We have data on conversations that a person has had with many users. Each row in our table has the startTime &amp; endTime for one conversation between this person and a user, that looks like this:</p>
  <pre><code>    with start_end_table as (
          select TIMESTAMP(&quot;2007-12-31 12:00:00+00&quot;) as startTime, TIMESTAMP(&quot;2007-12-31 12:05:00+00&quot;) as endTime 
          union all 
          select TIMESTAMP(&quot;2007-12-31 12:03:00+00&quot;) as startTime, TIMESTAMP(&quot;2007-12-31 12:17:00+00&quot;) as endTime
          union all
          select TIMESTAMP(&quot;2007-12-31 12:07:00+00&quot;) as startTime, TIMESTAMP(&quot;2007-12-31 12:15:00+00&quot;) as endTime
          union all
          select TIMESTAMP(&quot;2007-12-31 12:24:00+00&quot;) as startTime, TIMESTAMP(&quot;2007-12-31 12:31:00+00&quot;) as endTime
          union all
          select TIMESTAMP(&quot;2007-12-31 12:29:00+00&quot;) as startTime, TIMESTAMP(&quot;2007-12-31 12:36:00+00&quot;) as endTime
          union all
          select TIMESTAMP(&quot;2007-12-31 12:41:00+00&quot;) as startTime, TIMESTAMP(&quot;2007-12-31 12:46:00+00&quot;) as endTime
      )
      
      select
          *,
          timestamp_diff(endTime, startTime, minute) as diffTime
      from start_end_table
  </code></pre>
  <p><a href="https://i.stack.imgur.com/53Y7il.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/53Y7il.png" alt="enter image description here" /></a></p>
  <p>These chats are currently ordered sequentially by their start times. If we visualized these chats in a spreadsheet, we see that some of these chats overlap (each column represents 1 minute):</p>
  <p><a href="https://i.stack.imgur.com/VHDAxl.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/VHDAxl.png" alt="enter image description here" /></a></p>
  <p>As you can see, there is some overlap between the first 3 convos, as well as overlap between convos 4 and 5. Although the sum of all 6 chat times is 5 + 14 + 8 + 7 + 7 + 5 = 46 minutes (from 12:00 to 12:46), the person actually only spent 34 minutes chatting with users, as 12 minutes (7 from 12:17 to 12:24, and 5 from 12:36 to 12:41) were spent not chatting.</p>
  <p>Is it possible to compute this 34 minutes from the SQL table <code>start_end_table</code> that we have? I am struggling to come up with a solution that works for this.</p>