Date filters in Superset: bypassing the wrapper

Bypassing Date Filters in Apache Superset Virtual Datasets

In Apache Superset, virtual datasets often require custom date manipulations from dashboard filters. Filters automatically wrap SQL queries with additional conditions, breaking your intended logic. While the remove_filter macro existed in versions prior to 5, it's no longer available. Let’s walk through a common use case: adding 3 days to the from_dttm filter value.

Create a virtual dataset with this query:

select *   from messages   where 1=1   and ts > (TIMESTAMP '{{ from_dttm | default("1970-01-01", true) }}' + INTERVAL '3 days')

Add a "Table" chart to the dashboard with the "Last Week" filter. Superset wraps the query with conditions like ts >= '...' AND ts < '...', ignoring your +3 days adjustment. The filtering behaves incorrectly.

Understanding Filter Macro Behavior

The from_dttm and to_dttm macros return:

• None (Python None) if the filter is empty.
• A timestamp string (e.g., '2026-03-23 00:00:00') when a value is set.

Test this by temporarily simplifying the query:

select {{from_dttm}} as res

Without a filter: None. With a filter: '2026-03-23 00:00:00'. This is the key — use default to handle None values.

Universal Hack: Fake Column Approach

Capture filter values into Jinja2 variables with a default, create a fake DATETIME column. This column syncs with the dataset and becomes the basis for dashboard date filtering.

Base query:

{% set from_dttm_fv = from_dttm | default("1970-01-01 00:00:00", true) %}   SELECT *, '{{ from_dttm_fv }}'::TIMESTAMP as fict_column   FROM messages

After saving:

• Sync the dataset metadata.
• Verify that fict_column is of type DATETIME.
• In the dashboard, set fict_column as the Time Column in the date filter.

Superset’s external conditions (fict_column >= from_dttm AND fict_column < to_dttm) will always evaluate to true because from_dttm_fv is pulled directly from the filter.

Extending to to_dttm and Custom Logic

Capture both filter values:

{% set from_dttm_fv = from_dttm | default("1970-01-01 00:00:00", true) %}   {% set to_dttm_fv = to_dttm | default("1970-01-01 00:00:00", true) %}   SELECT *   , '{{ from_dttm_fv }}'::TIMESTAMP as fict_column   FROM messages   WHERE ts > (TIMESTAMP '{{ from_dttm_fv }}' + INTERVAL '3 days')   and ts < (TIMESTAMP '{{ to_dttm_fv }}' + INTERVAL '3 days')

Now:

• External conditions on fict_column are always true.
• Internal logic applies the +3 days shift to ts.
• Dashboard filtering works correctly.

Typical Setup Workflow

• Define the required date modification (shift, rounding).
• Test macros: confirm behavior with None and actual values.
• Create a fake column using default.
• Sync the dataset and verify DATETIME type.
• Configure the dashboard filter to use fict_column.
• Apply custom logic in the WHERE clause.
• Validate results on the dashboard.

Key Takeaways

• Fake column is mandatory: ensures Superset’s external conditions remain true.
• Default for None is critical: 1970-01-01 00:00:00 prevents runtime errors.
• Use ::TIMESTAMP cast: guarantees correct DATETIME type in metadata.
• Sync metadata: without it, the column won’t appear in filters.
• Universal solution: works for any date manipulation on from_dttm or to_dttm.

This method gives full control over date handling in virtual datasets without conflicts with dashboard filters.

— Editorial Team