In SQL, compute time differences between start & end timestamps, across multiple overlapping converations

user_5573294 0
Viewed 28

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 & endTime for one conversation between this person and a user, that looks like this:

    with start_end_table as (
        select TIMESTAMP("2007-12-31 12:00:00+00") as startTime, TIMESTAMP("2007-12-31 12:05:00+00") as endTime 
        union all 
        select TIMESTAMP("2007-12-31 12:03:00+00") as startTime, TIMESTAMP("2007-12-31 12:17:00+00") as endTime
        union all
        select TIMESTAMP("2007-12-31 12:07:00+00") as startTime, TIMESTAMP("2007-12-31 12:15:00+00") as endTime
        union all
        select TIMESTAMP("2007-12-31 12:24:00+00") as startTime, TIMESTAMP("2007-12-31 12:31:00+00") as endTime
        union all
        select TIMESTAMP("2007-12-31 12:29:00+00") as startTime, TIMESTAMP("2007-12-31 12:36:00+00") as endTime
        union all
        select TIMESTAMP("2007-12-31 12:41:00+00") as startTime, TIMESTAMP("2007-12-31 12:46:00+00") as endTime
    )
    
    select
        *,
        timestamp_diff(endTime, startTime, minute) as diffTime
    from start_end_table

enter image description here

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):

enter image description here

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.

Is it possible to compute this 34 minutes from the SQL table start_end_table that we have? I am struggling to come up with a solution that works for this.

google-bigquery timestamp
1 Answers

Consider below approach - first split all time interval to minutes and then count distinct minutes in all intervals

select count(distinct minute_spent) total_minutes_spent
from (
  select minute_spent from start_end_table, 
  unnest(generate_timestamp_array(
    startTime, timestamp_sub(endTime, interval 1 minute), interval 1 minute
  )) minute_spent
)
user_52219440
Related