CODEWITHSUNDEEP
sqlgoogle-bigquerytimestampdiff
Jeff Henry
Jeff Henry

Reputation: 19

Calculate TIMESTAMP_DIFF between two columns in BigQuery

I'm very new to SQL and am working with a large dataset (over 300,000 rows of data) and need to calculate the time elapsed between Column A (start time) and Column B (end time) for each row of data.

So far, I've been able to figure out how to find the time elapsed for a given row with TIMESTAMP_DIFF, using the following syntax:

SELECT 
TIMESTAMP "2020-06-06 12:04:48 UTC" as end_time,
  TIMESTAMP "2020-06-06 08:09:09 UTC" as start_time,
  TIMESTAMP_DIFF(TIMESTAMP "2020-06-06 12:04:48 UTC",
    TIMESTAMP "2020-06-06 08:09:09 UTC", MINUTE) AS minutes

Since it would be unrealistic to do this for every row of data, is there any way to apply this to the entirety of the two columns? Or is there some other method of subtracting the values in two columns that contain timestamp data?

Upvotes: 0

Views: 1561

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

SELECT TIMESTAMP_DIFF(end_time, start_time, MINUTE) AS minutes
FROM your_table

Upvotes: 1

Related Questions