timechart

Type of operation: Aggregate

Description

Bin (in time) and aggregate point or interval table columns through time, based on (optional) grouping columns.

An optional window frame can be specified to compute sliding window aggregation.

When options(bins: <N>) is specified, Observe will pick a user friendly bin size that produces at most N bins across the query window. And when it is set to 1, Observe will produce one single time bin that matches the query window exactly. This option is not accelerable as it is query window dependent.

When a bin_duration is specified, the bins are anchored from Unix epoch. With options(offset: <duration>), the bins are offset from the epoch by duration.

If groupby is not specified, the default grouping will be used. The default grouping for timechart is the set of primary key columns.

Usage

timechart [ options ], [ bin_duration ], [ frame ], [ groupby_1, groupby_2, ... ], groupOrAggregateFunction_1, groupOrAggregateFunction_2, ...

Argument

Type

Optional

Repeatable

Restrictions

options

options

yes

no

constant

bin_duration

duration

yes

no

none

frame

frame

yes

no

constant

groupby

storable

yes

yes

column

groupOrAggregateFunction

expression

no

yes

none

Options

Option

Type

Meaning

bins

int64

Sets the maximum number of bins to produce (not accelerable)

empty_bins

bool

Generate output even for bins in the time window that have no data (not accelerable)

offset

duration

Offset the bin’s start and end times by this amount from Unix epoch. This option can be used only when bin_duration is specified

Accelerable

timechart is sometimes accelerable, depending on options used. A dataset that only uses accelerable verbs can be accelerated, making queries on the dataset respond faster.

Examples

timechart 1h, count: count(1), group_by(server_name)

Group input point table by server name, calculating a count of rows through time per server name per hour, returning a dataset with the 5 columns ‘valid_from’, ‘valid_to’, ‘bucket’, ‘server_name’, and ‘Count’.

timechart 1h, frame(back:24h), count: count(1), group_by(server_name)

Group input point table by server name, calculating a moving count of rows through time per server name per hour, with each count covering the 24 hour window ending at the hour.

timechart options(empty_bins:true), 1h, count: count(1), group_by(server_name)

Similar to the first example, but generate a row with NULL value for each time bin in the query window with no matching input rows. Because of empty_bins, the query may run slowly, especially if the input data points are sparse.

timechart count: count(1), group_by(server_name)

Group the input point table by server name, calculating a count of rows through time per server name per time bin. The time bin size will be determined dynamically to make the chart human-readable. It’s determined based on the query window and a number of other parameters such as chart resolution. This formulation is not accelerable.

timechart options(bins: 100), memory_used: avg(memory_used), group_by(server_name)

Group input table by server name and calculate the average memory usage across time.

The time bin size will be determined dynamically to make the chart human-readable, and it will produce no more than 100 points for each server in the query window.

For example:

  • when query window is 1 hour, 1 minute bins will be produced

  • when query window is 4 hours, 5 minutes bins will be produced

  • when query window is 1 day, 30 minutes bins will be produced

This formulation is not accelerable.

timechart options(bins: 1), count: count(1), group_by(server_name)

Group input point table by server name, calculating the number of rows for each server during the query window.

This formulation is not accelerable.

Aliases

  • bucketize