Scheduling PDT on day of week at a specific time


(Kyle) #1

Hi All,

I am trying to set up a PDT to run on Sunday at 10:00PM (PST).

I thought of using this code:
select dateadd(‘hour’,22,date_trunc(‘WEEK’,(current_date + interval ‘1 day’))- interval ‘1 day’)

But this will still update the table once a day at midnight. Any ideas on how to set this up? Should I convert to unix? Please note that the sql database I am using is Redshift.

Best,
Kyle


(Ryan Dunlavy) #2

Hi @kpmartin87,

In order to get the current timestamp instead of the current date, we can use the GETDATE() redshift function. Because the ISO week of year will change at midnight every Monday morning, to schedule a sql_trigger_value for 10PM Sunday we can add 2 hours to the current timestamp before extracting the week.

SELECT EXTRACT(WEEK FROM (getDate() + interval '2 hours' ));

The database timezone for Redshift is often in UTC, so we may want to perform that conversion first.

SELECT EXTRACT(WEEK FROM (CONVERT_TIMEZONE('UTC', 'PST', getDate()) + interval '2 hours' ));

If you have any questions about this, let me know!

Best,
Ryan


(Kyle) #3

Awesome! Thanks for the help on this @ryan.dunlavy! Totally makes sense.