Time series functions (beta)

info

Time series functions are a beta feature. You should only enable a beta feature after testing in a staging environment.

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.

All examples in this topic use the kttm-v2-2019-08-25.json.gz dataset, which comes bundled with Imply's distribution of Apache Druid®. To load this dataset, open the Druid console and click Load data > Batch-SQL > Example data. Select KoalasToTheMax one day from the list of datasets.

Setup

Time series functions are disabled by default. Enable this feature by loading the imply-timeseries extension.

To load the extension, go to the cluster's Setup page and expand the Advanced config section. Click Add custom extension and enter imply-timeseries in the Name field. You do not need to provide the URL or the path to the extension file because it is included with Imply's distribution of Apache Druid. See Imply bundled extensions for more information.

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. Some of these functions are overloaded and can accept different types or numbers of parameters.

Function	Description
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.
LATEST_TIMESERIES(timeColumn, dataColumn, versionColumn, window, timeBucket [, maxEntries])	Creates a time series with bucketed timestamps and latest data points for each bucket using the ISO 8601 period specified in timeBucket. For each bucket, Druid selects the data point from dataColumn with the highest value in versionColumn. 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. versionColumn: Determines which data point to keep when multiple points fall into the same bucket. If two or more points occur in the same time bucket, Druid uses the value in versionColumn to identify the latest version for the data point. window: ISO 8601 interval that restricts the time range over which the time series is created. timeBucket: ISO 8601 period that specifies how to separate timestamps into buckets. The bucket period should be smaller than the window period. maxEntries: Maximum number of entries allowed in the time series at any point in time. maxEntries defaults to 7200.
LATEST_TIMESERIES_TO_TIMESERIES(latestTimeseries)	Takes a time series generated by LATEST_TIMESERIES and converts it into a standard time series format for further processing.
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. Use timeProperties instead.
• 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 creates 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-25T00:00:00.031Z/2019-08-25T00:05:46.517Z', 'PT1M')) as timeseries_json
FROM "kttm-v2-2019-08-25"

View the results

{
  "timeseries_json":{
    "window":"2019-08-25T00:00:00.031Z/2019-08-25T00:05:46.517Z",
    "timestamps":[1566691200000,1566691260000,1566691320000,1566691380000,1566691440000,1566691500000],
    "dataPoints":[3.40854674E8,3.3897496E8,6.2464025E7,2.2769079E7,1.3296615E7,7.4411089E7],
    "timeProperties":{
      "period":"PT1M",
      "origin":"1970-01-01T00:00:00.000Z",
      "timeZone":"UTC"
    },
    "bucketMillis":60000,
    "bounds":{
      "start":{
        "data":null,
        "timestamp":null
      },
      "end":{
        "data":91579.0,
        "timestamp":1566691546517
      }
    }
  }
}

LATEST_TIMESERIES_TO_TIMESERIES

The following query creates a time series with bucketed timestamps and latest data points. It then converts the result into a standard time series object. The resulting output is in JSON format.

SELECT TIMESERIES_TO_JSON(LATEST_TIMESERIES_TO_TIMESERIES(LATEST_TIMESERIES("__time", "session_length", "number", '2019-08-25T00:00:00.031Z/2019-08-25T00:05:46.517Z', 'PT1S', 100000))) 
  AS "latest_timeseries"
FROM "kttm-v2-2019-08-25"

View the results

