How to trigger datagroup/PDT rebuilds X minutes past each hour between certain hours of the day?

Knowledge Drop

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.                

Version history
Last update:
‎05-07-2021 09:48 AM
Updated by: