How to use MILLISECOND with date_add and date_diff

0
votes

I am bit stuck trying to use MILLISECOND with date_add function in bigquery:

I'm trying this SQL which is not working for me

DATE_ADD(MILLISECOND,-1,LEAD(dt) OVER (PARTITION BY xy ORDER BY dt ASC)

also looking for a solution for DATE_DIFF function

Any help is really appreciated.

2
google-bigquery
can you state what's the goal of this query? sample data? desired result? – Felipe Hoffa
Sorry, for not making it clear earlier. I am trying to find the lead value of the dt(validfrom) field and -1 to the milliseconds and load to a new dt field as validto. I am getting an error when I use the MILLISECOND in the above part. I am trying to figure whats the equivalent of SQL MILLISECOND to BQ MILLISECOND in DATE_ADD function. – Aced

2 Answers

1
votes

DATE_ADD needs date_part as argument. You can use DATATIME_ADD for this. This is working for me:

DATETIME_ADD(LEAD(validfrom) OVER (PARTITION BY xy ORDER BY validfrom ASC), INTERVAL -1 MILLISECOND) as validto

Playing with some dummy data:

WITH test_table as
(
    SELECT 1 AS xy, CURRENT_DATETIME() AS validfrom UNION ALL
    SELECT 1 AS xy, DATETIME_ADD(CURRENT_DATETIME(), INTERVAL 1 MINUTE ) AS validfrom UNION ALL
    SELECT 1 AS xy, DATETIME_ADD(CURRENT_DATETIME(), INTERVAL 2 MINUTE ) AS validfrom UNION ALL
    SELECT 1 AS xy, DATETIME_ADD(CURRENT_DATETIME(), INTERVAL 3 MINUTE ) AS validfrom
)
SELECT
  *,
  DATETIME_ADD(LEAD(validfrom) OVER (PARTITION BY xy ORDER BY validfrom ASC), INTERVAL -1 MILLISECOND) as validto
FROM test_table;

This is the result:

Row xy  validfrom                   validto  
1   1   2019-03-18T15:06:18.086840  2019-03-18T15:07:18.085840   
2   1   2019-03-18T15:07:18.086840  2019-03-18T15:08:18.085840   
3   1   2019-03-18T15:08:18.086840  2019-03-18T15:09:18.085840   
4   1   2019-03-18T15:09:18.086840  null

Notice that the validto field are 1 millisecond before than validfrom of the next row, which should be the expected result.

1
votes

This is a working example using DATETIME_ADD

SELECT *, DATETIME_DIFF(seq_date,LEAD_DT, HOUR) FROM  
(SELECT *, RANK() OVER (PARTITION BY ID ORDER BY seq_date) AS RNK,
DATETIME_ADD((LEAD(seq_date) OVER (PARTITION BY ID ORDER BY seq_date)) ,INTERVAL 3 HOUR) LEAD_DT
FROM 
(select 1 as id, CURRENT_DATETIME() as seq_date, 10 as cost
union ALL
select 1 as id, DATETIME_ADD(CURRENT_DATETIME(), INTERVAL -10 MINUTE ) as seq_date, 12 as cost
UNION ALL
select 1 as id, DATETIME_ADD(CURRENT_DATETIME(), INTERVAL -1 HOUR ) as seq_date, 13 as cost
UNION ALL
select 1 as id, DATETIME_ADD(CURRENT_DATETIME(), INTERVAL 5 HOUR ) as seq_date, 20 as cost));

This is a working example using DATE_ADD

SELECT *, DATE_DIFF(seq_date,LEAD_DT, DAY) FROM  
(SELECT *, RANK() OVER (PARTITION BY ID ORDER BY seq_date) AS RNK,
DATE_ADD((LEAD(seq_date) OVER (PARTITION BY ID ORDER BY seq_date)) ,INTERVAL 3 DAY) LEAD_DT
FROM 
(select 1 as id, CURRENT_DATE() as seq_date, 10 as cost
union ALL
select 1 as id, DATE_ADD(CURRENT_DATE(), INTERVAL -10 DAY ) as seq_date, 12 as cost
UNION ALL
select 1 as id, DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY ) as seq_date, 13 as cost
UNION ALL
select 1 as id, DATE_ADD(CURRENT_DATE(), INTERVAL 5 DAY ) as seq_date, 20 as cost));

You can refer below link for more details