Time of Day Range

Usage

A Time of Day Range filter is a UI control for selecting a time-of-day range (hour, minute and second).

A time-of-day range filter is used in a condition on a SQL time expression.

Syntax

{[<time_expr>]=<filter_variable_name>}

The <time_expr> must either be an expression of type TIME or an expression that can be cast to TIME. The filter input values that the expression is compared to are always generated by Cluvio as time-of-day literals, e.g. TIME '13:45:31'. For example, the parameterised SQL query

SELECT *
FROM logs
WHERE {log_time=my_time_range_filter}

with a selected input time range of 13:00:00 through 13:59:59 (inclusive) results in database-specific SQL similar to the following:

SELECT *
FROM logs
WHERE log_time >= TIME '13:00:00' AND log_time < TIME '13:59:59' + INTERVAL '1' SECOND

Relative Ranges

A time range filter allows the selection of relative time 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 time range filter is analogous to relative ranges in date-time-range filters, with the restriction that the largest possible time unit that can be selected is an hour.

Configuration

To configure a time-of-day range filter, select the Time Range UI control type in the filter definition dialog.

The Value Type of a time-of-day range filter is always Time.

Use the Configuration tab to set a default value and further configuration options.

Condition Selectors

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

Next Range

SELECT *
FROM orders
WHERE {created_at_time=my_time_range.next}

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

• If the selected time-of-day range is a relative range "ending now", the next range starts at the next full hour or minute and ends at the same minute and/or second as now. For example, if the selected time range is "This hour (ending now)" and the current time-of-day is 15:10:30, the next time range starts at 16:00:00 and ends at 16:10:30.

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

Previous Range

SELECT *
FROM orders
WHERE {created_at_time=my_time_range.previous}

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

• If the selected time range is a relative range "ending now", the previous time range is a range starting at the previous hour or minute and ends at the same second as now. For example, if the selected time range is "This hour (ending now)" and the current time-of-day is 15:10:30, the previous time range starts at 14:00:00 and ends at 14:10:30.

• Otherwise the previous time range ends right before the selected time range starts 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 time range.

Range Value

SELECT {my_time_range.value}

The range value selector produces the selected time range as a SQL string literal of the form <ISO8601-time>~<ISO8601-time> for an absolute selected range, for example '15:00:00~15:59:59', or as a SQL string literal representing a relative range selection as specified in Parameters in URLs, for example 'this_hour'.

Cluvio API

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

Range Start

SELECT {my_time_range.start}

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

Time Zone

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

Range End

SELECT {my_time_range.end}

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

Time Zone

Unless a dashboard time zone is configured, the selection of a relative time range produces a SQL expression that represents a time-of-day 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_time <= {my_time_range.end}

Next Range Start

SELECT {my_time_range.next_start}

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

Next Range End

SELECT {my_time_range.next_end}

The next range end selector produces the inclusive end of the next time-of-day range as a database-specific time 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 time range produces a SQL expression that represents a time-of-day 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_time <= {my_time_range.next_end}

Previous Range Start

SELECT {my_time_range.previous_start}

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

Previous Range End

SELECT {my_time_range.previous_end}

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

Sub-second precision

The inclusive pervious 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_time <= {my_time_range.previous_end}