Knowledge Drop

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


Userlevel 2
  • Looker Staff
  • 16 replies

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.                

 

 

 


0 replies

Be the first to reply!

Reply