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

View the original community article here

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.