rbkk2016 rbkk2016 - 6 months ago 28
SQL Question

difference in seconds between current row and previous row and store the value in separate column using google bigquery

I have a table that stores timestamp as below

Date Order ID
2016-05-31 11:46:54 UTC 14567
2016-05-31 11:46:43 UTC 876
2016-05-31 11:46:24 UTC 1345
2016-05-31 11:46:04 UTC 7345


I would like to take the difference in seconds between current row and previous row and store the value in separate column using google bigquery.

Date Order Difference In Seconds
2016-05-31 11:46:54 UTC 14567 0
2016-05-31 11:46:43 UTC 876 11
2016-05-31 11:46:24 UTC 1345 19
2016-05-31 11:46:04 UTC 7345 42

Answer

Below assumes your DATE filed is of STRING datatype
If it is already TIMESTAMP datatype - you should remove TIMESTAMP() from below query

SELECT
  DATE, id,
  IFNULL(TIMESTAMP_TO_SEC(TIMESTAMP(DATE)) -   
         TIMESTAMP_TO_SEC(TIMESTAMP(prev_date))
    , 0) AS Difference_In_Seconds
FROM (
  SELECT  
    DATE, id,
    LEAD(DATE) OVER (ORDER BY DATE DESC) AS prev_date
  FROM
    (SELECT '2016-05-31 11:46:54 UTC' AS DATE, 14567 AS id),
    (SELECT '2016-05-31 11:46:43 UTC' AS DATE, 876 AS id),
    (SELECT '2016-05-31 11:46:24 UTC' AS DATE, 1345 AS id), 
    (SELECT '2016-05-31 11:46:04 UTC' AS DATE, 7345 AS id)
)
ORDER BY DATE DESC

Added to address case when DATE field is of TIMESTAMP datatype

To make it simple - try below :o)

SELECT
  DATE, id,
  IFNULL(TIMESTAMP_TO_SEC(TIMESTAMP(DATE)) -   
         TIMESTAMP_TO_SEC(TIMESTAMP(prev_date))
    , 0) AS Difference_In_Seconds
FROM (
  SELECT  
    DATE, id,
    LEAD(DATE) OVER (ORDER BY DATE DESC) AS prev_date
  FROM 
    (SELECT STRING(DATE) AS DATE, id FROM [test:product.tab1] )
)
ORDER BY DATE DESC
Comments