_TIMESERIES Function

  • reference
  • Couchbase Server 7.2
January 5, 2025
+ 12

The _TIMESERIES function enables you to query time series data.

The time series data must be stored in Couchbase time series documents. For more information, see Store and Process Time Series Data.

The function dynamically generates data point objects from the array of time series data in the input document. For a regular time series, the function uses the time series interval to generate a date-time stamp for each time point automatically.

Syntax

_TIMESERIES(document, options)

Arguments

document

An expression resolving to a time series document.

options

A JSON object specifying options for the function.

Options

Name Description Schema

ts_data
optional

The path to the time series data in the document.

If omitted, the function uses the ts_data field in the document.

If the function cannot find the time series data, the document is ignored.

Default: ts_data

String

ts_interval
optional

The path to the time series interval in the document.

If omitted, the function uses the ts_interval field in the document.

If the function cannot find the time series interval, the time series is assumed to be irregular.

Default: ts_interval

String

ts_start
optional

The path to the start date and time for the document.

If omitted, the function uses the ts_start field in the document.

If the function cannot find the start date and time, the document is ignored.

Default: ts_start

String

ts_end
optional

The path to the end date and time for the document.

If omitted, the function uses the ts_end field in the document.

If the function cannot find the end date and time, the document is ignored.

Default: ts_end

String

ts_keep
optional

Whether the timeline should be removed to save memory. If true, the timeline is kept.

Default: false

Boolean

ts_ranges
optional

One or more date-time ranges, which are used to filter the time series data. Time series data outside the specified range or ranges is filtered out.

For a single predicate range, this is an expression resolving to an array containing two integers, both representing milliseconds since the Unix epoch. The first value is the start of the filtered time series range, and the second is the end of the filtered time series range.

Both values are inclusive. If you need the start of the range to be exclusive, increase the start value by one. If you need the end of the range to be exclusive, decrease the end value by one.

Both values must be present. If you need the start of the range to be unbounded, use 0. If you need the end of the range to be unbounded, use MaxInt64 — that is, (2^63)-1.

To filter on multiple predicate ranges, this option can be an array of arrays, in which each nested array contains a pair of millisecond integers as above.

If omitted, the time series data is not filtered.

Array of integers, or array of arrays of integers

ts_project
optional

An integer, or an array of integers, indicating which values should be returned for each data point. 0 indicates the first value, 1 the second value, and so on.

If omitted, all values are returned for every data point.

Numeric expression, or array of numeric expressions

Return Values

The function returns an array of objects, with one object for each data point in the selected range. Each object has the following fields:

_t

An integer representing the time at that data point, in milliseconds since the Unix epoch.

_v0, _v1, …​

The value or values at that time point.

Usage

You can use the _TIMESERIES function anywhere in a query, but typically you use it as the right-hand side of an UNNEST clause.

You cannot use the _TIMESERIES function in an index definition.

If your query contains predicates on date-time ranges, and you are using an index for your time series documents, you should include the range predicates in the WHERE clause of the query, as well as the ts_ranges option of the _TIMESERIES function. This pushes the range predicates to the index scan.

A query containing date-time range predicates in both the _TIMESERIES function and the WHERE clause may become repetitive and hard to understand, especially with complex range predicates. To make your queries easier to read, use a query parameter or common table expression to store the date-time range predicates.

Examples

Example 1. Query regular time series data

The following query selects time series data for mean temperatures in the specified time range. Each time series document contains a month’s data.

sql++
WITH docs AS ( [ { "region": "UK", "ts_data": [18.5, 18.5, 18.5, 18.5, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 16.5, 16.5, 16.5, 16.5, 16.5, 16.5, 16.5, 16.5, 16.5, 16.5], "ts_end": 1375228800000, "ts_start": 1372636800000, "ts_interval": 86400000 }, { "region": "UK", "ts_data": [19.5, 19.5, 19.5, 19.5, 19.5, 19.5, 19.5, 19.5, 19.5, 19.5, 17, 15.5, 15.5, 15.5, 15.5, 15.5, 15.5, 15.5, 15.5, 15.5, 15.5, 15.5, 15.5, 15.5, 15.5, 15.5, 15.5, 14, 14, 14, 14], "ts_end": 1377907200000, "ts_start": 1375315200000, "ts_interval": 86400000 } ] ), range_start AS (1375056000000), range_end AS (1375574400000) SELECT t.* FROM docs AS d UNNEST _timeseries(d, {"ts_ranges": [range_start, range_end]}) AS t WHERE d.region = 'UK' AND (d.ts_start <= range_end AND d.ts_end >= range_start);

