The following function is available in Phi for running a Select-type query on data:
query(datetime fromTimestamp, datetime toTimestamp, array nodeIds)
Returns a cursor to a set of results.
NodeIds
can be a single numeric ID, or an array of IDs, and refers to data pools or assets.
fromTimestamp
and toTimestamp
should normally be entered verbatim (don't replace them with values), as they will be populated automatically by the metrics calculator when the function is run.
The function is like an SQL "select" query, and retrieves all data for the specified data pools or assets in the given time range.
## Get all of the child IDs of the current node, at any level below. ## This will return an array of the IDs. foundNodeIds = getAllChildIds() ## Provided there is at least one ID in the array... if(count(foundNodeIds) > 0) { ## Loop through the array for(row in query(fromTimestamp, toTimestamp, foundNodeIds)) { ## Rows will be returned in timestamp_1 order, showing Node ID, timestamp_1 and the data dump(row.node_id, row.timestamp_1, row.data) } }
query
returns a cursor of results that the script loops through using the for()
loop.
The above data will be accessed in time order, but if you prefer the rows in node ID order, put in an additional loop like this:
foundNodeIds = getAllChildIds() if(count(foundNodeIds) > 0) { for(thisNodeId in foundNodeIds) { for(row in query(fromTimestamp, toTimestamp, thisNodeId)) { dump(row.node_id, row.timestamp_1, row.data) } } }
Each time the variable row
is set in the loop, it is an object looking like this:
object{ "node_id": [integer], "is_data_pool": [boolean], "timestamp_1": [unixtime], "timestamp_2": [unixtime], "sources_known": null, "sources_assumed": null, "sources_total": null, "data": object{ "field1": [value], "field2": [value], ... }, "assumed_data": object{ "field3": [value], "field4": [value], ... }, "assumed_timestamps": object{ "field3": [unixtime], "field4": [unixtime], ... }, "empty": 0, "processed": 1, "backdated": 0, "last_duty_cycle_time": [duration] }
The values within the object can be accessed using, for example, row.node_id
to get the node ID, or row.data.field1
, to get the value of field1 in the data of that row.
Some notes on this object's contents:
node_id
is the unique numeric ID of the node, i.e. data pool or assetis_data_pool
is a 1 if a data pool, or 0 if an assettimestamp_1
is the time the data bubbled up from the level below and timestamp_2
will be blanktimestamp_1
is the time the server received the row and is assumed to be the sensor's effective measurement time provided timestamp_2
is blank; but when the asset sends a specific time (e.g. sending data from some earlier time) then timestamp_2
contains the server time and timestamp_1
is the sensor's effective (e.g. earlier) measurement timedata
is an object of the known fields and their dataassumed_data
is an object of other fields not included in data
of this row, but whose values are assumed to be the same from earlier rows; this makes it easier to work with field values that are not sent in every transmission; where set, assumed_timestamps
will contain the respective timestamps of when the data was last actually sent.processed
is for internal use, 0 when data is first recorded but 1 when the row has been processed by the Data Processorbackdated
for an asset is set to a 1 when an asset sent data from some earlier time (by including the effective timestamp)last_duty_cycle_time
for an asset is the time since the last transmission, which for assets communicating regularly can be used for reliability calculations.