Date & Time Range

Usage

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

This is the custom filter equivalent of the built-in timerange filter.

The filter is used in a condition on a date-time SQL expression (e.g. column).

Syntax

{[<date_time_expr>]=<filter_variable_name>}

For example, the parameterised SQL

SELECT *
FROM logs
WHERE {log_timestamp=my_date_time_range_filter}

with a selected input range of 2023-04-12T00:00:00 (inclusive) through 2023-04-12T23:59:59 (inclusive), comprising the full day of April 12th 2023, results in database-specific SQL similar to the following being executed by Cluvio:

SELECT *
FROM logs
WHERE log_timestamp >= TIMESTAMP '2023-04-12T00:00:00' AND log_timestamp < TIMESTAMP '2023-04-12T23:59:59' + INTERVAL '1' SECOND

Relative Ranges

A date & time range filter allows the selection of relative ranges. Because relative date-time ranges are always relative to now, they are especially useful as saved defaults on a dashboard.

The options for relative ranges are as follows:

• Last n <unit>: For example, "Last 7 days" is a range that includes the previous 6 days and today (until end of day).

• Last n <unit> (ending now): For example, "Last 7 days (ending now)" is a range that includes the last 168 hours (7 * 24h) ending "now".

• This <unit>: For example, "This day" is a range that includes the current (full) day, i.e. from midnight to midnight.

• This <unit> (ending now): For example, "This day (ending now)" is a range that starts at midnight and ends now.

• Previous <unit>: For example, "Previous week" is a range that starts on midnight of Monday of the previous week and ends at midnight of Monday of the current week.

Configuration

To configure a Date & Time Range filter, select the UI control type Date / Time Range in the filter definition dialog.

The Value Type is always DateTime and the input values are validated and represented in SQL accordingly. In the Configuration tab further details of the filter behavior can be configured.

The Time resolution can be used to control the precision with which the user can select the start and end time of the range.

Time zones

The date-time-range filter automatically applies time zone conversion, when a time zone is configured on the report's dashboard. For more details see the Time Zones Overview.

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=my_date_time_range.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=my_date_time_range.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 {my_date_time_range.value}

The range value selector produces the selected date-time range as a SQL string literal of the form '<ISO8601-date-time>~<ISO8601-date-time>' for an absolute range selected range, for example '2024-01-01T15:00:00~2024-01-01T23:59:59', or 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-time range filter, for example in URLs generated in the SQL result. See also Parameters in URLs.

Range Start

SELECT {my_date_time_range.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 {my_date_time_range.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 <= {my_date_time_range.end}

Next Range Start

SELECT {my_date_time_range.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 {my_date_time_range.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 <= {my_date_time_range.next_end}

Previous Range Start

SELECT {my_date_time_range.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 {my_date_time_range.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 <= {my_date_time_range.previous_end}