Add information about date filter options

[oli-rmsy]

Information about different filters within the UI should be included in our docs.

For example, what is the difference between last 1 month and last 1 completed month.

Salesforce have a good example here: https://help.salesforce.com/s/articleView?id=analytics.filter_dates_relative.htm&type=5

There are a lot of combinations here, so we need to be thorough when checking!

To help understand what's happening, you can choose a filter in the UI, and then read the SQL that is generated from it.

[linear]

PROD-264

[lightdash-bot]

This issue was mentioned by a user in slack: https://lightdash-community.slack.com/archives/C03MG2VTR08/p1761059338656259?thread_ts=1761059338.656259&cid=C03MG2VTR08

[jpetey75]

Filtering on raw timestamp dimensions

Operator: in the last

• hours: subtracts an hour from the current timestamp and returns data that fall between N hour(s) ago and the current moment
• days: subtracts N days from the current timestamp and returns data after N days ago at the current time of day and before the current moment
• weeks: subtracts N weeks from the current timestamp and returns data after N weeks ago at the current time of day and before the current moment
• months: subtracts N weeks from the current timestamp and returns data after N months ago at the current day and time and before the current moment
• quarters: subtracts N quarters from the current timestamp and returns data after N quarters ago at the current date and time and before the current moment (a quarter is 3 months)
• years: subtracts N years from the current timestamp and returns data after N years ago at the current date and time and before the current moment

...Other operators coming soon...

Filtering on date dimensions

...coming soon...

[oli-rmsy]

I had the following output from claude which I need to check though, but I think it's quite a good way of showing it, with an example timestamp, and the actual SQL that's being produced. We need to add a bit more detail I think.

I've checked the timestamp side of things and that all looks good.

Some notes:

Week depends on your week start settings in Lightdash
It's in the last, not in the past
There are key differences between timestamps and dates. The second half of the docs shows specific date examples.

Date Filter Reference Guide

This document shows all possible date filter combinations in Lightdash 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

Current Period Filters

Current (In The Current)

Filter	SQL Output
Current minute	orders.created_at >= TIMESTAMP('2025-10-24 15:30:00') AND orders.created_at < TIMESTAMP('2025-10-24 15:31:00')
Current hour	orders.created_at >= TIMESTAMP('2025-10-24 15:00:00') AND orders.created_at < TIMESTAMP('2025-10-24 16:00:00')
Current day	orders.created_at >= TIMESTAMP('2025-10-24 00:00:00') AND orders.created_at < TIMESTAMP('2025-10-25 00:00:00')
Current week	orders.created_at >= TIMESTAMP('2025-10-21 00:00:00') AND orders.created_at < TIMESTAMP('2025-10-28 00:00:00')
Current month	orders.created_at >= TIMESTAMP('2025-10-01 00:00:00') AND orders.created_at < TIMESTAMP('2025-11-01 00:00:00')
Current quarter	orders.created_at >= TIMESTAMP('2025-10-01 00:00:00') AND orders.created_at < TIMESTAMP('2026-01-01 00:00:00')
Current year	orders.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 Past)

Filter	SQL Output
Last 1 minute	orders.created_at >= TIMESTAMP('2025-10-24 15:29:00') AND orders.created_at <= TIMESTAMP('2025-10-24 15:30:00')
Last 1 hour	orders.created_at >= TIMESTAMP('2025-10-24 14:30:00') AND orders.created_at <= TIMESTAMP('2025-10-24 15:30:00')
Last 1 day	orders.created_at >= TIMESTAMP('2025-10-23 15:30:00') AND orders.created_at <= TIMESTAMP('2025-10-24 15:30:00')
Last 1 week	orders.created_at >= TIMESTAMP('2025-10-17 15:30:00') AND orders.created_at <= TIMESTAMP('2025-10-24 15:30:00')
Last 1 month	orders.created_at >= TIMESTAMP('2025-09-24 15:30:00') AND orders.created_at <= TIMESTAMP('2025-10-24 15:30:00')
Last 1 quarter	orders.created_at >= TIMESTAMP('2025-07-24 15:30:00') AND orders.created_at <= TIMESTAMP('2025-10-24 15:30:00')
Last 1 year	orders.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 Past, Completed)

