Skip to main content

Time series functions (beta)

info

Time series functions are an Imply Polaris beta feature. You should only enable a beta feature after testing in a staging environment. For more information, see Preview features.

The bucketMillis parameter is being removed

The bucketMillis parameter has been deprecated and will be removed at the end of March 2024. Instead of the bucketMillis parameter, use the timeProperties object. It provides more detailed information about the time properties, including bucket period, timezone, and origin.

Time series functions operate on data recorded over a period of time. You use time series functions to analyze time series data, identify trends and seasonality, interpolate values, and load extra time periods to fill in boundary values.

You can include time series functions in the SQL workbench or in the query of the request to the Query API.

Time series aggregation

The following table lists the time series aggregation functions that you can use to generate a binary representation of the time series.

FunctionDescription
DOWNSAMPLED_SUM_TIMESERIES(timeColumn, dataColumn, window, timeProperties [, maxEntries])Creates a time series by downsampling recordings based on the bucket period specified in timeProperties. Data points are downsampled by addition. For each bucket, the timestamp corresponds to the start time of the bucket and the data point represents the added values.
  • timeColumn: Name of the column that contains the timestamps corresponding to the time series being created. timeColumn can be a numeric or a timestamp column.
  • dataColumn: Name of the column that contains the data points corresponding to the time series being created.
  • window: ISO 8601 interval that restricts the time range over which the time series is created.
  • timeProperties: List of time properties for the aggregation separated by semicolons. The first property is an ISO 8601 period which divides the recordings into time buckets. The second property, timeZone, is the timezone of the timestamps in the time series. The third property, origin, is the starting time of the bucketing. Both timeZone and origin are optional parameters. The following example shows all three parameters: P1Y;IST;1999-12-31T01:00:00.000.
  • maxEntries: Maximum number of entries allowed in the time series at any point in time. maxEntries defaults to the number of buckets possible given the window parameter and the bucket period.
DOWNSAMPLED_SUM_TIMESERIES(timeseriesColumn, window, timeProperties [, maxEntries])Creates a time series by downsampling a column of time series objects based on the bucket period specified in timeProperties. Data points are downsampled by addition. For each time bucket, the timestamp corresponds to the start time of the bucket and the data point represents the added values.
  • timeseriesColumn: Name of the column that contains the time series objects.
  • window: ISO 8601 interval that restricts the time range over which the time series is created.
  • timeProperties: List of time properties for the aggregation separated by semicolons. The first property is an ISO 8601 period which divides the recordings into time buckets. The second property, timeZone, is the timezone of the timestamps in the time series. The third property, origin, is the starting time of the bucketing. Both timeZone and origin are optional parameters. The following example shows all three parameters: P1Y;IST;1999-12-31T01:00:00.000.
  • maxEntries: Maximum number of entries allowed in the time series at any point in time. maxEntries defaults to the number of buckets possible given the window parameter and the bucket period.
TIMESERIES(timeColumn, dataColumn, window [, maxEntries])Creates a time series from raw data.
  • timeColumn: Name of the column that contains the timestamps corresponding to the time series being created. timeColumn can be a numeric or a timestamp column.
  • dataColumn: Name of the column that contains the data points corresponding to the time series being created.
  • window: ISO 8601 interval that restricts the time range over which the time series is created.
  • maxEntries: Maximum number of entries allowed in the time series at any point in time. maxEntries defaults to 7200.
TIMESERIES(timeseriesColumn, window [, maxEntries])Creates a time series from merging a column of time series objects.
  • timeseriesColumn: Name of the column that contains the time series objects.
  • window: ISO 8601 interval that restricts the time range over which the time series is created.
  • maxEntries: Maximum number of entries allowed in the time series at any point in time. maxEntries defaults to 7200.
SUM_TIMESERIES(timeseriesColumn, window [, maxEntries])Creates a time series by taking a column of time series objects and adding their data points for the same timestamp. The time series objects must have the same timestamps and window period.
  • timeseriesColumn: Name of the column that contains the time series objects.
  • window: ISO 8601 interval that restricts the time range over which the time series is created.
  • maxEntries: Maximum number of entries allowed in the time series at any point in time. maxEntries defaults to 260_000.