Note that the specified range predicate cuts across more than one time series document.

For each time point, the _TIMESERIES function calculates the date-time stamp _t and returns a single value _v0.

Results
json
[ { "_t": 1375056000000, "_v0": 16.5 }, { "_t": 1375142400000, "_v0": 16.5 }, { "_t": 1375228800000, "_v0": 16.5 }, { "_t": 1375315200000, "_v0": 19.5 }, { "_t": 1375401600000, "_v0": 19.5 }, { "_t": 1375488000000, "_v0": 19.5 }, { "_t": 1375574400000, "_v0": 19.5 } ]
Example 2. Query regular time series data with multiple data points

The following query selects time series data for daily low and high temperatures in the specified time range. Each time series document contains a month’s data.

sql++
WITH docs AS ( [ { "region": "UK", "ts_data": [ [10, 27], [10, 27], [10, 27], [10, 27], [10, 30], [10, 30], [10, 30], [10, 30], [10, 30], [10, 30], [10, 30], [10, 30], [10, 30], [10, 30], [10, 30], [10, 30], [10, 30], [10, 30], [10, 30], [10, 30], [10, 30], [10, 23], [10, 23], [10, 23], [10, 23], [10, 23], [10, 23], [10, 23], [10, 23], [10, 23], [10, 23] ], "ts_end": 1375228800000, "ts_start": 1372636800000, "ts_interval": 86400000 }, { "region": "UK", "ts_data": [ [12, 27], [12, 27], [12, 27], [12, 27], [12, 27], [12, 27], [12, 27], [12, 27], [12, 27], [12, 27], [12, 22], [9, 22], [9, 22], [9, 22], [9, 22], [9, 22], [9, 22], [9, 22], [9, 22], [9, 22], [9, 22], [9, 22], [9, 22], [9, 22], [9, 22], [9, 22], [9, 22], [9, 19], [9, 19], [9, 19], [9, 19] ], "ts_end": 1377907200000, "ts_start": 1375315200000, "ts_interval": 86400000 } ] ), range_start AS (1375056000000), range_end AS (1375574400000) SELECT MILLIS_TO_TZ(t._t,"UTC") AS day, t._v0 AS low, t._v1 AS high FROM docs AS d UNNEST _timeseries(d, {"ts_ranges": [range_start, range_end]}) AS t WHERE d.region = 'UK' AND (d.ts_start <= range_end AND d.ts_end >= range_start);

Note that the specified time range predicate cuts across more than one time series document.

For each time point, the _TIMESERIES function calculates the date-time stamp _t and returns the values _v0 and _v1.

The query adds aliases to the data returned by the _TIMESERIES function and converts the date-time stamp to a readable date-time string.

Results
json
[ { "day": "2013-07-29T00:00:00Z", "high": 23, "low": 10 }, { "day": "2013-07-30T00:00:00Z", "high": 23, "low": 10 }, { "day": "2013-07-31T00:00:00Z", "high": 23, "low": 10 }, { "day": "2013-08-01T00:00:00Z", "high": 27, "low": 12 }, { "day": "2013-08-02T00:00:00Z", "high": 27, "low": 12 }, { "day": "2013-08-03T00:00:00Z", "high": 27, "low": 12 }, { "day": "2013-08-04T00:00:00Z", "high": 27, "low": 12 } ]

To view the results as a chart:

  1. Click Chart.

  2. In Chart Type, select Multi-Connected Points by Column.

  3. In X-Axis, select day.

  4. In Y-Values, select both high and low.

Line chart showing high and low temperatures over 7 days
Example 3. Query irregular time series data

The following query selects time series data for house sales and prices in the specified time range. Each time series document contains a decade’s data. [1]

