Timestamp difference between every consecutive row BIGQUERY SQL

Question

I have a table in SQL BQ with ID and DateTime (TIMESTAMP) columns. I want to calculate the difference in timestamp in let's say seconds between every consecutive row, and create a new column with calculated time differences.

Table:

ID     DateTime
a      2019-10-15 10:00:19 UTC
a      2019-10-15 10:00:29 UTC
a      2019-10-15 10:00:39 UTC
a      2019-10-15 10:00:49 UTC
a      2019-10-15 10:00:59 UTC

the desired result would look like this:

ID     DateTime                    TimeDiff
a      2019-10-15 10:00:19 UTC      null
a      2019-10-15 10:00:29 UTC       10
a      2019-10-15 10:00:39 UTC       10
a      2019-10-15 10:00:49 UTC       10
a      2019-10-15 10:00:59 UTC       10

So far i have tried these options with no success:

select ID, DateTime,
(LAG(DateTime) OVER (PARTITION BY ID ORDER BY DateTime ASC) - DateTime) AS TimeDiff
from `xxx.yyy.table` 
order by DateTime

and

select ID, DateTime,
timestamp_diff(lag(DateTime, 1) OVER (ORDER BY DateTime)) as TimeDiff
from `xxx.yyy.table`
order by DateTime

and

select ID, DateTime,
LAG(DateTime) OVER (PARTITION BY FieldID ORDER BY DateTime ASC) AS timeDiff
from `xxx.yyy.table` 
order by DateTime

Gordon Linoff Gordon Linoff · Accepted Answer · 2020-07-01T11:16:39

LAG() is the right function to get the value from the previous row. You just need to use TIMESTAMP_DIFF() correctly:

select ID, DateTime,
       timestamp_diff(DateTime,
                      lag(DateTime, 1) OVER (ORDER BY DateTime), 
                      second
                     ) as TimeDiff
from `xxx.yyy.table`
order by DateTime;

Note that it looks like you want this per id. If so, you should PARTITION BY as well:

       timestamp_diff(DateTime,
                      lag(DateTime, 1) OVER (PARTITION BY id ORDER BY DateTime), 
                      second
                     ) as TimeDiff

Timestamp difference between every consecutive row BIGQUERY SQL

1 Answers