To convert the binary output to JSON format, wrap the outermost function with the TIMESERIES_TO_JSON function. The output is a time series object with the following properties:

  • window: ISO 8601 time interval for which the time series is created.
  • timestamps: List of sorted timestamps.
  • dataPoints: List of data points associated with the timestamps.
  • timeProperties: Time properties object that contains ISO period, timeZone, and origin parameters.
  • bucketMillis: Deprecated. Time interval in milliseconds that determines how to segment the data.
  • 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.

Examples

DOWNSAMPLED_SUM_TIMESERIES

The following query produces a time series by downsampling recordings based on the 2019-08-25T00:00:00.031Z/2019-08-25T00:05:46.517Z period. The resulting output is in JSON format.

SELECT TIMESERIES_TO_JSON(DOWNSAMPLED_SUM_TIMESERIES("__time", "session_length", '2019-08-19T00:00:00.234Z/2019-08-19T23:59:59.877Z', 'PT1M')) as downsampled_timeseries
FROM "Koalas to the Max"
View the results
{
"downsampled_timeseries":{
"window":"2019-08-19T00:00:00.234Z/2019-08-19T23:59:59.877Z",
"timestamps":[
1566172800000,1566172860000,1566172920000,1566172980000,1566173040000,1566173100000,1566173160000,1566173220000,1566173280000,1566173340000,1566173400000,1566173460000,1566173520000,1566173580000,1566173640000,1566173700000,1566173760000,1566173820000,
...
1566258000000,1566258060000,1566258120000,1566258180000,1566258240000,1566258300000,1566258360000,1566258420000,1566258480000,1566258540000,1566258600000,1566258660000,1566258720000,1566258780000,1566258840000,1566258900000,1566258960000,1566259020000,1566259080000,1566259140000
],
"dataPoints":[
4342102.0,6799755.0,1.5951734E7,8491739.0,9520073.0,8127726.0,6219452.0,4783010.0,7.5452827E7,9363897.0,6134945.0,6603625.0,6723617.0,8909703.0,6024551.0,4110716.0,4771525.0,2430190.0,1999305.0,1.327893E7,1.3758501E7,9693550.0,8530676.0,1.15453733E8,1.67724155E8,1.3165745E8,
...
2.6223013E7,3.1531803E7,6.7241171E7,2.0335366E7,4.0052009E7,1.9367057E7,2.126301E7,2.4467079E7,2.4874429E7,5.0194879E7,5.654094E7,2.8103319E7,6.3475976E7,4.6137679E7,9.7408696E7,5.0078246E7,7.585225E7,3.1495826E7,2.3159649E7,2.7507422E7,2.5954539E7,3.6406193E7,2.4787654E7
],
"timeProperties":{
"period":"PT1M",
"origin":"1970-01-01T00:00:00.000Z",
"timeZone":"UTC"
},
"bucketMillis":60000,
"bounds":{
"start":{
"data":null,
"timestamp":null
},
"end":{
"data":596.0,
"timestamp":1566259199877
}
}
}
}

TIMESERIES

The following query produces a time series using the raw data values stored in the table named Koalas to the Max. The resulting output is in JSON format.

