Skip to main content

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
TimstoreCellA 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.
IntervalOptional. An interval in seconds at which the query should be executed. Only values with a timestampt within given interval are used as query input. If no interval is specified the query is performed on each step.
TargetRangeOptional. A cell range to write the query result to.
LimitOptional. Specifies the maximum number of results stored. If limit is reached the function returns a #LIMIT error. Defaults to 1000.

Query A query json consist of following clauses:

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.
whereOptional. Only values which fulfill the conditiondefined by the where clause are taken. To compare values >, >=, <, <=, = != are supported. Use AND and OR to combine conditions.

Some Query Examples

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 avarage of all values
10wherev1 > 30for entries with a v1 value greater 30

Aggregation Methods

MethodDescription
noneNo aggregation is performed and the last valid value of specified DataCell is returned.
avgCalculates the average of all received values.
countCounts the number of received number values.
countaCounts all values which are not zero.
maxDetermines the maximum of all received values.
minDetermines the minimum of all received values.
productCalculates the product of all received values.
stdevsCalculates the standard deviation of all recieved values.
stdevpCurrently not available!!
sumCalculates the sum of all received values.

Return Value

TRUE, if successful.

Example

FunctionComment
=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.