{
  "latest_timeseries": {
    "window": "2019-08-25T00:00:00.031Z/2019-08-25T00:05:46.517Z",
    "timestamps":[
      1566691200000,1566691201000,1566691202000,1566691203000,1566691204000,1566691205000,1566691206000,1566691207000,1566691208000,1566691209000,1566691211000,1566691212000,1566691213000,1566691214000,1566691215000,1566691216000,1566691217000,1566691218000,1566691219000,1566691220000,1566691221000,1566691222000,1566691223000,1566691224000,1566691225000,1566691226000,1566691227000,1566691228000,
      ...
      1566691513000,1566691514000,1566691515000,1566691516000,1566691517000,1566691518000,1566691519000,1566691520000,1566691521000,1566691522000,1566691523000,1566691525000,1566691526000,1566691527000,1566691528000,1566691529000,1566691531000,1566691532000,1566691533000,1566691534000,1566691535000,1566691536000,1566691537000,1566691538000,1566691539000,1566691540000,1566691542000,1566691543000,1566691544000,1566691545000,1566691546000
    ],
    "dataPoints":[
      252689.0,79764.0,49275.0,82064.0,31420.0,52173.0,82476.0,1376342.0,13215.0,56549.0,144642.0,1070453.0,60384.0,120415.0,91296.0,78095.0,150703.0,65760.0,64861.0,67577.0,209689.0,69473.0,50595.0,28747.0,53138.0,215462.0,133380.0,280708.0,56610.0,148385.0,348331.0,1785925.0,94468.0,38621.0,45364.0,113412.0,143500.0,1408059.0,145138.0,101146.0,229547.0,119754.0,47948.0,233048.0,92154.0,163538.0,
      ...
      1347358.0,424140.0,52.0,37089.0,72290.0,39440.0,218528.0,61419.0,237479.0,64602.0,44592.0,66133.0,81046.0,3631.0,244042.0,1363246.0,78129.0,1364604.0,52567.0,74045.0,75335.0,75751.0,51349.0,77876.0,79183.0,1910.0,80583.0,18301.0,90067.0,97284.0,259899.0,85669.0,87491.0,525000.0,15292.0,27149.0,92889.0,67143.0,100567.0,94464.0,97192.0,655316.0,25805.0,4819.0,248760.0,28622.0,24361.0,83605.0,118553.0,543512.0,108336.0,1401934.0,420991.0,37563.0,2.026504E7
    ],
    "timeProperties": {
      "period": "PT1S",
      "origin": null,
      "timeZone": "UTC"
    },
    "bucketMillis": 1000,
    "bounds": {
      "start": {
        "data": null,
        "timestamp": null
      },
      "end": {
        "data": null,
        "timestamp": null
      }
    }
  }
}

TIMESERIES

The following query creates a time series using the raw data values stored in the table named kttm-v2-2019-08-25. The resulting output is in JSON format.

SELECT count(*) as total_count,
TIMESERIES_TO_JSON(TIMESERIES("__time", "session_length", '2019-08-25T00:00:00.031Z/2019-08-25T00:05:46.517Z', 203000)) as timeseries_json
FROM "kttm-v2-2019-08-25"

View the results

{
  "total_count": 465346,
  "timeseries_json": {
    "window":"2019-08-25T00:00:00.031Z/2019-08-25T00:05:46.517Z",
    "timestamps":[
      1566691200031,1566691200059,1566691200178,1566691200965,1566691201241,1566691201858,1566691202501,1566691202525,1566691202688,1566691203546,1566691204014,1566691205170,1566691205223,1566691205379,1566691205833,1566691206205,1566691206208,1566691206250,1566691206550,1566691206977,1566691207544,1566691207587,1566691207611,1566691207747,
      ...
      1566691544969,1566691545010,1566691545586,1566691546054,1566691546361
    ],
    "dataPoints":[
      76261.0,252689.0,1753602.0,62093.0,79764.0,6264.0,8070.0,8078.0,49275.0,82064.0,31420.0,15024.0,17501.0,52173.0,149.0,52950.0,10613.0,82476.0,19424.0,19253.0,1376342.0,68713.0,315220.0,315250.0,102294.0,13215.0,21133.0,56008.0,198372.0,56549.0,144642.0,53529.0,49.0,39488.0,57443.0,1070453.0,2487.0,2302.0,17963.0,60384.0,2307.0,43
      ...
      1401934.0,30915.0,420991.0,278.0,6217.0,37563.0,2.026504E7,1028.0
    ],
    "timeProperties":null,
    "bucketMillis":null,
    "bounds":{
      "start":{
        "data":null,
        "timestamp":null
      },
      "end":{
        "data":91579.0,
        "timestamp":1566691546517
      }
    }
  }
}

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.

