Knowledge Drop

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

  • 5 April 2021
  • 1 reply
  • 221 views

Userlevel 4

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.                


1 reply

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

Reply