Filter	SQL Output
Last 1 completed minute	orders.created_at >= TIMESTAMP('2025-10-24 15:29:00') AND orders.created_at < TIMESTAMP('2025-10-24 15:30:00')
Last 1 completed hour	orders.created_at >= TIMESTAMP('2025-10-24 14:00:00') AND orders.created_at < TIMESTAMP('2025-10-24 15:00:00')
Last 1 completed day	orders.created_at >= TIMESTAMP('2025-10-23 00:00:00') AND orders.created_at < TIMESTAMP('2025-10-24 00:00:00')
Last 1 completed week	orders.created_at >= TIMESTAMP('2025-10-13 00:00:00') AND orders.created_at < TIMESTAMP('2025-10-20 00:00:00')
Last 1 completed month	orders.created_at >= TIMESTAMP('2025-09-01 00:00:00') AND orders.created_at < TIMESTAMP('2025-10-01 00:00:00')
Last 1 completed quarter	orders.created_at >= TIMESTAMP('2025-07-01 00:00:00') AND orders.created_at < TIMESTAMP('2025-10-01 00:00:00')
Last 1 completed year	orders.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)

Filter	SQL Output
Next 1 minute	orders.created_at >= TIMESTAMP('2025-10-24 15:30:00') AND orders.created_at <= TIMESTAMP('2025-10-24 15:31:00')
Next 1 hour	orders.created_at >= TIMESTAMP('2025-10-24 15:30:00') AND orders.created_at <= TIMESTAMP('2025-10-24 16:30:00')
Next 1 day	orders.created_at >= TIMESTAMP('2025-10-24 15:30:00') AND orders.created_at <= TIMESTAMP('2025-10-25 15:30:00')
Next 1 week	orders.created_at >= TIMESTAMP('2025-10-24 15:30:00') AND orders.created_at <= TIMESTAMP('2025-10-31 15:30:00')
Next 1 month	orders.created_at >= TIMESTAMP('2025-10-24 15:30:00') AND orders.created_at <= TIMESTAMP('2025-11-24 15:30:00')
Next 1 quarter	orders.created_at >= TIMESTAMP('2025-10-24 15:30:00') AND orders.created_at <= TIMESTAMP('2026-01-24 15:30:00')
Next 1 year	orders.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)

Filter	SQL Output
Next 1 completed minute	orders.created_at >= TIMESTAMP('2025-10-24 15:31:00') AND orders.created_at < TIMESTAMP('2025-10-24 15:32:00')
Next 1 completed hour	orders.created_at >= TIMESTAMP('2025-10-24 16:00:00') AND orders.created_at < TIMESTAMP('2025-10-24 17:00:00')
Next 1 completed day	orders.created_at >= TIMESTAMP('2025-10-25 00:00:00') AND orders.created_at < TIMESTAMP('2025-10-26 00:00:00')
Next 1 completed week	orders.created_at >= TIMESTAMP('2025-10-27 00:00:00') AND orders.created_at < TIMESTAMP('2025-11-03 00:00:00')
Next 1 completed month	orders.created_at >= TIMESTAMP('2025-11-01 00:00:00') AND orders.created_at < TIMESTAMP('2025-12-01 00:00:00')
Next 1 completed quarter	orders.created_at >= TIMESTAMP('2026-01-01 00:00:00') AND orders.created_at < TIMESTAMP('2026-04-01 00:00:00')
Next 1 completed year	orders.created_at >= TIMESTAMP('2026-01-01 00:00:00') AND orders.created_at < TIMESTAMP('2027-01-01 00:00:00')

Special Cases

Within Custom Range