Function	Description
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.
FILTER_TIMESERIES(timeseries, filterExpr)	Filters the input time series by filterExpr and returns a new time series. filterExpr is a Druid SQL expression in string format. In filterExpr, you can reference the time series data by using timestamp for time values and value for data points.
FIRST_IN_TIMESERIES(timeseries)	Returns the first data point value in the input time series.
LAST_IN_TIMESERIES(timeseries)	Returns the last 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 is a Druid SQL expression in string format. In mapExpr, you can reference the time series data by using timestamp for time values and value for data points. 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.
MAX_OVER_TIMESERIES(timeseries)	Returns the largest data point value in the input time series.
MIN_OVER_TIMESERIES(timeseries)	Returns the smallest data point value in the input time series.
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)	Returns a double value which is the sum over the data points of the input time series.
AVG_OVER_TIMESERIES(timeseries)	Returns a double value which is the average over the data points of the input time series.
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.

Function	Description
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.

Function

Description

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-25T00:00:00.031Z/2019-08-25T00:05:46.517Z', 203000))) as delta
FROM "kttm-v2-2019-08-25"

View the results

{
  "total_count":465346,
  "delta":{
    "window":"2019-08-25T00:00:00.031Z/2019-08-25T00:05:46.517Z",
    "timestamps":[
      1566691200031,1566691200059,1566691200178,1566691201241,1566691202501,1566691202525,1566691202688,1566691203546,1566691205223,1566691205379,1566691206205,1566691206250,1566691207544,1566691207611,1566691207747,1566691208852,1566691209424,1566691209643,1566691211161,1566691212085,1566691212106,1566691212136,1566691213559,1566691213598,1566691214081,1566691214635,1566691214850,1566691215592,
      ...
      1566691519479,1566691520006,1566691520970,1566691521420,1566691522196,1566691522903,1566691523319,1566691525610,1566691525877,1566691526822,1566691527188,1566691528215,1566691531589,1566691531953,1566691532069,1566691532237,1566691532342,1566691533658,1566691534036,1566691534281,1566691534684,1566691535612,1566691535741,1566691536486,1566691538543,1566691539289,1566691539345,1566691539384,1566691539722,1566691540091,1566691540179,1566691540356,1566691540415,1566691540658,1566691540952,1566691542248,1566691543001,1566691543580,1566691544324,1566691545010,1566691545586,1566691546054
    ],
    "dataPoints":[
      176428.0,1500913.0,17671.0,1806.0,8.0,41197.0,32789.0,2477.0,34672.0,52801.0,71863.0,1357089.0,246507.0,30.0,7918.0,34875.0,142364.0,88093.0,39439.0,17955.0,1013010.0,15661.0,42421.0,131493.0,49737.0,29011.0,19682.0,36958.0,57481.0,106319.0,208682.0,53522.0,5447.0,64676.0,91347.0,47892.0,19609.0,142112.0,52714.0,33778.0,37210.0,15904.0,35711.0,179455.0,101275.0,147328.0,18283.0,8710.0,113181.0,199946.0,1747807.0,194554.0,12.0,1.59817309E8,120907.0,68048.0,1026.0,48819.0,92809.0,1407732.0,11287.0,26.0,96984.0,
      ...
      13999.0,33.0,18044.0,239674.0,10456.0,544.0,64962.0,524824.0,18255.0,14889.0,11857.0,52513.0,13227.0,48107.0,85459.0,23522.0,78256.0,652819.0,74793.0,1318293.0,22576.0,1204.0,2402.0,945.0,17847.0,226094.0,22126.0,83563.0,78901.0,25252.0,13963.0,219.0,31461.0,102330.0,430901.0,1.9715883E7,33.0,9552.0,96944.0,437809.0,1366714.0,390076.0,5939.0,31346.0,2.0227477E7,90551.0
    ],
    "timeProperties":{
      "period":"PT0.001S",
      "origin":null,
      "timeZone":"UTC"
    },
    "bucketMillis":1,
    "bounds":{
      "start":{
        "data":null,
        "timestamp":null
      },
      "end":{
        "data":null,
        "timestamp":null
      }
    }
  }
}

FILTER_TIMESERIES

The following query filters the input time series, returning data points greater than 100. The resulting output is in JSON format.

