Skip to main content

Documentation Index

Fetch the complete documentation index at: https://lightdash-update-dbt-code-blocks.mintlify.app/llms.txt

Use this file to discover all available pages before exploring further.

​
Using filters

To learn more about using filters, check out our docs on limiting data using filters.

​
Filter types

​
Numeric filters

Filterlogic
is nullOnly pulls in rows where the values are null for the field selected.
is not nullOnly pulls in rows where the values are not null for the field selected.
isOnly pulls in rows where the values are equal to the values listed.
is notOnly pulls in rows where the values are not equal to the values listed.
is less thanOnly pulls in rows where the values for the field selected are strictly less than the value listed.
is greater thanOnly pulls in rows where the values for the field selectedare strictly greater than the value listed.

​
String filters

Filterlogic
is nullOnly pulls in rows where the values are null for the field selected.
is not nullOnly pulls in rows where the values are not null for the field selected.
isOnly pulls in rows where the values are equal to the values listed.
is notOnly pulls in rows where the values are not equal to the values listed. This filter explicitly includes NULL values. To exclude NULLs, add a separate is not null filter.
starts withOnly pulls in rows where the values for the field selected start with characters you’ve entered.
includesOnly pulls in rows where the values for the field selected includes the characters you’ve entered.
ends withOnly pulls in rows where the values for the field selected end with the characters you’ve entered.

​
Boolean filters

Filterlogic
is nullOnly pulls in rows where the values are null for the field selected.
is not nullOnly pulls in rows where the values are not null for the field selected.
isOnly pulls in rows where the values are equal to the values listed.

​
Date filters

Filterlogic
is nullOnly pulls in rows where the values are null for the field selected.
is not nullOnly pulls in rows where the values are not null for the field selected.
isOnly pulls in rows where the values are equal to the values listed.
is notOnly pulls in rows where the values are not equal to the values listed.
in the lastOnly pulls in rows where the dates for the field selected are in the last time period you entered: “in the last 3 days”, “in the last 2 completed weeks”, “in the last 3 quarters” etc.
not in the lastOnly pulls in rows where the dates for the field selected are not in the last time period you entered.
in the nextOnly pulls in rows where the dates for the field selected are in the next time period you entered: “in the next 3 days”, “in the next 2 completed weeks”, “in the next 3 quarters” etc.
not in the nextOnly pulls in rows where the dates for the field selected are not in the next time period you entered.
in the currentOnly pulls in rows where the dates for the field selected are in the current time period you entered: “in the current day”, “in the current week”, “in the current quarter” etc.
not in the currentOnly pulls in rows where the dates for the field selected are not in the current time period you entered.
is beforeOnly pulls in rows where the dates for the field selected are strictly before the date you entered.
is on or beforeOnly pulls in rows where the dates for the field selected are on or before the date you entered.
is afterOnly pulls in rows where the dates for the field selected are strictly after the date you entered.
is on or afterOnly pulls in rows where the dates for the field selected are on or after the date you entered.
is betweenOnly pulls in rows where the dates for the field selected are on or between the dates you entered: “between 2001-12-23 and 2003-01-02”.

​
Date/Timestamp Filter Reference Guide

The below examples show possible date/timestamp filter combinations and their corresponding SQL outputs. All examples use BigQuery syntax and assume:
  • Current timestamp: 2025-10-24 15:30:00
  • Example field: orders.created_at
  • Timezone: UTC
  • Week starts on Monday

​
Timestamp filter examples

​
Current Period Filters

Current (In The Current)
FilterSQL Output
Current minuteorders.created_at >= TIMESTAMP('2025-10-24 15:30:00') AND orders.created_at < TIMESTAMP('2025-10-24 15:31:00')
Current hourorders.created_at >= TIMESTAMP('2025-10-24 15:00:00') AND orders.created_at < TIMESTAMP('2025-10-24 16:00:00')
Current dayorders.created_at >= TIMESTAMP('2025-10-24 00:00:00') AND orders.created_at < TIMESTAMP('2025-10-25 00:00:00')
Current weekorders.created_at >= TIMESTAMP('2025-10-21 00:00:00') AND orders.created_at < TIMESTAMP('2025-10-28 00:00:00')
Current monthorders.created_at >= TIMESTAMP('2025-10-01 00:00:00') AND orders.created_at < TIMESTAMP('2025-11-01 00:00:00')
Current quarterorders.created_at >= TIMESTAMP('2025-10-01 00:00:00') AND orders.created_at < TIMESTAMP('2026-01-01 00:00:00')
Current yearorders.created_at >= TIMESTAMP('2025-01-01 00:00:00') AND orders.created_at < TIMESTAMP('2026-01-01 00:00:00')

​
Past Period Filters

