Built-in Timerange

The built-in timerange filter allows the user to select a date/time range as either:

• An absolute range, e.g. January 2023.
• A relative range, e.g. Last 7 days.

Usage

The built-in date-time range filter is a condition used to reduce the data to an absolute or relative date & time range. It is typically used in SQL in the WHERE clause of a query:

SELECT
  *
FROM orders
WHERE
// highlight-next-line
  {created_at=timerange}

Syntax

{[<date-time-expr>]=timerange}

The generated SQL for the timerange filter is database-specific. Below is the generated SQL of the above query for a PostgreSQL database when filtering by the last 7 days (including today) at time of writing.

SELECT
  *
FROM dashboards
WHERE
  ((created_at) >= '2023-03-16T00:00:00' AND (created_at) <= '2023-03-22T23:59:59.999999')

The default selection for a built-in date-time range filter is All time, i.e. an unbounded range.

tip

String/Text expressions (e.g. columns) can be used with the timerange filter, as long as the values are in the ISO8601 format, e.g. 2016-07-01T00:00:00.

Configuration

The built-in date-time range filter does not have any configuration options, but you can use the custom filter variant of this filter, which offers the same functionality with the ability to fine-tune the behavior.

Time zones

The built-in timerange automatically applies time zone conversion, when a dashboard time zone is configured. For more details on time zones in Cluvio see Time Zones.

Condition Selectors

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

Next Range

SELECT *
FROM orders
WHERE {created_at=timerange.next}

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

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

• If the selected date-time range is a relative range "ending now", the next date-time range is a range starting at the next hour / day / week / month / ... and ending at the same time-of-day as now. For example, if the selected date-time range is "This day (ending now)" and the current time is 2024-01-01 15:10:30, the next date-time range starts at 2024-01-02 00:00:00 and ends at 2024-01-01 15:10:30.

• Otherwise the next date-time range starts immediately after the selected date-time range and has the same absolute length (in seconds) as the selected range.

Previous Range

SELECT *
FROM orders
WHERE {created_at=timerange.previous}

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

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

• If the selected date-time range is a relative range "ending now", the previous date-time range is a range starting at the previous hour / day / week / month / ... and ending at the same time-of-day as now. For example, if the selected date-time range is "This day (ending now)" and the current time is 2024-01-01 15:10:30, the previous date-time range starts at 2023-12-31 00:00:00 and ends at 2023-12-31 15:10:30.

• Otherwise the previous date-time range ends right before the selected date-time range and has the same absolute length (in seconds) 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 {timerange.value}

The range value selector produces the selected date-time range as a SQL string literal of the form <unix-time>~<unix-time> for an absolute range input, for example '1717200000~1717286399', or a SQL string literal representing a relative range as described in Parameters in URLs.

Cluvio API

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

Range Start

SELECT {timerange.start}

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

Time Zone

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

Range End

SELECT {timerange.end}

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

Time Zone

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

Sub-second precision

The inclusive end of the selected range includes the database-specific sub-second precision, such that it can be used as an inclusive upper bound in SQL:

SELECT * FROM orders WHERE created_at <= {timerange.end}

Next Range Start

SELECT {timerange.next_start}

The next range start selector produces the inclusive start of the next date-time range as a database-specific timestamp 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-time range produces a SQL expression that represents a date & time in the data time zone, typically UTC.

Next Range End

SELECT {timerange.next_end}

The next range end selector produces the inclusive end of the next date-time range as a database-specific timestamp 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-time range produces a SQL expression that represents a date & time in the data time zone, typically UTC.

Sub-second precision

The inclusive next end of the selected range includes the database-specific sub-second precision, such that it can be used as an inclusive upper bound in SQL:

SELECT * FROM orders WHERE created_at <= {timerange.next_end}

Previous Range Start

SELECT {timerange.previous_start}

The previous range start selector produces the inclusive start of the previous date-time range as a database-specific timestamp 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-time range produces a SQL expression that represents a date & time in the data time zone, typically UTC.

Previous Range End

SELECT {timerange.previous_end}

The previous range end selector produces the inclusive end of the previous date-time range as a database-specific timestamp 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-time range produces a SQL expression that represents a date & time in the data time zone, typically UTC.

Sub-second precision

The inclusive previous end of the selected range includes the database-specific sub-second precision, such that it can be used as an inclusive upper bound in SQL:

SELECT * FROM orders WHERE created_at <= {timerange.previous_end}