CODEWITHSUNDEEP
google-bigquery
higgs241
higgs241

Reputation: 629

Get difference in time stamp between consecutive rows in BigQuery?

I have a series of entries in a database, one of which is a timestamp. I'd like to add as a field the difference between the timestamp of the current row and the row preceding it. 

Row ID      TimeStamp    
1   601S    1459223437   
2   602S    1459224049   
3   603S    1459224139   
4   604S    1459224255   
5   606S    1459224332   
6   607S    1459224406   
7   608S    1459224510   
8   609S    1459224596   
9   610S    1459224680

What I'd like

Row ID      TimeStamp    Difference
1   601     1459223437   n/a
2   602     1459224049   612
3   603     1459224139   90
4   604     1459224255   116
5   606     1459224332   77
6   607     1459224406   74
7   608     1459224510   104
8   609     1459224596   86
9   610     1459224680   84

Upvotes: 1

Views: 2240

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

SELECT id, ts, ts - prev_ts AS diff FROM (
  SELECT id, ts, LAG(ts) OVER(ORDER BY ts) AS prev_ts FROM 
    (SELECT 601 AS id, 1459223437 AS ts),
    (SELECT 602 AS id, 1459224049 AS ts),
    (SELECT 603 AS id, 1459224139 AS ts),
    (SELECT 604 AS id, 1459224255 AS ts),
    (SELECT 606 AS id, 1459224332 AS ts),
    (SELECT 607 AS id, 1459224406 AS ts),
    (SELECT 608 AS id, 1459224510 AS ts),
    (SELECT 609 AS id, 1459224596 AS ts),
    (SELECT 610 AS id, 1459224680 AS ts)
)

Upvotes: 3

Related Questions