Last N Periods (in the last)
FilterSQL Output
Last 1 minuteorders.created_at >= TIMESTAMP('2025-10-24 15:29:00') AND orders.created_at <= TIMESTAMP('2025-10-24 15:30:00')
Last 1 hourorders.created_at >= TIMESTAMP('2025-10-24 14:30:00') AND orders.created_at <= TIMESTAMP('2025-10-24 15:30:00')
Last 1 dayorders.created_at >= TIMESTAMP('2025-10-23 15:30:00') AND orders.created_at <= TIMESTAMP('2025-10-24 15:30:00')
Last 1 weekorders.created_at >= TIMESTAMP('2025-10-17 15:30:00') AND orders.created_at <= TIMESTAMP('2025-10-24 15:30:00')
Last 1 monthorders.created_at >= TIMESTAMP('2025-09-24 15:30:00') AND orders.created_at <= TIMESTAMP('2025-10-24 15:30:00')
Last 1 quarterorders.created_at >= TIMESTAMP('2025-07-24 15:30:00') AND orders.created_at <= TIMESTAMP('2025-10-24 15:30:00')
Last 1 yearorders.created_at >= TIMESTAMP('2024-10-24 15:30:00') AND orders.created_at <= TIMESTAMP('2025-10-24 15:30:00')
Last N Completed Periods (in the last, Completed)
FilterSQL Output
Last 1 completed minuteorders.created_at >= TIMESTAMP('2025-10-24 15:29:00') AND orders.created_at < TIMESTAMP('2025-10-24 15:30:00')
Last 1 completed hourorders.created_at >= TIMESTAMP('2025-10-24 14:00:00') AND orders.created_at < TIMESTAMP('2025-10-24 15:00:00')
Last 1 completed dayorders.created_at >= TIMESTAMP('2025-10-23 00:00:00') AND orders.created_at < TIMESTAMP('2025-10-24 00:00:00')
Last 1 completed weekorders.created_at >= TIMESTAMP('2025-10-13 00:00:00') AND orders.created_at < TIMESTAMP('2025-10-20 00:00:00')
Last 1 completed monthorders.created_at >= TIMESTAMP('2025-09-01 00:00:00') AND orders.created_at < TIMESTAMP('2025-10-01 00:00:00')
Last 1 completed quarterorders.created_at >= TIMESTAMP('2025-07-01 00:00:00') AND orders.created_at < TIMESTAMP('2025-10-01 00:00:00')
Last 1 completed yearorders.created_at >= TIMESTAMP('2024-01-01 00:00:00') AND orders.created_at < TIMESTAMP('2025-01-01 00:00:00')

​
Future Period Filters

Next N Periods (In The Next)
FilterSQL Output
Next 1 minuteorders.created_at >= TIMESTAMP('2025-10-24 15:30:00') AND orders.created_at <= TIMESTAMP('2025-10-24 15:31:00')
Next 1 hourorders.created_at >= TIMESTAMP('2025-10-24 15:30:00') AND orders.created_at <= TIMESTAMP('2025-10-24 16:30:00')
Next 1 dayorders.created_at >= TIMESTAMP('2025-10-24 15:30:00') AND orders.created_at <= TIMESTAMP('2025-10-25 15:30:00')
Next 1 weekorders.created_at >= TIMESTAMP('2025-10-24 15:30:00') AND orders.created_at <= TIMESTAMP('2025-10-31 15:30:00')
Next 1 monthorders.created_at >= TIMESTAMP('2025-10-24 15:30:00') AND orders.created_at <= TIMESTAMP('2025-11-24 15:30:00')
Next 1 quarterorders.created_at >= TIMESTAMP('2025-10-24 15:30:00') AND orders.created_at <= TIMESTAMP('2026-01-24 15:30:00')
Next 1 yearorders.created_at >= TIMESTAMP('2025-10-24 15:30:00') AND orders.created_at <= TIMESTAMP('2026-10-24 15:30:00')
Next N Completed Periods (In The Next, Completed)
FilterSQL Output
Next 1 completed minuteorders.created_at >= TIMESTAMP('2025-10-24 15:31:00') AND orders.created_at < TIMESTAMP('2025-10-24 15:32:00')
Next 1 completed hourorders.created_at >= TIMESTAMP('2025-10-24 16:00:00') AND orders.created_at < TIMESTAMP('2025-10-24 17:00:00')
Next 1 completed dayorders.created_at >= TIMESTAMP('2025-10-25 00:00:00') AND orders.created_at < TIMESTAMP('2025-10-26 00:00:00')
Next 1 completed weekorders.created_at >= TIMESTAMP('2025-10-27 00:00:00') AND orders.created_at < TIMESTAMP('2025-11-03 00:00:00')
Next 1 completed monthorders.created_at >= TIMESTAMP('2025-11-01 00:00:00') AND orders.created_at < TIMESTAMP('2025-12-01 00:00:00')
Next 1 completed quarterorders.created_at >= TIMESTAMP('2026-01-01 00:00:00') AND orders.created_at < TIMESTAMP('2026-04-01 00:00:00')
Next 1 completed yearorders.created_at >= TIMESTAMP('2026-01-01 00:00:00') AND orders.created_at < TIMESTAMP('2027-01-01 00:00:00')
Within Custom Range
FilterSQL Output
Between 2 datesorders.created_at >= TIMESTAMP('2025-10-01 00:00:00') AND orders.created_at <= TIMESTAMP('2025-10-31 23:59:59')
On exact dateorders.created_at >= TIMESTAMP('2025-10-24 00:00:00') AND orders.created_at < TIMESTAMP('2025-10-25 00:00:00')

