How to set date offsets for scheduled data refreshes?

Dynamically change dates in scheduled queries

Occasionally you need to run SQL queries that contain date ranges. For instance, the following query: SELECT * FROM corona WHERE country = 'US' and date >= '2020-06-01' and date <= '2020-06-07' queries data from the first week of June, Monday through Sunday. Now, imagine you need to set up an auto-refresh schedule that runs every Monday at 9am and queries data for the past 7 days. This is a typical scenario for weekly reporting. How would you do this without having to manually update the dates in your SQL query each week?

You can use dynamic date offsets. Date offsets are activated using the @{DATE} syntax. In the query above, you would replace the two date instances with a distinct variable name enclosed by @{ }. After replacing the instances, the query would look like this: SELECT * FROM corona WHERE country = 'US' and date >= @{start_date} and date <= @{end_date}. Then, in the input fields that appear, enter 1W for the start_date and 0 for the end_date to indicate no offset from the time the query is scheduled to run. Below is list of allowed offsets you can use and how to use them.

No offset (0)

A 0 offset means no offset. This in turns means, "at whichever time your query is ran or scheduled to run".

Hourly (H)

Allows you to offset by a number of hours before the time the query is ran or scheduled to run. To offset time by 8 hours, enter 8H in the input field.

Daily (H)

Allows you to offset by a number of days before the time the query is ran or scheduled to run. To offset time by 1 day, enter 1D in the input field.

Weekly (W)

Allows you to offset by a number of weeks before the time the query is ran or scheduled to run. To offset time by 1 week, enter 1W in the input field.

Book demoStart free trial

Try Castodia free for 14 days, no credit card required.