-1

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

Onur
  • 35
  • 6

2 Answers2

0

Try this: TIMESTAMP_DIFF(ended_at, started_at, minute)

Official documentation is here

Sergey Geron
  • 9,098
  • 2
  • 22
  • 29
0

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

Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • OMG, I have tried this type of code a million times but got allways an error. Your Code works well. Thank you so much :) – Onur Jun 24 '21 at 12:58
  • I'd recommend posting up any such attempts and the error messages, so we can tell you where you go wrong - it sounds like you were very close to a solution, maybe just some missing bracket or something – Caius Jard Jun 24 '21 at 12:59