View the original community article here
Last tested: Mar 5, 2021
The below examples are created for Bigquery.
If you just want to trigger the datagroup/PDT rebuilds 10 minutes past each hour:
if current minute is equal to or greater than 10, return the current hour, if not return current hour -1.
SELECT IF(
EXTRACT(MINUTE FROM CURRENT_DATETIME())>=10,
EXTRACT(HOUR FROM CURRENT_DATETIME()),
EXTRACT(HOUR FROM CURRENT_DATETIME())-1
)
10 minutes past each hour between 9 and 19:
Similar logic like above, however be aware that we use 18 rather 19, the last rebuild should be 18:10, and after 18:10, it should always return 18 until the next business hours starts at 9.
SELECT IF(
(
(EXTRACT(HOUR FROM CURRENT_DATETIME()) BETWEEN 9 AND 18) AND
(EXTRACT(MINUTE FROM CURRENT_DATETIME())>=10)
),
EXTRACT(HOUR FROM CURRENT_DATETIME()),
if(
(EXTRACT(HOUR FROM CURRENT_DATETIME()) BETWEEN 9 AND 18), EXTRACT(HOUR FROM CURRENT_DATETIME())-1,18
)
)
This content is subject to limited support.