two timestamp differences in seconds

Question

I have a BigQuery issue I am trying to learn/ work around. I need to get the elapsed time in seconds between a min observation and a max observation.

• date field = event_timestamp_utc - format = 2024-06-26T17:02:22.800977
• start observation = min(extract(time from event_timestamp_utc)) as start_ts
• end Observation = max(extract(time from event_timestamp_utc)) as end_ts

my current sql

select
   user_id
  ,(select value.string_value from unnest(event_params) where key = 'tms_app') as app
  ,concat(ga_session_id, (select value.string_value from unnest(event_params) where key = 'tms_app')) as pseudo_key
  ,extract(date FROM event_timestamp_utc) as Event_Date
  ,min(extract(time from event_timestamp_utc)) as start_ts
  ,max(extract(time from event_timestamp_utc)) as end_ts
  ,(max(extract(time from event_timestamp_utc)) - min(extract(time from event_timestamp_utc))) as usage_time
  

from `BigQ Location`
where event_date_cdt >= '2024-01-01'
group by jbh_user_id, app, ga_session_id, pseudo_key, extract(date FROM event_timestamp_utc)
order by Event_date, ga_session_id, start_ts

I have played with a few different options but cannot get them to work with my min/ max in the grouping to get the relevant start and stop by application. Any guidance is appreciated.

Answer 1

For differences between times, use TIME_DIFF, TIMESTAMP_DIFF or DATE_DIFF.

WITH tmp AS (
    select
        user_id
        ,(select value.string_value from unnest(event_params) where key = 'tms_app') as app
        ,concat(ga_session_id, (select value.string_value from unnest(event_params) where key = 'tms_app')) as pseudo_key
        ,extract(date FROM event_timestamp_utc) as Event_Date
        ,CAST(min(extract(time from event_timestamp_utc)) AS TIME) as start_ts
        ,CAST(max(extract(time from event_timestamp_utc)) AS TIME) as end_ts
    from
        `BigQ Location`
    where
        event_date_cdt >= '2024-01-01'
    group by
        user_id,
        2, 
        3, 
        4
    order by 
        Event_date, 
        ga_session_id, 
        start_ts
)
SELECT
    user_id,
    app,
    pseudo_key,
    Event_Date,
    TIME_DIFF(end_ts, start_ts, SECOND) AS usage_time
FROM
    tmp;

will work.

Example:

WITH tmp AS (
    SELECT
        TIME "15:30:00" as end_ts,
        TIME "14:35:00" as start_ts,
)
SELECT 
    TIME_DIFF(end_ts, start_ts, SECOND) as difference
FROM
    tmp;

Result: 3300 (seconds)

Here is the TIME_DIFF documentation.