How can I calculate the difference in Minutes between 2 Timestamp-variables in BigQuery? (Standard SQL)

Question

I have 2 columns started_at and ended_at (both timestamps) and try to calculate the exact difference in minutes, but somehow it dont wanna work. I've already looked for similar questions but it doesnt work for me..

started_at	ended_at
2021-01-02 16:22:53 UTC	2021-01-02 16:43:40 UTC
...	...

What I have tried:

    EXTRACT (DATE FROM started_at) AS start_ymd,
    EXTRACT (TIME FROM started_at) AS start_time,
    EXTRACT (DATE FROM ended_at) AS end_ymd,
    EXTRACT (TIME FROM ended_at) AS end_time

Results are in yyyy-mm-dd format but I dont know what to do with these extracts.

The standard method below works well with 2 single timestamps:

SELECT 

    TIMESTAMP("2010-07-07 10:20:00+00") AS later_timestamp,
    TIMESTAMP("2008-12-25 15:30:00+00") AS earlier_timestamp,

    TIMESTAMP_DIFF(TIMESTAMP "2010-07-07 10:20:00+00", TIMESTAMP "2008-12-25 15:30:00+00", HOUR) AS hours;

But I have 2 columns with millions of data and I dont know how to calculate each timediff. Thanks a lot guys, I am dealing with this little problem since 6 hours smh

Answer 1

The "standard method" you mentioned should work fine, you just need to put a table name in (your query as it stands diffs two contant values and hence returns one single result):

SELECT  
  started_at, 
  ended_at,
  TIMESTAMP_DIFF(started_at, ended_at, MINUTE) AS minutes_between
FROM
  yourtablenamehere

Used on a 2 million row table called yourtablenamehere it produces 2 million timestamp diffs - run with care

Answer 2

Try this: TIMESTAMP_DIFF(ended_at, started_at, minute)

Official documentation is here