How To Filter Alli Data Reports by Date to Power Actions - Tips & Tricks

Overview

The purpose of this document is to highlight how to properly filter an Alli Data report by date with tips & tricks to ensure you properly account for things like timezones and daylight savings.

If your Alli Data report is powered by another datasource (like a Google Sheet datasource, for example) we highly recommend you review the specific datasource’s Run Time details, which can be found here. It is important to note, for new datasources Automatic refresh times begin after Midnight on the following day.

This is important! For example: if you set up a Google Sheet Datasource to be leveraged in an action on Monday, and have an action that is running at 2 am on Tuesday, the automatic refreshes do not begin until Tuesday morning, so your data may not load before 2 am depending on the datasousource > so you may need to ensure you manually load your datasource so it contains all the data you want to action on.

Instructions

If you are creating a new action, follow the instructions listed in the Getting Started With Actions tutorial, and when you are setting your data source, select Alli Data.

Use Case:

A typical use case for utilizing an Alli Data report is when you are managing a Google Sheet that you need to filter down to only the rows you want to send on a specific date. Like in the Google Sheet below, if today was 3/13/2024, and you want the items with 3/14/2024 to run tonight at midnight CDT.

To accomplish this, you would create an Alli Datasource (we’ll name ours demo.example_sheet), and then set up a Alli Data report to filter on the date_running column.

Below are a few ways you can filter your data to ensure you capture the right rows.

Good:

select id, status
from demo.example_sheet
where date_runing = current_date

• This is good because we are ensuring we are filtering to the current date, but you should remember current_date uses the time zone of the computer server where your data lives, and not necessarily your timezone - meaning it could be already "tomorrow" in the server's time zone but still "today" for you or your Action schedule.

Better:

select id, status
from demo.example_sheet
WHERE date_runing = date(timezone('EST', current_date))

• This is better because you are accounting for the timezone you want to filter to, in filtering to todays date in EST aka Eastern Standard Time.

• However, this does not account for daylight savings and could cause issues. If your action runs at 11 PM CDT on 3/13/2024, then the current date in EST is also 11 pm on 3/13/2024, and the rows you want to run (rows with a date of 3/14) will not. (see chart below)

Best:

select id, status
from demo.example_sheet
WHERE date = date(timezone('America/New_York', current_date))

• This is best because not only are we accounting for timezones, America/New_York being eastern timezone, but utilizing this format instead of EST does account for daylight savings according to documentation Time zone name -- The full time zone name, such as America/New_York. Full time zone names can include daylight savings rules.

You can also use

• Eastern Standard Time (EST): America/New_York (New York)

• Central Standard Time (CST): America/Chicago (Chicago)

• Mountain Standard Time (MST): America/Denver (Denver)

• Pacific Standard Time (PST): America/Los_Angeles (Los Angeles)

Put It All Together

Using the above tutorial, I know that the correct SQL to utilize is likely the below, because if I run my action at 11 pm central, no matter if it runs 11 PM CDT or 11 PM CST, that daylight savings is accounted for, and date(timezone('America/New_York', current_date)) will always pull 12 AM of the next day. In this case, when it runs at 11 PM CDT on 3/13/2024, it will pull the 2 rows with 3/14.

select id, status
from demo.example_sheet
WHERE date = date(timezone('America/New_York', current_date))

Related articles