Date-Time Aggregation

Usage

A Date-Time Aggregation filter is a UI control for selecting a date-time aggregation interval. This is the custom filter equivalent of the built-in aggregation.

A date-time aggregation filter is used to transform a date-time SQL expression for the purpose of grouping result rows and computing an aggregate function.

Syntax

{[<date_time_expr>]:<filter_variable_name>}

The <date_time_expr> can be a date-time, date or time-of-day expression, depending on which aggregation options the filter should support (see configuration).

Like the built-in date-time aggregation filter, a custom date-time aggregation filter is a transformation used to aggregate data over a set of date/time intervals. It is typically used in SQL in both the SELECT and GROUP BY clauses in combination with an aggregate function, as in the following query:

SELECT
// highlight-next-line
  {created_at:aggregation},
  COUNT(*)
FROM orders
GROUP BY 1
ORDER BY 2

For the above query, the database-specific SQL generated by Cluvio looks similar to the following when a user chooses Day aggregation:

SELECT
  DATE_TRUNC('day', (created_at)::timestamp)::date,
  COUNT(*)
FROM orders
GROUP BY 1
ORDER BY 2

The default selection for a custom date-time aggregation filter is Day.

Configuration

To configure a custom date-time aggregation filter, select the Aggregation UI control type in the filter definition dialog.

The Value Type of a custom date-time agggregation filter is always Text, because the selected aggregation is represented as a fixed set of allowed text values by Cluvio.

The Configuration tab allows changing the default aggregation and selecting which aggregations are available in the filter UI. For example, if you use a custom date-time aggregation filter on a DATE column in a report, you would want to remove Hour and Minute from the available options.

Time zones

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

Value Selectors

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

Aggregation Value

SELECT {my_aggregation.value}

The .value selector produces a SQL string literal with the selected aggregation, as defined in Parameters in URLs.

Cluvio API

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