Skip to main content

Timerange

Overview​

The timerange filter is a sophisticated feature in SQL queries for handling datetime comparisons. It uses a single operator (=) but intelligently adapts to different datetime scenarios, automatically converting to appropriate SQL comparisons like BETWEEN, <=, or >=.

Functionality​

The timerange filter changes its behavior based on the input:

  • Two Datetime Values: Converts to a BETWEEN comparison.
  • First Value Null: Translates to a <= comparison (before or on a specific date).
  • Second Value Null: Translates to a >= comparison (on or after a specific date).

Syntax​

The general syntax for using the timerange filter in an SQL query is:

SELECT [column1, column2, ...] FROM [table] WHERE {column_name=timerange};
  • [column1, column2, ...]: Columns you want to select.
  • [table]: The table from which to select data.
  • {column_name=timerange}: The timerange filter applied to column_name.

Examples​

Example 1: Date Range (BETWEEN)​

Query: Select students born within a specific date range.

SQL:

SELECT name, age FROM students WHERE {birthday=timerange};

Behavior: If timerange includes two dates, it translates to WHERE birthday BETWEEN date1 AND date2.

Example 2: Before or On a Specific Date (<=)​

Query: Select students born before or on a specific date.

SQL:

SELECT name, age FROM students WHERE {birthday=timerange};

Behavior: If the first datetime in timerange is null and the second datetime is specified, it translates to WHERE birthday <= date2.

Example 3: On or After a Specific Date (>=)​

Query: Select students born on or after a specific date.

SQL:

SELECT name, age FROM students WHERE {birthday=timerange};

Behavior: If the first datetime is provided and the second datetime is unspecified, it translates to WHERE birthday >= date1.