How to calculate datetime interval-CodePudding

Wonder how to calculate de interval between this datetime. I've been trying Datetime_diff() function in SQL Bigqury but unsucessfuly.

started_at	ended_at
2020-04-26 17:45:00 UTC	2020-04-26 18:12:00 UTC
2020-04-17 17:08:00 UTC	2020-04-17 17:17:00 UTC
2020-04-01 17:54:00 UTC	2020-04-01 18:08:00 UTC

I would like to add a new column with the DURATION of this time travels.

CodePudding user response：

started_at and ended_at looks TIMESTAMP type, so use TIMESTAMP_DIFF() like below.

WITH sample_data AS (   
  SELECT TIMESTAMP '2020-04-26 17:45:00 UTC' started_at, TIMESTAMP '2020-04-26 18:12:00 UTC' ended_at UNION ALL
  SELECT '2020-04-17 17:08:00 UTC', '2020-04-17 17:17:00 UTC' UNION ALL
  SELECT '2020-04-01 17:54:00 UTC', '2020-04-01 18:08:00 UTC'
)
SELECT TIMESTAMP_DIFF(ended_at, started_at,MINUTE) duration FROM sample_data;

If it's STRING type by any chance, cast it as TIMESTAMP like below instead.

TIMESTAMP_DIFF(TIMESTAMP(ended_at), TIMESTAMP(started_at), MINUTE)