SELECT count(*) as total_count,
TIMESERIES_TO_JSON(TIMESERIES("__time", "session_length", '2019-08-19T00:00:00.234Z/2019-08-19T23:59:59.877Z', 203000)) as timeseries_json
FROM "Koalas to the Max"
View the results
{
"total_count":505431,
"timeseries_json":{
"window":"2019-08-19T00:00:00.234Z/2019-08-19T23:59:59.877Z",
"timestamps":[
1566172800528,1566172800673,1566172801161,1566172802190,1566172802746,1566172802898,1566172803363,1566172803950,1566172804257,1566172804277,1566172806439,1566172806973,1566172807599,1566172808060,1566172808639,1566172808743,1566172809378,1566172810073,1566172810258,1566172810583,1566172810620,1566172810628,1566172811236,1566172811608,
...
1566259194247,1566259194530,1566259194578,1566259194770,1566259195158,1566259195245,1566259195692,1566259195993,1566259196571,1566259196616,1566259196692,1566259197087,1566259197604,1566259197757,1566259197932,1566259198509,1566259198510,1566259198822,1566259198956,1566259199198,1566259199218
],
"dataPoints":[
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,209.0,3149.0,3158.0,4357.0,53974.0,54457.0,1686.0,1714.0,28420.0,56605.0,58492.0,65947.0,297716.0,63330.0,114037.0,114584.0,174546.0,40607.0,41207.0,68808.0,74171.0,120331.0,75215.0,
...
22640.0,392121.0,4156.0,4164.0,108702.0,150.0,80517.0,95.0,211217.0,107111.0,24639.0,37388.0,53994.0,1186.0,163499.0,1192.0,2930.0,2937.0,5937.0,25256.0,40199.0,70814.0,2833.0,26741.0,30111.0,75477.0,75910.0,27301.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
],
"timeProperties":null,
"bucketMillis":null,
"bounds":{
"start":{
"data":null,
"timestamp":null
},
"end":{
"data":596.0,
"timestamp":1566259199877
}
}
}
}

Time series processing functions

The following table lists the time series processing functions that you can use to transform or augment a time series. You can apply these functions to a time series repeatedly and in any suitable order.

FunctionDescription
ADD_TIMESERIES(timeseries1, timeseries2 [, shouldNullPoison])Adds the data points in the two time series objects and returns a new time series. The two provided time series must have identical timestamps.
shouldNullPoison sets the output to null if any of the input time series are null. shouldNullPoison defaults to false.
DELTA_TIMESERIES(timeseries [, bucketPeriod])Returns a new time series which contains delta differences between the data points in the input time series. You can use the DELTA_TIMESERIES function to analyze counter data (an always increasing numeric metric) and determine the rate of increase of the counter value over time.
bucketPeriod is an ISO 8601 period through which the deltas are added up (or folded) within a time bucket. bucketPeriod defaults to 1 millisecond.
DIVIDE_TIMESERIES(timeseries1, timeseries2 [, shouldNullPoison])Divides the data points in the two time series objects and returns a new time series. The two provided time series must have identical timestamps.
shouldNullPoison sets the output to null if any of the input time series are null. shouldNullPoison defaults to false. The function returns a non-null time series if the first time series is non-null and the second time series is null. If the denominator data point in any division is 0, the new data point for the divided time series is a NaN value.
MAX_OVER_TIMESERIES(timeseries)Returns the largest data point value in the input time series.
MAP_TIMESERIES(timeseries, mapExpr)Returns a new time series by mapping the data points of the input time series through mapExpr.
mapExpr takes a Druid SQL expression as a string argument and applies it to each value of the time series. You can reference the (time, datapoint) tuple in a time series using (timestamp, value) variables in the mapExpr. An invocation like MAP_TIMESERIES(timeseries, 'pow(value, 2) + timestamp') changes every data point in the time series to pow(value, 2) + timestamp where value is the current data point value and timestamp is the millisecond time of that entry's recording.
MULTIPLY_TIMESERIES(timeseries1, timeseries2)Multiplies the data points in the two time series objects and returns a new time series. The two provided time series must have identical timestamps.
QUANTILE_OVER_TIMESERIES(timeseries, percentile)Computes a quantile value over the data points in the input time series.
percentile is a percentile between 0 and 1 which determines the quantile to be computed.
SUBTRACT_TIMESERIES(timeseries1, timeseries2 [, shouldNullPoison])Subtracts the data points in the two time series objects and returns a new time series. The two provided time series must have identical timestamps.
shouldNullPoison sets the output to null if any of the input time series are null. shouldNullPoison defaults to false.The function returns a non-null time series if the first time series is non-null and the second time series is null.
SUM_OVER_TIMESERIES(timeseries, window [, maxEntries])Returns a double value which is the sum over the data points of the input time series.
  • window: ISO 8601 interval that specifies the time range to consider.
  • maxEntries: Maximum number of entries allowed in the time series at any point in time. maxEntries defaults to 260000.
