CODEWITHSUNDEEP
sqlgoogle-bigquery
Spencer Carnage
Spencer Carnage

Reputation: 2066

How do I get all timestamps associated with an ID and aggregate it them into total time elapsed?

I have a sample table:

id   timestamp 
1    2020-02-03T19:25:41.961Z   
1    2020-02-03T19:25:42.000Z   
1    2020-02-03T19:26:33.147Z   
2    2020-02-03T20:21:29.684Z
2    2020-02-03T20:21:29.705Z

In BigQuery, I want to group all of the columns with the same ID together, and then get the total elapsed time between all of the timestamp cols for that ID.

Upvotes: 0

Views: 41

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

You can use timestamp_diff():

select id, timestamp_diff(max(timestamp), min(timestamp), second) as diff_seconds
from t
group by id;

Upvotes: 2

Related Questions