​
Notes

  1. Completed periods always:
    • Start at the beginning of a period (00:00:00)
    • End at the beginning of the next period
    • Don’t include partial periods
  2. Rolling periods (non-completed):
    • Use the current time as the reference point
    • Look backward/forward the specified amount
    • Include partial periods
  3. Current periods:
    • Always start at the beginning of the current period
    • End at the beginning of the next period
    • Example: Current month starts at 1st of the month
  4. Week handling:
    • By default, weeks start on the default day configured in your database
    • You can configure this to a different day in your database connection settings
    • Week boundaries are always at midnight (00:00:00)

​
Date filter examples

​
Current Period Filters

Current (In The Current)
FilterSQL Output
Current dayorders.created_date = DATE('2025-10-24')
Current weekorders.created_date >= DATE('2025-10-21') AND orders.created_date < DATE('2025-10-28')
Current monthorders.created_date >= DATE('2025-10-01') AND orders.created_date < DATE('2025-11-01')
Current quarterorders.created_date >= DATE('2025-10-01') AND orders.created_date < DATE('2026-01-01')
Current yearorders.created_date >= DATE('2025-01-01') AND orders.created_date < DATE('2026-01-01')

​
Past Period Filters

Last N Periods (in the last)
FilterSQL Output
Last 1 dayorders.created_date >= DATE('2025-10-23') AND orders.created_date <= DATE('2025-10-24')
Last 7 daysorders.created_date >= DATE('2025-10-17') AND orders.created_date <= DATE('2025-10-24')
Last 30 daysorders.created_date >= DATE('2025-09-24') AND orders.created_date <= DATE('2025-10-24')
Last 90 daysorders.created_date >= DATE('2025-07-26') AND orders.created_date <= DATE('2025-10-24')
Last 365 daysorders.created_date >= DATE('2024-10-24') AND orders.created_date <= DATE('2025-10-24')
Last N Completed Periods (in the last, Completed)
FilterSQL Output
Last 1 completed dayorders.created_date = DATE('2025-10-23')
Last 1 completed weekorders.created_date >= DATE('2025-10-13') AND orders.created_date < DATE('2025-10-20')
Last 1 completed monthorders.created_date >= DATE('2025-09-01') AND orders.created_date < DATE('2025-10-01')
Last 1 completed quarterorders.created_date >= DATE('2025-07-01') AND orders.created_date < DATE('2025-10-01')
Last 1 completed yearorders.created_date >= DATE('2024-01-01') AND orders.created_date < DATE('2025-01-01')

​
Future Period Filters

Next N Periods (In The Next)
FilterSQL Output
Next 1 dayorders.created_date >= DATE('2025-10-24') AND orders.created_date <= DATE('2025-10-25')
Next 7 daysorders.created_date >= DATE('2025-10-24') AND orders.created_date <= DATE('2025-10-31')
Next 30 daysorders.created_date >= DATE('2025-10-24') AND orders.created_date <= DATE('2025-11-23')
Next 90 daysorders.created_date >= DATE('2025-10-24') AND orders.created_date <= DATE('2026-01-22')
Next 365 daysorders.created_date >= DATE('2025-10-24') AND orders.created_date <= DATE('2026-10-24')
Next N Completed Periods (In The Next, Completed)
FilterSQL Output
Next 1 completed dayorders.created_date = DATE('2025-10-25')
Next 1 completed weekorders.created_date >= DATE('2025-10-27') AND orders.created_date < DATE('2025-11-03')
Next 1 completed monthorders.created_date >= DATE('2025-11-01') AND orders.created_date < DATE('2025-12-01')
Next 1 completed quarterorders.created_date >= DATE('2026-01-01') AND orders.created_date < DATE('2026-04-01')
Next 1 completed yearorders.created_date >= DATE('2026-01-01') AND orders.created_date < DATE('2027-01-01')
Within Custom Range
FilterSQL Output
Between 2 datesorders.created_date >= DATE('2025-10-01') AND orders.created_date <= DATE('2025-10-31')
On exact dateorders.created_date = DATE('2025-10-24')

​
Notes

  1. Key differences from timestamp filters:
    • No time components in any filters
    • Single day comparisons use equality (=) instead of ranges
    • DATE() function used instead of TIMESTAMP()
  2. Completed periods always:
    • Start at the beginning of a period
    • End at the beginning of the next period
    • Don’t include partial periods
    • For single days, use equality instead of ranges
  3. Rolling periods:
    • Use the current date as the reference point
    • Count in full days (N days forward/backward)
    • Include the current date in the range
  4. Current periods:
    • For single day: use equality
    • For longer periods: use standard ranges
    • Example: Current month is all days from 1st to last day
  5. Week handling:
    • Weeks start on Monday by default
    • Can be configured to start on Sunday
    • Full days only, no time components