Skip to content

Querying data

Introduction

Time series data can be queried by making a GET request to /db/{db_name}/data, passing a JSON document in the body of the request (see below). Yes, a GET request with a body is a valid HTTP call! There are several dimensions to a query. The structure of the response varies based on the query and the underlying raw data.

Raw data

The response for a raw data query will return it in the same format as was used to ingest it. i.e. instant data will have a single timestamp, interval data will have two timestamps. The offset associated with the reading will also be returned, and this could vary between readings (if the raw readings use different offset).

Rolled up data (aggregated on the temporal axis)

Things become a little more complex when dealing with data that has been rolled up to a different resolution. A query for a resolution other than raw will always include from and until timestamps. Why is this? The rolled up data is now interval data, even if the underlying raw data was not.

Timezone offsets

Raw data will always be returned with the offset associated with the reading. If the data has been rolled up to a different resolution the response will use either a UTC offset, or no offset at all. It depends on how you initially configured the database (the rollup_tz_offset property).

Grouped results (aggregated on the sensor axis)

You can also ask TSGrid to aggregate sensor data on the fly, grouping measurements for multiple sensors into single readings. This operation can be performed on raw or rolled up data. The elements in grouped data response will not include a sensor_id property as the timestamps represent an aggregation of all the sensor ids passed in the query property.

That was quite a lot to cover so here is a summary of the response structure, based on the different scenarios:

Instant Interval Grouped
Raw sensor id + one ts + tz sensor id + two ts + tz no sensor id + one ts + tz
Rolled up (utc) sensor id + two ts + utc tz sensor id + two ts + utc tz no sensor id + two ts + utc tz
Rolled up (local) sensor id + two ts, no tz sensor id + two ts, no tz no sensor id, two ts, no tz

Query properties

Now we have got the basics out of the way, these are the properties you can pass to the /db/{db_name}/query endpoint (in a JSON document):

Property Description
sensor_ids Similar in concept to a SQL IN clause
from A timestamp in ISO 8601 offset date time format representing the start of the interval you want to query (inclusive)
until A timestamp in ISO 8601 offset date time format representing the end of the interval you want to query (exclusive)
resolution Output resolution. Valid options are:
  • raw
  • min5
  • min10
  • min15
  • min15
  • min30
  • hourly
  • daily
group_results Boolean - Whether the results should be aggregated together. If false one reading per sensor/timestamp will be returned. If true the sensor readings will be aggregated together resulting in one reading per timestamp. Measurements will be aggregated using the same aggregation method you defined when creating the database.

Examples

Raw instant data

The page visits database we created earlier is instant data i.e. it has only a single timestamp. Therefore, the query response for raw page visit data will also have a single timestamp:

Request:

GET /db/page-visits/query HTTP/1.1
Host: localhost:8080
Content-Type: application/json

{
  "sensor_ids": [1,2],
  "from": "2020-01-01T00:00:00Z",
  "until": "2020-01-03T00:00:00Z",
  "resolution": "raw",
  "group_results": false
}

Response:

Content-Type: application/json
Transfer-Encoding: chunked

[
  {
    "sensor_id": 1,
    "time": "2020-01-01T00:00:00Z",
    "values": {
      "clicks": 1
    }
  },
  {
    "sensor_id": 1,
    "time": "2020-01-01T01:00:00Z",
    "values": {
      "clicks": 1
    }
  },
  {
    "sensor_id": 2,
    "time": "2020-01-01T00:00:00Z",
    "values": {
      "clicks": 1
    }
  },
  {
    "sensor_id": 2,
    "time": "2020-01-01T01:00:00Z",
    "values": {
      "clicks": 1
    }
  }
]

The response format is basically a mirror of the underlying raw data

Rolled up instant data

Timestamps for rolled-up readings will use either a UTC offset date time, or a local date time, depending on which aggregationTimezone was used when initially creating the database. Aggregated readings will always be returned as interval readings. Let's look at the page visits example again. Although the raw data has only one timestamp, once it has been rolled-up it becomes interval data. The response therefore includes from and until timestamps:

Request:

GET /db/page-visits/query HTTP/1.1
Host: localhost:8080
Content-Type: application/json

{
  "sensor_ids": [1,2],
  "from": "2020-01-01T00:00:00Z",
  "until": "2020-01-03T00:00:00Z",
  "resolution": "daily",
  "group_results": false
}

Response:

Content-Type: application/json
Transfer-Encoding: chunked

[
  {
    "sensor_id": 1,
    "from": "2020-01-01T00:00:00Z",
    "until": "2020-01-02T00:00:00Z",
    "values": {
      "clicks": 2
    }
  },
  {
    "sensor_id": 2,
    "from": "2020-01-01T00:00:00Z",
    "until": "2020-01-02T00:00:00Z",
    "values": {
      "clicks": 2
    }
  }
]

Note

Notice how the from and until timestamps map to UTC days. That's because we configured the database to use UTC aggregation, and we queried for data at daily resolution.

Raw interval data

If the underlying raw data includes from and until timestamps, the raw query response will also include these timestamps. Take the example of the smart meter database we created earlier:

Request:

GET /db/meter-readings/query HTTP/1.1
Host: localhost:8080
Content-Type: application/json

{
  "sensor_ids": [1,2],
  "from": "2020-01-01T00:00:00Z",
  "until": "2020-01-03T00:00:00Z",
  "resolution": "raw",
  "group_results": false
}

Response:

Content-Type: application/json
Transfer-Encoding: chunked

[
  {
    "sensor_id": 1,
    "from": "2020-01-01T00:00:00Z",
    "until": "2020-01-01T00:15:00Z",
    "values": {
      "consumption_kwh": 100,
      "power_kw": 400
    }
  },
  {
    "sensor_id": 1,
    "from": "2020-01-01T00:15:00Z",
    "until": "2020-01-01T00:30:00Z",
    "values": {
      "consumption_kwh": 100,
      "power_kw": 400
    }
  },
  {
    "sensor_id": 2,
    "from": "2020-01-01T00:00:00Z",
    "until": "2020-01-01T00:15:00Z",
    "values": {
      "consumption_kwh": 100,
      "power_kw": 400
    }
  },
  {
    "sensor_id": 2,
    "from": "2020-01-01T00:15:00Z",
    "until": "2020-01-01T00:30:00Z",
    "values": {
      "consumption_kwh": 100,
      "power_kw": 400
    }
  }
]

Again the response is basically a mirror of the underlying data we ingested earlier.

Rolled up interval data

Interval data that has been rolled-up to a different resolution will have appropriate from and until timestamps:

Request:

GET /db/meter-readings/query HTTP/1.1
Host: localhost:8080
Content-Type: application/json

{
  "sensor_ids": [1,2],
  "from": "2020-01-01T00:00:00Z",
  "until": "2020-01-03T00:00:00Z",
  "resolution": "daily",
  "group_results": false
}

Response:

Content-Type: application/json
Transfer-Encoding: chunked

[
  {
    "sensor_id": 1,
    "from": "2020-01-01T00:00:00",
    "until": "2020-01-02T00:00:00",
    "values": {
      "consumption_kwh": 200,
      "power_kw": 400
    }
  },
  {
    "sensor_id": 2,
    "from": "2020-01-01T00:00:00",
    "until": "2020-01-02T00:00:00",
    "values": {
      "consumption_kwh": 200,
      "power_kw": 400
    }
  }
]

Tip

If you look carefully at these timestamps you will see they are missing a timezone. When we created the smart meter database we asked TSGrid to aggregate using local time. We deliberately throw away the timezone information. 09:00Z is the same at 09:00+01. In this example TSGrid is returning readings for a local day