Skip to main content

Built-in Aggregation

Usage​

Date-Time AggregationDate-Time Aggregation

The built-in date-time aggregation filter is a transformation used to aggregate data over a set of predefined 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
Syntax
{[<date-time-expr>]:aggregation}

where <date-time-expr> is a date-time SQL expression (e.g. a column of type TIMESTAMP in PostgreSQL).

The generated SQL for the built-in aggregation filter is database-specific. Below is the generated SQL of the above query for a PostgreSQL database when aggregating by Day.

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

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

Configuration​

By default, the aggregation control on a dashboard would allow selecting from these options: Minute, Hour, Day, Week, Month, Quarter, Year. The Week option would aggregate to weeks starting Monday or Sunday, depending on the configured week start in the organization preferences.

You can change the options available for a particular dashboard in the Dashboard settings

note

The custom filter variant of Date-Time Aggregation filter offers the same functionality with more configuration options.

Time zones​

The built-in aggregation automatically applies time zone conversion, when time zone is configured on the report's dashboard. For more details see the Time Zones Overview.

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 built-in aggregation filter, for example in URLs generated in the SQL result. See also Parameters in URLs.