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
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,
Advertisement
Answer
You can use below for the first one
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