SELECT
TIMESERIES_TO_JSON(FILTER_TIMESERIES(TIMESERIES("__time", "session_length", '2019-08-25T00:00:00.234Z/2019-08-25T23:59:59.877Z', 470000), 'value < 100')) as filter_timeseries
FROM "kttm-v2-2019-08-25"

View the results

{
  "filter_timeseries":{
    "window":"2019-08-25T00:00:00.234Z/2019-08-25T23:59:59.877Z",
    "timestamps":[
      1566691211911,1566691213985,1566691220470,1566691276459,1566691286767,1566691337803,1566691366984,1566691375184,1566691413202,1566691442235,1566691461552,1566691491168,1566691494698,1566691495651,1566691496406,1566691507763,1566691516875,1566691537715,1566691555478,1566691594115,1566691599018,1566691606834,1566691612986,1566691615723,
      ...
      1566777268487,1566777274024,1566777274385,1566777285216,1566777289787,1566777306557,1566777317378,1566777325402,1566777341722,1566777355227,1566777358927,1566777361838,1566777373656,1566777384336,1566777391344,1566777395855,1566777406140,1566777409618,1566777448945,1566777490625,1566777497811,1566777522376,1566777528610,1566777530054,1566777559070,1566777559260,1566777576910,1566777592910
    ],
    "dataPoints":[
      49.0,43.0,76.0,96.0,48.0,72.0,46.0,41.0,48.0,59.0,51.0,52.0,62.0,31.0,89.0,49.0,87.0,42.0,99.0,97.0,73.0,35.0,73.0,64.0,93.0,80.0,66.0,90.0,78.0,98.0,89.0,85.0,70.0,36.0,15.0,83.0,69.0,76.0,84.0,75.0,53.0,63.0,62.0,66.0,29.0,93.0,60.0,72.0,51.0,94.0,96.0,74.0,86.0,92.0,95.0,67.0,69.0,58.0,85.0,98.0,88.0,42.0,59.0,52.0,73.0,97.0,82.0,90.0,85.0,54.0,50.0,58.0,
      ...
      59.0,69.0,93.0,55.0,64.0,57.0,31.0,27.0,25.0,58.0,56.0,34.0,42.0,51.0,95.0,65.0,75.0,38.0,68.0,75.0,63.0,72.0,81.0,50.0,76.0,29.0,53.0,56.0,56.0,31.0,93.0,31.0,23.0,63.0,84.0,67.0,88.0,68.0,61.0,62.0,57.0,99.0,68.0,70.0,42.0,91.0,67.0,53.0,87.0,33.0,94.0,76.0,68.0,92.0,28.0,86.0,75.0,92.0,30.0,94.0,26.0,66.0,32.0,37.0,48.0,90.0,28.0,78.0,50.0,77.0,48.0,69.0,92.0,44.0,96.0
    ],
    "timeProperties":null,
    "bucketMillis":null,
    "bounds":{
      "start":{
        "data":1753602.0,
        "timestamp":1566691200178
      },
      "end":{
        "data":null,
        "timestamp":null
      }
    }
  }
}

FIRST_IN_TIMESERIES and LAST_IN_TIMESERIES

The following query returns the first and last data point values in the time series:

SELECT
FIRST_IN_TIMESERIES(TIMESERIES("__time", "session_length", '2019-08-25T00:00:00.234Z/2019-08-25T23:59:59.877Z', 470000)) as first_data_point,
LAST_IN_TIMESERIES(TIMESERIES("__time", "session_length", '2019-08-25T00:00:00.234Z/2019-08-25T23:59:59.877Z', 470000)) as last_data_point
FROM "kttm-v2-2019-08-25"

View the results

{
  "first_data_point":62093,
  "last_data_point":331555
}

LINEAR_INTERPOLATION

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

SELECT count(*) as total_count,
TIMESERIES_TO_JSON(LINEAR_INTERPOLATION(TIMESERIES("__time", "session_length", '2019-08-25T00:00:00.031Z/2019-08-25T00:05:46.517Z', 203000), 'PT1H')) as timeseries
FROM "kttm-v2-2019-08-25"

View the results

