Translating Oracle Date Functions to BigQuery

I’m having trouble translating these Oracle date functions to BigQuery because BigQuery doesn’t allow such user-friendly additions to dates/timestamps. Any help is appreciated. Below are the two date functions in Oracle and my attempted (failed) versions in BigQuery:

Oracle:

TRUNC(S.DT, 'hh24') + TRUNC(TO_CHAR(S.DT, 'MI') / 5) * 5 / 1440 as interval_5_min,

 TRUNC(STRT_DT) + 7 + ( HR / 24 ) + TIME_INT as DT

​x
 
TRUNC(S.DT, 'hh24') + TRUNC(TO_CHAR(S.DT, 'MI') / 5) * 5 / 1440 as interval_5_min,​ TRUNC(STRT_DT) + 7 + ( HR / 24 ) + TIME_INT as DT​

BigQuery attempt:

datetime_add(datetime_TRUNC(S.DT, hour), interval (cast((trunc(extract(minute from S.DT) /5) * 5 / 1440) as int64)) minute) interval_5_min, 
datetime_add(datetime_TRUNC(STRT_DT, day), interval 7 day) + ( HR / 24 ) + TIME_INT as DT,

 
datetime_add(datetime_TRUNC(S.DT, hour), interval (cast((trunc(extract(minute from S.DT) /5) * 5 / 1440) as int64)) minute) interval_5_min, datetime_add(datetime_TRUNC(STRT_DT, day), interval 7 day) + ( HR / 24 ) + TIME_INT as DT,​

Answer

You can use below for the first one

datetime_trunc(DT, minute) - interval mod(extract(minute from DT), 5) minute

 
datetime_trunc(DT, minute) - interval mod(extract(minute from DT), 5) minute     ​

and for second – try below

datetime_trunc(STRT_DT, day) + interval 7 day + interval div(HR, 24) hour + interval TIME_INT minute

 
datetime_trunc(STRT_DT, day) + interval 7 day + interval div(HR, 24) hour + interval TIME_INT minute ​

Advertisement

Answer