Date Range Filter Compiler

Build a utility that compiles date range filters into warehouse-specific SQL WHERE clauses. The system should handle relative date filters with timezone support and generate SQL compatible with different database warehouses.

Requirements

Your implementation should:

1. Accept filter configurations that specify:

   • A field name to filter on
   • A filter operator (e.g., "in the past", "in the next", "in the current")
   • A time value and unit (e.g., 7 days, 2 months, 1 year)
   • A timezone for date calculations
   • A target warehouse type (BigQuery, Snowflake, Postgres, or Redshift)

2. Generate valid SQL WHERE clause expressions that:

   • Calculate the appropriate date boundaries based on the current date and timezone
   • Use warehouse-specific SQL syntax for date/timestamp operations
   • Handle both "completed" periods (e.g., last 7 complete days) and "ongoing" periods (e.g., last 7 days including today)

3. Support at least these filter operators:

   • "in the past X days/weeks/months/years"
   • "in the next X days/weeks/months/years"
   • "in the current day/week/month/year"

4. Export the compiled SQL as a string that can be used in a WHERE clause.

Test Cases

• Compiling a filter for "timestamp_field in the past 7 days" with UTC timezone for BigQuery generates valid SQL with BigQuery-specific date functions @test

• Compiling a filter for "date_field in the next 30 days" with America/New_York timezone for Postgres generates valid SQL with Postgres-specific date functions @test

• Compiling a filter for "created_at in the current month" with UTC timezone for Snowflake generates SQL that checks if the field is within the current calendar month @test

Implementation

@generates

API

export type FilterOperator =
  | 'inThePast'
  | 'inTheNext'
  | 'inTheCurrent';

export type TimeUnit = 'day' | 'week' | 'month' | 'year';

export type WarehouseType = 'bigquery' | 'snowflake' | 'postgres' | 'redshift';

export interface DateFilterConfig {
  fieldName: string;
  operator: FilterOperator;
  value: number;
  unit: TimeUnit;
  timezone: string;
  warehouse: WarehouseType;
  completed?: boolean; // For past/next filters: true = completed periods only, false = including current
}

export function compileDateFilter(config: DateFilterConfig): string;

Dependencies { .dependencies }

@lightdash/common { .dependency }

Provides filter compilation utilities and warehouse-specific SQL generation for date operations.