Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Scheduling PDT on day of week at a specific time

  • 29 May 2018
  • 3 replies
  • 569 views
kpmartin87
Userlevel 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

3 replies

ryan.dunlavy
Rank
Userlevel 3

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

kpmartin87
Userlevel 1

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

rpani710

To add to @ryan.dunlavy’s point, it is better to use “US/Pacific” instead of “PST” to automatically take care of the timezone. 

Reply


Terms of Service