Filter	SQL Output
Between 2 dates	orders.created_at >= TIMESTAMP('2025-10-01 00:00:00') AND orders.created_at <= TIMESTAMP('2025-10-31 23:59:59')
On exact date	orders.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:

   • Weeks start on Monday by default
   • Can be configured to start on Sunday
   • Week boundaries are always at midnight (00:00:00)

Date-Only Filter Reference Guide

This document shows all possible date filter combinations in Lightdash for DATE fields (without time components). All examples use BigQuery syntax and assume:

• Current date: 2025-10-24
• Example field: orders.created_date
• Week starts on Monday

Current Period Filters

Current (In The Current)

Filter	SQL Output
Current day	orders.created_date = DATE('2025-10-24')
Current week	orders.created_date >= DATE('2025-10-21') AND orders.created_date < DATE('2025-10-28')
Current month	orders.created_date >= DATE('2025-10-01') AND orders.created_date < DATE('2025-11-01')
Current quarter	orders.created_date >= DATE('2025-10-01') AND orders.created_date < DATE('2026-01-01')
Current year	orders.created_date >= DATE('2025-01-01') AND orders.created_date < DATE('2026-01-01')

Past Period Filters

Last N Periods (In The Past)

Filter	SQL Output
Last 1 day	orders.created_date >= DATE('2025-10-23') AND orders.created_date <= DATE('2025-10-24')
Last 7 days	orders.created_date >= DATE('2025-10-17') AND orders.created_date <= DATE('2025-10-24')
Last 30 days	orders.created_date >= DATE('2025-09-24') AND orders.created_date <= DATE('2025-10-24')
Last 90 days	orders.created_date >= DATE('2025-07-26') AND orders.created_date <= DATE('2025-10-24')
Last 365 days	orders.created_date >= DATE('2024-10-24') AND orders.created_date <= DATE('2025-10-24')

Last N Completed Periods (In The Past, Completed)

Filter	SQL Output
Last 1 completed day	orders.created_date = DATE('2025-10-23')
Last 1 completed week	orders.created_date >= DATE('2025-10-13') AND orders.created_date < DATE('2025-10-20')
Last 1 completed month	orders.created_date >= DATE('2025-09-01') AND orders.created_date < DATE('2025-10-01')
Last 1 completed quarter	orders.created_date >= DATE('2025-07-01') AND orders.created_date < DATE('2025-10-01')
Last 1 completed year	orders.created_date >= DATE('2024-01-01') AND orders.created_date < DATE('2025-01-01')

Future Period Filters

Next N Periods (In The Next)

Filter	SQL Output
Next 1 day	orders.created_date >= DATE('2025-10-24') AND orders.created_date <= DATE('2025-10-25')
Next 7 days	orders.created_date >= DATE('2025-10-24') AND orders.created_date <= DATE('2025-10-31')
Next 30 days	orders.created_date >= DATE('2025-10-24') AND orders.created_date <= DATE('2025-11-23')
Next 90 days	orders.created_date >= DATE('2025-10-24') AND orders.created_date <= DATE('2026-01-22')
Next 365 days	orders.created_date >= DATE('2025-10-24') AND orders.created_date <= DATE('2026-10-24')

Next N Completed Periods (In The Next, Completed)

Filter	SQL Output
Next 1 completed day	orders.created_date = DATE('2025-10-25')
Next 1 completed week	orders.created_date >= DATE('2025-10-27') AND orders.created_date < DATE('2025-11-03')
Next 1 completed month	orders.created_date >= DATE('2025-11-01') AND orders.created_date < DATE('2025-12-01')
Next 1 completed quarter	orders.created_date >= DATE('2026-01-01') AND orders.created_date < DATE('2026-04-01')
Next 1 completed year	orders.created_date >= DATE('2026-01-01') AND orders.created_date < DATE('2027-01-01')

Special Cases

Within Custom Range

Filter	SQL Output
Between 2 dates	orders.created_date >= DATE('2025-10-01') AND orders.created_date <= DATE('2025-10-31')
On exact date	orders.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()
   • Minutes and hours are not applicable

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