Translating Oracle Date Functions to BigQuery

user_16310118 0
Viewed 103

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,
google-bigquery oracle sql
2 Answers

In the first, you appear to want to truncate to the nearest 5 minutes:

datetime_add(datetime_trunc(S.DT, hour),
             interval trunc(extract(minute from S.DT) / 5) * 5  minute
            ) as interval_5_min,

The second is just date arithmetic. However, it depends on the values. A reasonable approximation is:

datetime_add(datetime_TRUNC(STRT_DT, day),
             interval (7 + (HR / 24) + time_int day
            )

However, Oracle allows fractional days and BigQuery doesn't. So, I might go for:

datetime_add(datetime_TRUNC(STRT_DT, day),
             interval (7 + time_int) * 24 + HR hour
            )

And even this might not be correct if there are minute or second components that are part of the calculation.

user_11440350

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
user_52219440
Related