sql++
WITH docs AS ( [ { "district": "South", "ts_data": [ [852595200000, 69950], [852854400000, 67000], [884044800000, 71500], [884131200000, 73000], [884217600000, 72000] ], "ts_end": 884217600000, "ts_start": 852595200000 }, { "district": "South", "ts_data": [ [978912000000,115000], [1010534400000,139950], [1073347200000,195000], [1105056000000,225000], [1136678400000,210000] ], "ts_end": 1136678400000, "ts_start": 978912000000 }, { "district": "South", "ts_data": [ [1294531200000,200000], [1326326400000,212000], [1357430400000,171000], [1420675200000,252500], [1452384000000,330000], [1483660800000,290000], [1514764800000,325000] ], "ts_end": 1514764800000, "ts_start": 1294531200000 } ] ), range_start AS (1104537600000), range_end AS (1419984000000) SELECT MILLIS_TO_TZ(t._t,"UTC") AS date, t._v0 AS price FROM docs AS d UNNEST _timeseries(d, {"ts_ranges": [range_start, range_end]}) AS t WHERE d.district = 'South' AND (d.ts_start <= range_end AND d.ts_end >= range_start);

Note that the specified time range cuts across more than one time series document.

For each time point, the _TIMESERIES function returns the date-time stamp _t and a single value _v0.

The query adds aliases to the data returned by the _TIMESERIES function and converts the date-time stamp to a readable date-time string.

Results
json
[ { "date": "2005-01-07T00:00:00Z", "price": 225000 }, { "date": "2006-01-08T00:00:00Z", "price": 210000 }, { "date": "2011-01-09T00:00:00Z", "price": 200000 }, { "date": "2012-01-12T00:00:00Z", "price": 212000 }, { "date": "2013-01-06T00:00:00Z", "price": 171000 } ]

To view the results as a chart:

  1. Click Chart.

  2. In Chart Type, select Line.

  3. In X-Axis, select date.

  4. In Y-Axis, select price.

Line chart showing house prices over 10 years
Example 4. Use window functions with time series data

Before you try this example, you must follow all the examples in Store and Process Time Series Data to import time series data.

For this example, set the query context to the time scope in the travel sample dataset. For more information, see Query Context.

The following query returns the weekly average and four-week moving average for temperature over a two-month range.

sql++
WITH range_start AS (STR_TO_MILLIS ("2013-07-01", "YYYY-MM-DD")), range_end AS (STR_TO_MILLIS ("2013-08-31", "YYYY-MM-DD")) SELECT MILLIS_TO_TZ(week*1000*60*60*24*7, "UTC") AS week_of, week_avg, AVG(week_avg) OVER (ORDER BY week ROWS 4 PRECEDING) AS four_week_mov_avg FROM weather AS d UNNEST _timeseries(d, {"ts_ranges": [range_start, range_end]}) AS t WHERE d.region = 'UK' AND (d.ts_start <= range_end AND d.ts_end >= range_start) GROUP BY IDIV(t._t, 1000*60*60*24*7) AS week LETTING week_avg = AVG(t._v0);
Results
json
[ { "four_week_mov_avg": 18.5, "week_avg": 18.5, "week_of": "2013-06-27T00:00:00Z" }, { "four_week_mov_avg": 19.142857142857142, "week_avg": 19.785714285714285, "week_of": "2013-07-04T00:00:00Z" }, { "four_week_mov_avg": 19.428571428571427, "week_avg": 20, "week_of": "2013-07-11T00:00:00Z" }, { "four_week_mov_avg": 19.19642857142857, "week_avg": 18.5, "week_of": "2013-07-18T00:00:00Z" }, { "four_week_mov_avg": 18.657142857142855, "week_avg": 16.5, "week_of": "2013-07-25T00:00:00Z" }, { "four_week_mov_avg": 18.857142857142854, "week_avg": 19.5, "week_of": "2013-08-01T00:00:00Z" }, { "four_week_mov_avg": 18.385714285714286, "week_avg": 17.428571428571427, "week_of": "2013-08-08T00:00:00Z" }, { "four_week_mov_avg": 17.485714285714288, "week_avg": 15.5, "week_of": "2013-08-15T00:00:00Z" }, { "four_week_mov_avg": 16.842857142857145, "week_avg": 15.285714285714286, "week_of": "2013-08-22T00:00:00Z" }, { "four_week_mov_avg": 16.342857142857145, "week_avg": 14, "week_of": "2013-08-29T00:00:00Z" } ]