{
  "total_count":465346,
  "timeseries":{
    "window":"2019-08-25T00:00:00.031Z/2019-08-25T00:05:46.517Z",
    "timestamps":[
      1566691200031,1566691200059,1566691200178,1566691200965,1566691201241,1566691201858,1566691202501,1566691202525,1566691202688,1566691203546,1566691204014,1566691205170,1566691205223,1566691205379,1566691205833,1566691206205,1566691206208,1566691206250,1566691206550,1566691206977,1566691207544,1566691207587,1566691207611,1566691207747,1566691207778,1566691208809,1566691208852,1566691209424,1566691209643,1566691209764,1566691211161,1566691211907,
      ...
      1566691540151,1566691540179,1566691540356,1566691540415,1566691540657,1566691540658,1566691540952,1566691542248,1566691543001,1566691543159,1566691543249,1566691543580,1566691543890,1566691544324,1566691544969,1566691545010,1566691545586,1566691546054,1566691546361
    ],
    "dataPoints":[
      76261.0,252689.0,1753602.0,62093.0,79764.0,6264.0,8070.0,8078.0,49275.0,82064.0,31420.0,15024.0,17501.0,52173.0,149.0,52950.0,10613.0,82476.0,19424.0,19253.0,1376342.0,68713.0,315220.0,315250.0,102294.0,13215.0,21133.0,56008.0,198372.0,56549.0,144642.0,53529.0,49.0,39488.0,57443.0,1070453.0,2487.0,2302.0,17963.0,60384.0,2307.0,43.0,131536.0,41667.0,91404.0,120415.0,91296.0,57049.0,76731.0,25997.0,62955.0,20614.0,78095.0,44384.0,150703.0,11511.0,220193.0,11522.0,65044.0,
      ...
      77299.0,72372.0,1390665.0,34005.0,3229.0,25805.0,268.0,1472.0,3874.0,4819.0,22666.0,248760.0,6496.0,28622.0,24361.0,8063.0,42.0,83605.0,437.0,79338.0,104590.0,118553.0,9947.0,10166.0,41627.0,10281.0,112611.0,543512.0,2.0259395E7,1807.0,1840.0,11392.0,108336.0,546145.0,177960.0,35220.0,1401934.0,30915.0,420991.0,278.0,6217.0,37563.0,2.026504E7,1028.0
    ],
    "timeProperties":{
      "period":"PT0.001S",
      "origin":null,
      "timeZone":"UTC"
    },
    "bucketMillis":1,
    "bounds":{
      "start":{
        "data":null,
        "timestamp":null
      },
      "end":{
        "data":91579.0,
        "timestamp":1566691546517
      }
    }
  }
}

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-25T00:00:00.031Z/2019-08-25T00:05:46.517Z', 203000), 'sqrt(value)')) as square_rooted_timeseries
FROM "kttm-v2-2019-08-25"

View the results

{
  "total_count":465346,
  "square_rooted_timeseries":{
    "window":"2019-08-25T00:00:00.031Z/2019-08-25T00:05:46.517Z",
    "timestamps":[
      1566691200031,1566691200059,1566691200178,1566691200965,1566691201241,1566691201858,1566691202501,1566691202525,1566691202688,1566691203546,1566691204014,1566691205170,1566691205223,1566691205379,1566691205833,1566691206205,1566691206208,1566691206250,1566691206550,1566691206977,1566691207544,1566691207587,1566691207611,1566691207747,1566691207778,1566691208809,1566691208852,
      ...
      1566691540151,1566691540179,1566691540356,1566691540415,1566691540657,1566691540658,1566691540952,1566691542248,1566691543001,1566691543159,1566691543249,1566691543580,1566691543890,1566691544324,1566691544969,1566691545010,1566691545586,1566691546054,1566691546361
    ],
    "dataPoints":[
      276.1539425755135,502.6818079063534,1324.2363837321493,249.18467047553307,282.42521133921457,79.14543574963751,89.83317872590283,89.87769467448528,221.97972880423114,286.46814831670207,177.25687574816385,122.5724275683565,132.29134514396625,228.41409763847764,12.206555615733702,230.10866998007702,103.01941564578979,287.1863506505837,139.37001112147476,138.75518008348374,1173.1760311223547,262.1316463153581,561.4445653846869,561.4712815451918,319.83433211586276,114.9565135170687,145.37193676910272,236.6600938054407,
      ...
      42.50882261366456,42.89522117905443,106.73331251301067,329.1443452347313,739.0162379812774,421.8530549847897,187.66992300312802,1184.0329387310135,175.82661914511124,648.838192464038,16.673332000533065,78.84795495128583,193.81176434881345,4501.670800936025,32.0624390837628
    ],
    "timeProperties":null,
    "bucketMillis":null,
    "bounds":{
      "start":{
        "data":null,
        "timestamp":null
      },
      "end":{
        "data":91579.0,
        "timestamp":1566691546517
      }
    }
  }
}

