Skip to main content
Version: Streamsheets 2.5

TIMEQUERY

Used to query values stored by TIMESTORE. The result can be saved to a specified TargetRange or used as an input for charts.

Syntax

=TIMEQUERY(TimestoreCell, Query, [Interval], [TargetRange], [Limit])

Arguments

NameDescription
TimestoreCellA reference to the timestore cell on which the query should be executed.
QueryA JSON object which specifies the query to perform. See below for more information. A query consists of the following fields:

NameDescription
selectDefines the values to query. Multiple values are separated by comma. The wildcard * will select all values.
aggregateOptional. Defines the aggregation method to use for each value separted by comma. Must match the number of values in select clause. See below for a complete list of defined aggregation methods. Defaults to none.

NumberMethodDescription
0noneNo aggregation is performed and the last valid value of specified DataCell is returned.
1averageCalculates the average of all received values.
2countCounts the number of received values.
3countaCounts all values which are not zero.
4maxDetermines the maximum of all received values.
5minDetermines the minimum of all received values.
6productCalculates the product of all received values.
7stdev.sCalculates the standard deviation of all recieved values.
8stdev.pCurrently not available!!
9sumCalculates the sum of all received values.
whereOptional. Only values which fulfill the condition defined by the where clause are taken. To compare values >, >=, <, <=, = != are supported. Use AND and OR to combine conditions.
Interval (optional)An interval in seconds at which the query should be executed. Only values with a timestamp within given interval are used as query input. If no interval is specified the query is performed on each step.
TargetRange (optional)A cell range to write the query result to.
Limit (optional)Specifies the maximum number of results stored. If limit is reached the function returns a #LIMIT error. Defaults to 1000.

Return

TRUE, if successful.

Examples

Query Source:

ABDescription
1selectv1JSON(A1:B3)
2aggregatesumwill sum up all values of v1
3wherev2 > 50 AND v1 < 100for entries with a v2 value greater 50 and a v1 value less 100
4
5selectv1, v2JSON(A5:B6)
6aggregatesum, maxwill sum up v1 and determine maximum of v2
7
8select*JSON(A8:B10)
9aggregateavgwill calculate the average of all values
10wherev1 > 30for entries with a v1 value greater 30
FormulaResultComment
=TIMEQUERY(C1, JSON(A1:B2))
Queries the total values for v1 on each step
=TIMEQUERY(C1, JSON(A5:B6), 10)
Queries the total for v1 and the maximum of v2 every 10 seconds
=TIMEQUERY(C1, JSON(A8:B8),,D4:E14)
Simply writes all stored values to target range on each step

TQ TQTRR

TIMEQUERY(F19,JSON(G21:H21),,A16:D39,) A query to gather all information (G21:H21) from the TIMESTORE function (F19) and display them in a Target Range (A16:D39). Left image shows the query, right image shows the Target Range.