Date Range

Usage

A Date Range filter is a UI control for selecting a date range.

A date range filter is used in a condition on a SQL date expression.

Syntax

{[<date_expr>]=<filter_variable_name>}

The <date_expr> must either be an expression of type DATE or an expression that can be cast to a DATE. The filter input values that the expression is compared to are always generated by Cluvio as date literals, as in DATE '2023-04-12'. For example, the parameterised SQL query

SELECT *
FROM logs
WHERE {log_date=my_date_range_filter}

with a selected input range of 2023-04-12 through 2023-04-14 (inclusive) results in database-specific SQL similar to the following:

SELECT *
FROM logs
WHERE log_date >= DATE '2023-04-12' AND log_date < DATE '2023-04-14' + INTERVAL '1' DAY

Relative Ranges

A date range filter allows the selection of relative date ranges. Because relative ranges are always relative to now, they are especially useful as saved defaults on a dashboard. The use of relative ranges on a date range filter is analogous to relative ranges in date-time-range filters, with the restriction that the smallest possible date/time unit that can be selected is a day.

Configuration

To configure a date range filter, select the Date Range UI control type in the filter definition dialog.

Use the Configuration tab to set a default value and further configuration options. The Value Type of a date range filter is always Date.

Condition Selectors

A condition selector offers the ability to tap into comparisons of date ranges that are derived from the selected range.

Next Range

SELECT *
FROM orders
WHERE {created_at_date=my_date_range.next}

The .next condition selector produces a SQL condition for the next date range. Thereby the next date range is defined as follows:

• If the selected date range covers full N months, the next date range also covers full N months, regardless of the number of days in a month. For example, if the selected date range starts at 2024-01-01 and ends at 2024-01-31 the next range starts at 2024-02-01 and ends at 2024-02-29.

• If the selected date range is a relative range "ending today", the next date range is a range starting at the next day / week / month / ... and ending at the same day-of-week/month/year as today. For example, if the selected date range is "This week (ending today)" and today is Wednesday 2024-06-05, the next date range starts at Monday 2024-06-10 and ends at Wednesday 2024-06-12.

• Otherwise the next date range starts on the day following the last day of the selected range and has the same absolute length (in days) as the selected range.

Previous Range

SELECT *
FROM orders
WHERE {created_at_date=my_date_range.previous}

The .previous condition selector produces a SQL condition for the previous date range. Thereby the previous date range is defined as follows:

• If the selected date range covers full N months, the previous date range also covers full N months, regardless of the number of days in a month. For example, if the selected date range starts at 2024-02-01 and ends at 2024-02-29 the previous range starts at 2024-01-01 and ends at 2024-01-31.

• If the selected date range is a relative range "ending today", the previous date range is a range starting at the previous day / week / month / ... and ending at the same day-of-week/month/year as today. For example, if the selected date range is "This week (ending today)" and today is Wednesday 2024-06-05, the previous date range starts at 2024-05-27 and ends at 2024-05-29.

• Otherwise the previous date range ends on the day right before the selected start date and has the same absolute length (in days) as the selected range.

Value Selectors

A value selector allows to tap into SQL value expressions associated with the selected date-time range.

Range Value

SELECT {my_date_range.value}

The range value selector produces the selected date range as a SQL string literal of the form '<ISO8601-date>~<ISO8601-date>' for an absolute range, for example '2024-01-01~2024-01-31', or as a SQL string literal representing a relative range as specified in Parameters in URLs.

Cluvio API

This value can be used as a Cluvio API input parameter for a custom date range filter, for example in URLs generated in the SQL result. See also Parameters in URLs.

Range Start

SELECT {my_date_range.start}

The range start selector produces the inclusive start of the selected date range as a database-specific date expression.

Time Zone

Unless a dashboard time zone is configured, the selection of a relative date range produces a SQL expression that represents a date in the data time zone, typically UTC.

Range End

SELECT {my_date_range.end}

The range end selector produces the inclusive end of the selected date range as a database-specific date expression.

Time Zone

Unless a dashboard time zone is configured, the selection of a relative date range produces a SQL expression that represents a date in the data time zone, typically UTC.

Next Range Start

SELECT {my_date_range.next_start}

The next range start selector produces the inclusive start of the next date range as a database-specific date expression or NULL if there is no next range because the selected range has no upper bound. See the Next Range condition selector for how the next range is defined.

Time Zone

Unless a dashboard time zone is configured, the selection of a relative date range produces a SQL expression that represents a date in the data time zone, typically UTC.

Next Range End

SELECT {my_date_range.next_end}

The next range end selector produces the inclusive end of the next date range as a database-specific date expression or NULL if there is no next range because the selected range has no upper bound. See the Next Range condition selector for how the next range is defined.

Time Zone

Unless a dashboard time zone is configured, the selection of a relative date range produces a SQL expression that represents a date in the data time zone, typically UTC.

Previous Range Start

SELECT {my_date_range.previous_start}

The previous range start selector produces the inclusive start of the previous date range as a database-specific date expression or NULL if there is no previous range because the selected range has no lower bound. See the Previous Range condition selector for how the previous range is defined.

Time Zone

Unless a dashboard time zone is configured, the selection of a relative date range produces a SQL expression that represents a date in the data time zone, typically UTC.

Previous Range End

SELECT {my_date_range.previous_end}

The previous range end selector produces the inclusive end of the previous date range as a database-specific date expression or NULL if there is no previous range because the selected range has no lower bound. See the Previous Range condition selector for how the previous range is defined.

Time Zone

Unless a dashboard time zone is configured, the selection of a relative date range produces a SQL expression that represents a date in the data time zone, typically UTC.