MAX_OVER_TIMESERIES and MIN_OVER_TIMESERIES

The following query returns the largest and the smallest data point values in the time series:

SELECT
MAX_OVER_TIMESERIES(TIMESERIES("__time", "session_length", '2019-08-25T00:00:00.031Z/2019-08-25T00:05:46.517Z', 470000)) as largest_data_point,
MIN_OVER_TIMESERIES(TIMESERIES("__time", "session_length", '2019-08-25T00:00:00.234Z/2019-08-25T23:59:59.877Z', 470000)) as smallest_data_point
FROM "kttm-v2-2019-08-25"

View the results

{
  "largest_data_point":159900372,
  "smallest_data_point":-26263
}

PADDING_INTERPOLATION

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

SELECT count(*) as total_count,
TIMESERIES_TO_JSON(PADDING_INTERPOLATION(TIMESERIES("__time", "session_length", '2019-08-25T00:00:00.031Z/2019-08-25T00:05:46.517Z', 203000), 'PT1H')) as timeseries
FROM "kttm-v2-2019-08-25"

View the results

{
  "total_count":465346,
  "timeseries":{
    "window":"2019-08-25T00:00:00.031Z/2019-08-25T00:05:46.517Z",
    "timestamps":[
      1566691200031,1566691200059,1566691200178,1566691200965,1566691201241,1566691201858,1566691202501,1566691202525,1566691202688,1566691203546,1566691204014,1566691205170,1566691205223,1566691205379,1566691205833,1566691206205,1566691206208,1566691206250,1566691206550,1566691206977,1566691207544,
      ...
      1566691539500,1566691539722,1566691540091,1566691540151,1566691540179,1566691540356,1566691540415,1566691540657,1566691540658,1566691540952,1566691542248,1566691543001,1566691543159,1566691543249,1566691543580,1566691543890,1566691544324,1566691544969,1566691545010,1566691545586,1566691546054,1566691546361
      ],
    "dataPoints":[
      76261.0,252689.0,1753602.0,62093.0,79764.0,6264.0,8070.0,8078.0,49275.0,82064.0,31420.0,15024.0,17501.0,52173.0,149.0,52950.0,10613.0,82476.0,19424.0,19253.0,1376342.0,68713.0,315220.0,315250.0,102294.0,13215.0,21133.0,56008.0,198372.0,56549.0,144642.0,53529.0,49.0,39488.0,57443.0,1070453.0,2487.0,2302.0,
      ...
      8063.0,42.0,83605.0,437.0,79338.0,104590.0,118553.0,9947.0,10166.0,41627.0,10281.0,112611.0,543512.0,2.0259395E7,1807.0,1840.0,11392.0,108336.0,546145.0,177960.0,35220.0,1401934.0,30915.0,420991.0,278.0,6217.0,37563.0,2.026504E7,1028.0
    ],
    "timeProperties":{
      "period":"PT0.001S",
      "origin":null,
      "timeZone":"UTC"
    },
    "bucketMillis":1,
    "bounds":{
      "start":{
        "data":null,
        "timestamp":null
      },
      "end":{
        "data":91579.0,
        "timestamp":1566691546517
      }
    }
  }
}

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-25T00:00:00.031Z/2019-08-25T00:05:46.517Z', 203000), 0.5) as quantile
FROM "kttm-v2-2019-08-25" 