To view the results as a chart:

  1. Click Chart.

  2. In Chart Type, select Multi-Connected Points by Column.

  3. In X-Axis, select week_of.

  4. In Y-Values, select both four_week_mov_avg and week_avg.

Line chart showing weekly average temperature and four-week moving average over 2 months
Example 5. Query time series data with multiple predicate ranges

Before you try this example, you must follow all the examples in Store and Process Time Series Data to import time series data.

For this example, set the query context to the time scope in the travel sample dataset. For more information, see Query Context.

The following query returns the weekly average and four-week moving average for temperature over March, April, and May.

sql++
WITH datarange AS ( [ [STR_TO_MILLIS("2013-03-01", "YYYY-MM-DD"), STR_TO_MILLIS("2013-03-31", "YYYY-MM-DD")], [STR_TO_MILLIS("2013-05-01", "YYYY-MM-DD"), STR_TO_MILLIS("2013-05-31", "YYYY-MM-DD")], [STR_TO_MILLIS("2013-07-01", "YYYY-MM-DD"), STR_TO_MILLIS("2013-07-31", "YYYY-MM-DD")] ]), docs AS ( SELECT DISTINCT RAW META(d).id FROM datarange AS tr JOIN weather AS d ON d.region = 'UK' AND (d.ts_start <= tr[1] AND d.ts_end>= tr[0])) SELECT MILLIS_TO_TZ(week*1000*60*60*24*7, "UTC") AS week_of, week_avg, AVG(week_avg) OVER (ORDER BY week ROWS 4 PRECEDING) AS four_week_mov_avg FROM weather AS d USE KEYS docs UNNEST _timeseries(d, {"ts_ranges": datarange }) AS t GROUP BY IDIV(t._t, 1000*60*60*24*7) AS week LETTING week_avg = AVG(t._v0);
Results
json
[ { "four_week_mov_avg": 5.5, "week_avg": 5.5, "week_of": "2013-02-28T00:00:00Z" }, { "four_week_mov_avg": 5.5, "week_avg": 5.5, "week_of": "2013-03-07T00:00:00Z" }, { "four_week_mov_avg": 5.5476190476190474, "week_avg": 5.642857142857143, "week_of": "2013-03-14T00:00:00Z" }, { "four_week_mov_avg": 5.785714285714286, "week_avg": 6.5, "week_of": "2013-03-21T00:00:00Z" }, { "four_week_mov_avg": 5.928571428571429, "week_avg": 6.5, "week_of": "2013-03-28T00:00:00Z" }, { "four_week_mov_avg": 7.028571428571428, "week_avg": 11, "week_of": "2013-04-25T00:00:00Z" }, { "four_week_mov_avg": 8.128571428571428, "week_avg": 11, "week_of": "2013-05-02T00:00:00Z" }, { "four_week_mov_avg": 9.585714285714285, "week_avg": 12.928571428571429, "week_of": "2013-05-09T00:00:00Z" }, { "four_week_mov_avg": 10.985714285714284, "week_avg": 13.5, "week_of": "2013-05-16T00:00:00Z" }, { "four_week_mov_avg": 12.385714285714284, "week_avg": 13.5, "week_of": "2013-05-23T00:00:00Z" }, { "four_week_mov_avg": 12.885714285714283, "week_avg": 13.5, "week_of": "2013-05-30T00:00:00Z" }, { "four_week_mov_avg": 14.385714285714283, "week_avg": 18.5, "week_of": "2013-06-27T00:00:00Z" }, { "four_week_mov_avg": 15.757142857142856, "week_avg": 19.785714285714285, "week_of": "2013-07-04T00:00:00Z" }, { "four_week_mov_avg": 17.057142857142857, "week_avg": 20, "week_of": "2013-07-11T00:00:00Z" }, { "four_week_mov_avg": 18.057142857142857, "week_avg": 18.5, "week_of": "2013-07-18T00:00:00Z" }, { "four_week_mov_avg": 18.657142857142855, "week_avg": 16.5, "week_of": "2013-07-25T00:00:00Z" } ]

1. Contains HM Land Registry data © Crown copyright and database right 2021. This data is licensed under the Open Government Licence v3.0.