Skip to main content
Skip table of contents

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.

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.

image-20240314-234825.png

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

Good:

CODE
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:

CODE
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)

Time (CDT)

EST

EDT

11 PM 3/13/2024

11 PM 3/13/2024

12 AM 3/14/2024

Best:

CODE
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.

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

 

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.