Date Filtering in Apache Superset's Preset Using Jinja

Question

Having a hard time getting my head around filtering in Superset's Preset.

Consider the following SQL code:

SELECT    order_id, receipt_total,
          cast(created_at AS date) AS creation_date
FROM      order_details
WHERE     cast(created_at AS date) = '2023-03-19'

If I have a filter set up in my dashboard (let's call it "created_date") that takes a date range as input (e.g., "2023-03-19 ≤ col < 2023-03-19"), how can I re-write the WHERE clause above in Jinja syntax to work with any filter date or range of?

I've tried the following so far with no success:

WHERE cast(created_at AS date) = {{ filter_values('created_date') }}

Error: syntax error at or near "]"

WHERE cast(created_at AS date) IN ({{ "'" + "', '".join(filter_values('created_date')) + "'" }})

Error: invalid input syntax for type date: ""

Any guidance would be sincerely appreciated.

Answer 1

Jinja filters allow you to use date filtering with date range filters in dashboards. This means that you have to create the date range filter with whatever name you want, and the lower range can be accessed via from_dttm value and the upper range via to_dttm. Like this:

SELECT * FROM ... where date between DATE('{{ from_dttm }}') AND DATE('{{ to_dttm }}') 

One important point is that these values are TIMESTAMPS which means you have to cast them to date using DATE(). Hope this helps.