Example queries of time series data by using the wire listener

These examples show how to query time series data by using the MongoDB API.

Before using these examples, you must configure the wire listener for the MongoDB. For more information, see Configuring the wire listener for the first time. These examples are run against the stores_demo database. For more information, see dbaccessdemo command: Create demonstration databases. These examples query the ts_data_v virtual table that stores the device ID in the loc_esi_id column.

For examples of aggregating or slicing time series data, see Aggregate or slice time series data.

List all device IDs

This query returns all unique device IDs.
MongoDB API
Run a distinct command on the ts_data_v virtual table:
db.ts_data_v.distinct("loc_esi_id")

Results:
["4727354321000111","4727354321046021","4727354321090954",...]

List device IDs that have a value greater than 10

This query returns the list of device IDs that have at least one measured value in the time series that is greater than 10.
MongoDB API
Run a distinct command on the ts_data_v table, with $gt value comparison operator specified:
db.ts_data_v.distinct("loc_esi_id",{"value":{"$gt":10}})

Results:
["4727354321046021","4727354321132574","4727354321289322",...]

Find the data for a specific device ID

This query returns the data for the device with the ID of 4727354321046021.
MongoDB API
Run a find command on the ts_data_v virtual table with the loc_esi_id value specified:
db.ts_data_v.find({"loc_esi_id":4727354321046021})

Results:
	{"loc_esi_id":"4727354321046021","measure_unit":"KWH",
 "direction":"P","tstamp":ISODate("2010-11-10T06:00:00Z"),
 "value":0.041}
	{"loc_esi_id":"4727354321046021","measure_unit":"KWH",
 "direction":"P","tstamp":ISODate("2010-11-10T06:15:00Z"),
 "value":0.041}
	{"loc_esi_id":"4727354321046021","measure_unit":"KWH",
 "direction":"P","tstamp":ISODate("2010-11-10T06:30:00Z"),
 "value":0.04}
...]

Find and sort data with multiple qualifications

This query finds all data for the device with the ID of 4727354321046021 with a value greater than 10.0 and a direction of P. The query returns the tstamp and value fields, and sorts the results in descending order by the value field.

MongoDB API
Run a find command on the ts_data_v table, with the $and boolean logical operator specified:
db.ts_data_v.find({"$and":[{"loc_esi_id":4727354321046021},
{"value":{"$gt":10.0}},{"direction":"P"}]},
{"tstamp":1,"value":1}).sort({"value":-1})

Results:
	{"tstamp":ISODate("2011-01-25T16:15:00Z"),"value":14.58}
	{"tstamp":ISODate("2011-01-26T00:45:00Z"),"value":12.948}
	{"tstamp":ISODate("2011-01-26T02:30:00Z"),"value":12.768}
	...

Find all data for a device in a specific date range

To query for specific dates, convert the dates to milliseconds since the epoch. For example:
  • 2011-01-01 00:00:00 = 1293861600000
  • 2011-01-02 00:00:00 = 1293948000000
This query returns the data from midnight January 1, 2011 to January 2, 2011 for device ID 4727354321000111. The date that is queried is greater than 1293861600000 and less than 1293948000000. The query returns the tstamp and value fields.
MongoDB API
Run a find command on the ts_data_v table, with values specified for the $and boolean logical query operator:
db.ts_data_v.find({"$and":[{"loc_esi_id":"4727354321000111"},
{"tstamp":{"$gte":ISODate("2011-01-01 00:00:00")}},
{"tstamp":{"$lt":ISODate("2011-01-02 00:00:00")}}]},
{"tstamp":"1","value":"1"})

Results:
	{"tstamp":ISODate("2011-01-01T00:00:00Z"),"value":0.343 }
	{"tstamp":ISODate("2011-01-01T00:15:00Z"),"value":0.349 }
	{"tstamp":ISODate("2011-01-01T00:30:00Z"),"value":1.472 }
...]

Find the latest data point for a specific device

This query sets the sort parameter to order the tstamp field in descending order and sets the limit parameter to 1 to return only the latest value. The device ID is 4727354321000111 and the query returns the tstamp and value fields.
MongoDB API
Run a find command on the ts_data_v table, with sort and limit values specified:
db.ts_data_v.find({"loc_esi_id":"4727354321000111"},
{"tstamp":"1","value":"1"}).sort({"tstamp":-1}).limit(1)

Results:
	{"tstamp":ISODate("2011-02-08T05:45:00Z"),"value":1.412 }

Find the 100th data point for a specific device

This query sets the sort parameter to order the tstamp field in ascending order and sets the skip parameter to 100 to return the 100th value. The device ID is 4727354321000111 and the query returns the tstamp and value field.
MongoDB API
Run the find command on the ts_data_v table, with values specified for sort, limit and skip:
db.ts_data_v.find({"loc_esi_id":4727354321000111},
{"tstamp":1,"value":1}).sort({"tstamp":1}).limit(1).skip(100)

Results:
	{"tstamp":ISODate("2010-11-11T07:00:00Z"),"value":0.013}