View the results

{
  "total_count":465346,
  "quantile":47097
}

SUM_OVER_TIMESERIES and AVG_OVER_TIMESERIES

The following query returns the sum and average over all the data point values in the time series:

SELECT
SUM_OVER_TIMESERIES(TIMESERIES("__time", "session_length", '2019-08-25T00:00:00.234Z/2019-08-25T23:59:59.877Z', 470000)) as sum_over_timeseries,
AVG_OVER_TIMESERIES(TIMESERIES("__time", "session_length", '2019-08-25T00:00:00.234Z/2019-08-25T23:59:59.877Z', 470000)) as avg_over_timeseries
FROM "kttm-v2-2019-08-25"

View the results

{
  "sum_over_timeseries":153571366068,
  "avg_over_timeseries":330017.56998171244
}

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-25T00:00:00.031Z/2019-08-25T00:05:46.517Z', 203000), 'timeZone', 'IST')) as timeseries_meta
FROM "kttm-v2-2019-08-25"

View the results

{
  "timeseries_meta":{
    "window":"2019-08-25T00:00:00.031Z/2019-08-25T00:05:46.517Z",
    "timestamps":[
      1566691200031,1566691200059,1566691200178,1566691200965,1566691201241,1566691201858,1566691202501,1566691202525,1566691202688,1566691203546,1566691204014,1566691205170,1566691205223,1566691205379,1566691205833,1566691206205,1566691206208,1566691206250,
      ...
      1566691539500,1566691539722,1566691540091,1566691540151,1566691540179,1566691540356,1566691540415,1566691540657,1566691540658,1566691540952,1566691542248,1566691543001,1566691543159,1566691543249,1566691543580,1566691543890,1566691544324,1566691544969,1566691545010,1566691545586,1566691546054,1566691546361
    ],
    "dataPoints":[
      76261.0,252689.0,1753602.0,62093.0,79764.0,6264.0,8070.0,8078.0,49275.0,82064.0,31420.0,15024.0,17501.0,52173.0,149.0,52950.0,10613.0,82476.0,19424.0,19253.0,1376342.0,68713.0,315220.0,315250.0,102294.0,13215.0,21133.0,56008.0,198372.0,56549.0,144642.0,53529.0,49.0,
      ...
      83605.0,437.0,79338.0,104590.0,118553.0,9947.0,10166.0,41627.0,10281.0,112611.0,543512.0,2.0259395E7,1807.0,1840.0,11392.0,108336.0,546145.0,177960.0,35220.0,1401934.0,30915.0,420991.0,278.0,6217.0,37563.0,2.026504E7,1028.0
    ],
    "timeProperties":{
      "period":null,
      "origin":null,
      "timeZone":"Asia/Kolkata"
    },
    "bucketMillis":null,
    "bounds":{
      "start":{
        "data":null,
        "timestamp":null
      },
      "end":{
        "data":91579.0,
        "timestamp":1566691546517
      }
    }
  }
}

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-25T00:00:00.031Z/2019-08-25T00:05:46.517Z', 203000)) as time_series_size
FROM "kttm-v2-2019-08-25" 

View the results

{
  "total_count":465346,
  "time_series_size":988
}

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-25T00:00:00.031Z/2019-08-25T00:05:46.517Z', 203000), 'backfill', 'PT1M')) as timeseries
FROM "kttm-v2-2019-08-25"

View the results

{
  "total_count":465346,
  "timeseries":{
    "window":"2019-08-25T00:00:00.031Z/2019-08-25T00:05:46.517Z",
    "timestamps":[
      1566691260000,1566691320000,1566691380000,1566691440000,1566691500000
    ],
    "dataPoints":[
      1420316.7148333334,397588.23563333333,140742.83908333333,114262.67936666666,263898.9115666667
    ],
    "timeProperties":{
      "period":"PT1M",
      "origin":null,
      "timeZone":"UTC"
    },
    "bucketMillis":60000,
    "bounds":{
      "start":{
        "data":1805892.5869666666,
        "timestamp":1566691200000
      },
      "end":{
        "data":null,
        "timestamp":null
      }
    }
  }
}