TIMESERIES_ATTACH_META(timeseries, metadataKey, metadataValue)Merges the provided metadata property to a time series. metadataKey accepts period as ISO period, timeZone as string, and origin as datetime.
TIMESERIES_CLEAR_META(timeseries [, metadataKey]Clears the provided metadata property from a time series. metadataKey accepts period as ISO period, timeZone as string, and origin as datetime. If no argument is provided, all three metadata properties are cleared from the time series object.
TIMESERIES_TO_JSON(timeseries)Outputs time series data in JSON format.
TIMESERIES_SIZE(timeseries)Computes the size of the input time series.

Interpolation

Interpolation functions let you estimate the values of missing data points at regular intervals in the time series.

You define the regular time intervals by segmenting the time since Unix epoch (00:00:00 UTC on January 1, 1970) using the bucketPeriod parameter. The bucketPeriod parameter is an ISO 8601 period that determines the segment period in which time is chunked for calculating interpolation points.

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 time series object. Note that the boundary version of a function retains only the entries within the specified range, discarding the rest. The interpolation version of a function preserves all original entries.

FunctionDescription
LINEAR_INTERPOLATION(timeseries, bucketPeriod)Creates a time series from the input time series object with linear interpolation for missing data points. Retains all the original entries of the input time series.
LINEAR_BOUNDARY(timeseries, bucketPeriod)Creates a time series from the input time series object with linear interpolation for missing data points. The resulting time series contains recordings at time bucket boundaries, discarding the rest of the entries.
PADDING_INTERPOLATION(timeseries, bucketPeriod)Creates a time series from the input time series object with padding interpolation for missing data points. Retains all the original entries of the input time series.
PADDED_BOUNDARY(timeseries, bucketPeriod)Creates a time series from the input time series object with padding interpolation for missing data points. The resulting time series contains recordings at time bucket boundaries, discarding the rest of the entries.
BACKFILL_INTERPOLATION(timeseries, bucketPeriod)Creates a time series from the input time series object with backfill interpolation for missing data points. Retains all the original entries of the input time series.
BACKFILL_BOUNDARY(timeseries, bucketPeriod)Creates a time series from the input time series object with backfill interpolation for missing data points. The resulting time series contains recordings at time bucket boundaries, discarding the rest of the entries.

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 time series object with time-weighted average for each bucket point.
  • interpolator: Interpolation strategy to fill missing edge points for time intervals. interpolator accepts the following values: linear, padding, or backfill. See Interpolation for additional details.
  • bucketPeriod: ISO 8601 period that determines the segment period in which time is chunked for calculating time-weighted average.

Examples

This section contains examples of time series processing functions.

DELTA_TIMESERIES

The following query creates a time series that contains delta differences between the data points. The resulting output is in JSON format.

SELECT count(*) as total_count,
TIMESERIES_TO_JSON(DELTA_TIMESERIES(TIMESERIES("__time", "session_length", '2019-08-19T00:00:00.234Z/2019-08-19T23:59:59.877Z', 203000))) as delta
FROM "Koalas to the Max"
View the results
{
"total_count":505431,
"delta":{
"window":"2019-08-19T00:00:00.234Z/2019-08-19T23:59:59.877Z",
"timestamps":[
1566172800528,1566172801161,1566172802190,1566172803363,1566172804257,1566172806439,1566172806973,1566172808060,1566172808743,1566172810073,1566172810620,1566172810628,1566172811236,1566172811608,1566172812020,1566172812108,1566172813384,1566172814168,1566172816126,1566172819890,1566172820757,1566172822345,1566172822478,1566172824205,
...
1566259192032,1566259192297,1566259192312,1566259192490,1566259192673,1566259192806,1566259192963,1566259193583,1566259193674,1566259193808,1566259193949,1566259194247,1566259194530,1566259194770,1566259195158,1566259195245,1566259195692,1566259196616,1566259197087,1566259197604,1566259198510,1566259199198
],
"dataPoints":[
18734.0,25468.0,17828.0,9417.0,2008.0,9780.0,53805.0,9.0,24499.0,2940.0,9.0,1199.0,49617.0,483.0,28.0,26706.0,28185.0,1887.0,7455.0,231769.0,50707.0,547.0,59962.0,600.0,27601.0,5363.0,46160.0,122878.0,77548.0,14.0,124002.0,24028.0,22070.0,1285.0,23833.0,99065.0,25268.0,86077.0,61100.0,3091.0,59578.0,2830.0,89169.0,37125.0,7.0,3619.0,58409.0,138749.0,38717.0,
...
1445426.0,59580.0,93485.0,34391.0,612478.0,9.0,18199.0,47.0,39868.0,208198.0,22718.0,23654.0,22422.0,7380.0,6713.0,369481.0,8.0,104538.0,80367.0,211122.0,12749.0,16606.0,162313.0,1738.0,7.0,3000.0,19319.0,14943.0,30615.0,23908.0,3370.0,45366.0,433.0,247633.0,5974.0,79870.0,37698.0,32414.0,38720.0
],
"timeProperties":{
"period":"PT0.001S",
"origin":null,
"timeZone":"UTC"
},
"bucketMillis":1,
"bounds":{
"start":{
"data":null,
"timestamp":null
},
"end":{
"data":null,
"timestamp":null
}
}
}
}

LINEAR_INTERPOLATION

The following query creates a time series with linear interpolation for missing data points at equal intervals of one hour. The resulting output is in JSON format.

SELECT count(*) as total_count,
TIMESERIES_TO_JSON(LINEAR_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"
View the results
{
"total_count":505431,
"timeseries":{
"window":"2019-08-19T00:00:00.234Z/2019-08-19T23:59:59.877Z",
"timestamps":[
1566172800528,1566172800673,1566172801161,1566172802190,1566172802746,1566172802898,1566172803363,1566172803950,1566172804257,1566172804277,1566172806439,1566172806973,1566172807599,1566172808060,1566172808639,1566172808743,1566172809378,1566172810073,1566172810258,1566172810583,1566172810620,1566172810628,1566172811236,1566172811608,
...
1566259194578,1566259194770,1566259195158,1566259195245,1566259195692,1566259195993,1566259196571,1566259196616,1566259196692,1566259197087,1566259197604,1566259197757,1566259197932,1566259198509,1566259198510,1566259198822,1566259198956,1566259199198,1566259199218
],
"dataPoints":[
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,209.0,3149.0,3158.0,4357.0,53974.0,54457.0,1686.0,1714.0,28420.0,56605.0,58492.0,65947.0,297716.0,63330.0,114037.0,114584.0,174546.0,40607.0,41207.0,68808.0,74171.0,120331.0,75215.0,
...
22640.0,392121.0,4156.0,4164.0,108702.0,150.0,80517.0,95.0,211217.0,107111.0,24639.0,37388.0,53994.0,1186.0,163499.0,1192.0,2930.0,2937.0,5937.0,25256.0,40199.0,70814.0,2833.0,26741.0,30111.0,75477.0,75910.0,27301.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
],
"timeProperties":{
"period":"PT0.001S",
"origin":null,
"timeZone":"UTC"
},
"bucketMillis":1,
"bounds":{
"start":{
"data":null,
"timestamp":null
},
"end":{
"data":596.0,
"timestamp":1566259199877
}
}
}
}

MAP_TIMESERIES

The following query returns a new times series by mapping sqrt square root function over the data points in the input time series:

SELECT count(*) as total_count,
TIMESERIES_TO_JSON(MAP_TIMESERIES(TIMESERIES("__time", "session_length", '2019-08-19T00:00:00.234Z/2019-08-19T23:59:59.877Z', 203000), 'sqrt(value)')) as sqrt_timeseries
FROM "Koalas to the Max"
View the results
{
"total_count":505431,
"sqrt_timeseries":{
"window":"2019-08-19T00:00:00.234Z/2019-08-19T23:59:59.877Z",
"timestamps":[
1566172800528,1566172800673,1566172801161,1566172802190,1566172802746,1566172802898,1566172803363,1566172803950,1566172804257,1566172804277,1566172806439,1566172806973,1566172807599,1566172808060,1566172808639
...
1566259195692,1566259195993,1566259196571,1566259196616,1566259196692,1566259197087,1566259197604,1566259197757,1566259197932,1566259198509,1566259198510,1566259198822,1566259198956,1566259199198,1566259199218
],
"dataPoints":[
322.95355703258633,20.639767440550294,138.4196517839862,211.25340233946528,144.03124660989366,112.34322409473569,174.49641830135081,148.11144452742334,177.0706073858674,149.18780110987626,155.77226967596,184.51287217969374,17.60681686165901,
...
165.23014252853503,134.56596895203484,515.5007274485653,228.79903845951802,241.501552790039,371.74319092620914,266.2555163747786,181.36427432104702,82.23746105030237,210.85777196963835,175.4821928287882,91.16468614545876,201.80436070610565,281.8598942737331
],
"timeProperties":null,
"bucketMillis":null,
"bounds":{
"start":{
"data":null,
"timestamp":null
},
"end":{
"data":596.0,
"timestamp":1566259199877
}
}
}
}

MAX_OVER_TIMESERIES

The following query returns the largest data point value in the time series:

SELECT count(*) as total_count,
MAX_OVER_TIMESERIES(TIMESERIES("__time", "session_length", '2019-08-19T00:00:00.234Z/2019-08-19T23:59:59.877Z', 203000)) as largest_data_point
FROM "Koalas to the Max"
View the results
{
"total_count":505431,
"largest_data_point":579090884
}

PADDING_INTERPOLATION

The following query creates a time series with padding interpolation for missing data points at equal intervals of one hour. The resulting output is in JSON format.

SELECT count(*) as total_count,
TIMESERIES_TO_JSON(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"
View the results
{
"total_count":505431,
"timeseries":{
"window":"2019-08-19T00:00:00.234Z/2019-08-19T23:59:59.877Z",
"timestamps":[
1566172800528,1566172800673,1566172801161,1566172802190,1566172802746,1566172802898,1566172803363,1566172803950,1566172804257,1566172804277,1566172806439,1566172806973,1566172807599,1566172808060,1566172808639,1566172808743,1566172809378,1566172810073,
...
1566259194770,1566259195158,1566259195245,1566259195692,1566259195993,1566259196571,1566259196616,1566259196692,1566259197087,1566259197604,1566259197757,1566259197932,1566259198509,1566259198510,1566259198822,1566259198956,1566259199198,1566259199218
],
"dataPoints":[
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,209.0,3149.0,3158.0,4357.0,53974.0,54457.0,1686.0,1714.0,28420.0,56605.0,58492.0,65947.0,297716.0,63330.0,114037.0,114584.0,174546.0,
...
95.0,211217.0,107111.0,24639.0,37388.0,53994.0,1186.0,163499.0,1192.0,2930.0,2937.0,5937.0,25256.0,40199.0,70814.0,2833.0,26741.0,30111.0,75477.0,75910.0,27301.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
],
"timeProperties":{
"period":"PT0.001S",
"origin":null,
"timeZone":"UTC"
},
"bucketMillis":1,
"bounds":{
"start":{
"data":null,
"timestamp":null
},
"end":{
"data":596.0,
"timestamp":1566259199877
}
}
}
}

QUANTILE_OVER_TIMESERIES

The following query calculates the 50th percentile value over the data points in the time series:

SELECT count(*) as total_count,
QUANTILE_OVER_TIMESERIES(TIMESERIES("__time", "session_length", '2019-08-19T00:00:00.234Z/2019-08-19T23:59:59.877Z', 203000), 0.5) as quantile
FROM "Koalas to the Max"
View the results
{
"total_count":505431,
"quantile":32822
}

TIMESERIES_ATTACH_META

The following query merges the provided timeZone property to the time series. The resulting output shows the timeZone property set to Asia/Kolkata instead of the default UTC.

SELECT TIMESERIES_TO_JSON(TIMESERIES_ATTACH_META(TIMESERIES("__time", "session_length", '2019-08-19T00:00:00.234Z/2019-08-19T23:59:59.877Z', 203000), 'timeZone', 'IST')) as timeseries_meta
FROM "Koalas to the Max"
View the results
{
"timeseries_meta":{
"window":"2019-08-19T00:00:00.234Z/2019-08-19T23:59:59.877Z",
"timestamps":[
1566172800528,1566172800673,1566172801161,1566172802190,1566172802746,1566172802898,1566172803363,1566172803950,1566172804257,1566172804277,1566172806439,1566172806973,1566172807599,1566172808060,1566172808639,1566172808743,1566172809378,1566172810073,
...
1566259196571,1566259196616,1566259196692,1566259197087,1566259197604,1566259197757,1566259197932,1566259198509,1566259198510,1566259198822,1566259198956,1566259199198,1566259199218
],
"dataPoints":[
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,209.0,3149.0,3158.0,4357.0,53974.0,54457.0,1686.0,1714.0,28420.0,56605.0,58492.0,65947.0,297716.0,63330.0,
...
2937.0,5937.0,25256.0,40199.0,70814.0,2833.0,26741.0,30111.0,75477.0,75910.0,27301.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
],
"timeProperties":{
"period":null,
"origin":null,
"timeZone":"Asia/Kolkata"
},
"bucketMillis":null,
"bounds":{
"start":{
"data":null,
"timestamp":null
},
"end":{
"data":596.0,
"timestamp":1566259199877
}
}
}
}

TIMESERIES_SIZE

The following query computes the size of the time series:

SELECT count(*) as total_count,
TIMESERIES_SIZE(TIMESERIES("__time", "session_length", '2019-08-19T00:00:00.234Z/2019-08-19T23:59:59.877Z', 203000)) as time_series_size
FROM "Koalas to the Max"
View the results
{
"total_count":505431,
"time_series_size":202860
}

TIME_WEIGHTED_AVERAGE

The following query creates a times series with time-weighted average for each bucket point. The resulting output is in JSON format.

SELECT count(*) as total_count,
TIMESERIES_TO_JSON(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"
View the results
{
"total_count":505431,
"timeseries":{
"window":"2019-08-19T00:00:00.234Z/2019-08-19T23:59:59.877Z",
"timestamps":[
1566172860000,1566172920000,1566172980000,1566173040000,1566173100000,1566173160000,1566173220000,1566173280000,1566173340000,1566173400000,1566173460000,1566173520000,1566173580000,1566173640000,1566173700000,1566173760000,1566173820000,1566173880000,
...
1566258420000,1566258480000,1566258540000,1566258600000,1566258660000,1566258720000,1566258780000,1566258840000,1566258900000,1566258960000,1566259020000,1566259080000,1566259140000],
"dataPoints":[
60661.208183333336,256822.28623333335,78493.2697,84797.42005,83089.56221666667,93242.89868333333,55477.63733333333,678886.6576333334,84828.26483333333,62037.23965,89680.16408333334,50320.28748333333,69950.59883333334
...
46773333334,147223.4491,174239.39256666668,83126.1091,153152.88816666667,63640.22588333333,288866.9824166667,179143.40398333332,260116.72173333334,131835.1912,62802.97078333333,82252.3607,94091.53451666667,118883.66955,63952.626033333334
],
"timeProperties":{
"period":"PT1M",
"origin":null,
"timeZone":"UTC"
},
"bucketMillis":60000,
"bounds":{
"start":{
"data":49297.86866666667,
"timestamp":1566172800000
},
"end":{
"data":null,
"timestamp":null
}
}
}
}

Learn more

See the following topics for more information: