PDT trigger value at a specific time and day of week, once a week

[LookerExperts]

Knowledge Drop

Last Tested: Mar 22, 2021
 

This trigger changes weekly at 6 am on Monday:

MSSQL: SELECT FLOOR((DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE()) - 60*60*(24*4 + 6))/(60*60*24*7))

Postgres: SELECT FLOOR((EXTRACT(epoch from NOW()) - 60*60*(24*4 + 6))/(60*60*24*7))

Redshift: SELECT FLOOR((EXTRACT(epoch from GETDATE()) - 60*60*(24*4 + 6))/(60*60*24*7))

MySQL: SELECT FLOOR((UNIX_TIMESTAMP(NOW()) - 60*60*(24*4 + 6))/(60*60*24*7))

Snowflake: SELECT FLOOR((EXTRACT(epoch from CURRENT_TIMESTAMP) - 60*60*(24*4 + 6))/(60*60*24*7))

Just replace 6 with your desired hour, and 4 with the numbered day of the week from 0-6, starting from Thursday at 0. Thursday is 0 because the epoch starts at January 1, 1970, which is a Thursday.

This content is subject to limited support.                

[manufantaisie]

Hi,

Thanks for this, just used it now… but for BigQuery.

Code would be:

SELECT FLOOR((TIMESTAMP_DIFF(CURRENT_TIMESTAMP(),'1970-01-01 00:00:00',SECOND) - 60*60*(24*4 + 6))/(60*60*24*7))

Hope this helps others on BigQuery.

Cheers

[hgavilan]

hey guys is there a way to add minutes to the hour ? let's say i want it to run at 6:30 am each day on Snowflake??