Time series functions

Time series functions are an alpha feature that should be considered preview and subject to change or removal at any time. Alpha features are provided "as is," and are not subject to Imply SLAs.

Time series functions operate on data recorded over a period of time. You can use time series functions to analyze time series data, identify trends and seasonality, fill in gaps, and forecast future values.

Time series aggregation

The TIMESERIES aggregation function generates a time series from raw data. The output is a timeseries object with the following properties:

  • window: The time interval for which the time series is created.
  • timestamps: The list of sorted timestamps.
  • dataPoints: The list of data points associated with the timestamps.
  • bounds: If the overall query time interval is greater than the aggregator window, the bounds object contains the closest data points to the window on each end.
FunctionDescription
TIMESERIES(timeColumn, dataColumn, window, [maxEntries])Creates a time series out of raw data.
  • timeColumn specifies the name of the column that contains the timestamps corresponding to the time series being created.
  • dataColumn specifies the name of the column that contains the data points corresponding to the time series being created.
  • window is an ISO 8601 interval that specifies the time range to consider while creating the time series.
  • Optional: maxEntries controls the maximum number of entries allowed in the time series at any point of time. The default value is 7200.

Time series processing functions

You can use time series processing functions to transform or augment a time series. You can apply time series processing functions to a time series repeatedly and in any suitable order.

Interpolation

Interpolation lets you estimate the values of missing data points at regular intervals in the time series. You define the regular time intervals by segmenting time since Unix epoch (00:00:00 UTC on January 1, 1970) using the bucketPeriod parameter.

The supported interpolation methods include:

  • Linear: Uses linear interpolation to fill the data points for missing timestamps.
  • Padding: Carries forward the last seen value in the time series to the missing timestamps.
  • Backfill: Carries backwards the last seen value in the time series to the missing timestamps.

The following table lists the interpolation functions that you can apply to the timeseries object. The bucketPeriod parameter is an ISO 8601 interval that determines the segment period in which time is chunked for calculating interpolation points.

FunctionDescription
LINEAR_INTERPOLATION(timeseries, bucketPeriod)Creates a time series from the given timeseries object with linear interpolation for missing data points.
PADDING_INTERPOLATION(timeseries, bucketPeriod)Creates a time series from the given timeseries object with padding interpolation for missing data points.
BACKFILL_INTERPOLATION(timeseries, bucketPeriod)Creates a time series from the given timeseries object with backfill interpolation for missing data points.

Time-weighted average

You can calculate time-weighted averages of data points within each regular time interval. You define the regular time intervals by segmenting time since Unix epoch (00:00:00 UTC on January 1, 1970) using the bucketPeriod parameter. Time intervals that have no data points associated with them are not included in the output.

FunctionDescription
TIME_WEIGHTED_AVERAGE(timeseries, interpolator, bucketPeriod)Creates a time series from the given timeseries object with time-weighted average for each bucket point.
  • interpolator specifies the interpolation strategy to fill missing edge points for time intervals. You can supply one of the following values for interpolator: linear, padding, or backfill. See Interpolation for additional details.
  • bucketPeriod is an ISO 8601 interval that determines the segment period in which time is chunked for calculating time-weighted average.

Examples

The following query produces a time series using the raw data values stored in a table named Koalas to the Max:

SELECT count(*) as total_count,
TIMESERIES("__time", "session_length", '2019-08-19T00:00:00.234Z/2019-08-19T23:59:59.877Z', 203000) as timeseries
from "Koalas to the Max"

Click to view the output

{"total_count":202862,
"timeseries":"{
  \"window\":\"2019-08-19T00:00:00.234Z/2019-08-19T23:59:59.877Z\",
  \"timestamps\":[1566172800234,1566172800528,
  1566172800673,1566172801161,1566172802190,1566172802746,1566172802898,
  ...
  1566259198510,1566259198822,1566259198956,1566259199198,1566259199218],
  \"dataPoints\":[15079.0,104299.0,426.0,19160.0,44628.0,20745.0,12621.0,
  30449.0,21937.0,31354.0,22257.0,24265.0,34045.0,310.0,54115.0,1743.0,
  1752.0,610.0,25109.0,19976.0,
  ...
  18108.0,265741.0,52349.0,58323.0,138193.0,70892.0,32893.0,6763.0,44461.0,
  30794.0,8311.0,40725.0,79445.0],
  \"bounds\":{
    \"start\":{\"data\":null,\"timestamp\":null},
    \"end\":{\"data\":null,\"timestamp\":null}}
    }"
}

The following query creates a time series with linear interpolation for missing data points at equal intervals of one minute:

SELECT 
count(*) as total_count,
LINEAR_INTERPOLATION(TIMESERIES("__time", "session_length", '2019-08-19T00:00:00.234Z/2019-08-19T23:59:59.877Z', 203000), 'PT1M') as timeseries
from "Koalas to the Max"

The following query creates a time series with padding interpolation for missing data points at equal intervals of one hour:

SELECT 
count(*) as total_count,
PADDING_INTERPOLATION(TIMESERIES("__time", "session_length", '2019-08-19T00:00:00.234Z/2019-08-19T23:59:59.877Z', 203000), 'PT1H') as timeseries
from "Koalas to the Max"

The following query creates a times series with time-weighted average for each bucket point:

SELECT 
count(*) as total_count,
TIME_WEIGHTED_AVERAGE(TIMESERIES("__time", "session_length", '2019-08-19T00:00:00.234Z/2019-08-19T23:59:59.877Z', 203000), 'backfill', 'PT1M') as timeseries
from "Koalas to the Max"

Learn more

See the following topics for more information:

← OverviewOverview →