Skip to content

Creating a database

You create a database by making a POST request to /db with a valid JSON payload (see the examples below).

Database properties

When you create a database you need to set several properties:

Property name Description
reading_type instant or interval - i.e. one or two timestamps
data_type The numeric data type used for the reading value. This must be large enough to accommodate aggregated values. Valid options are:
  • int - Signed 32 bit integer
  • long - Signed 64 bit integer
  • float - Signed single precision floating point number
  • double - Signed double precision floating point number
This data type will be used for all measurements
ttl_timestamp Each reading can have a time to live associated with it. TSGrid can calculate the TTL based on one of two timestamps:
  • event_time - The timestamp of the reading
  • ingestion_time - The time the reading was ingested into the system
resolutions A dictionary of required resolutions along with a ttl property. Valid keys are:
  • raw
  • min5
  • min10
  • min15
  • min30
  • hourly
  • daily
Note: raw is required, the other resolutions are optional. See the examples below
measurements A map of measurement names (columns) along with the aggregation function that should be used for the given measurement:
  • sum
  • min
  • max
  • mean
rollup_tz_offset The timezone offset to use when aggregating timestamps with different offsets. Valid options are:
  • utc - all timestamps are converted to UTC
  • local - the offset/timezone is ignored i.e. 09:00 GMT == 09:00 BST

Note

The rollup_tz_offset property only affects the rolled-up data, the underlying raw data is unchanged. If you ingest one reading as 09:00Z and one reading as 09:00+02 these offsets will be preserved.

Let's take a look at two examples to get a better understanding:

Page visits example

Let's assume we want to record visits to a site. Every time someone clicks a page we record it in the database. The raw data itself is relatively low value, so we can purge it to save space, relying instead on the rolled up values. We will ask TSGrid to roll-up data to hourly and daily values. The only measurement we want to store is "clicks"

Database properties

Property name Value
reading_type instant
data_type int
ttl_timestamp ingestion_time
resolutions raw: 5
hourly: 90
daily: -1
measurements "clicks" with an aggregation of sum
rollup_tz_offset utc

The HTTP request

Make a POST request to the TSGrid endpoint:

POST /db HTTP/1.1
Host: localhost:8080
Content-Type: application/json

{
  "name": "page-visits",
  "reading_type": "instant",
  "data_type": "int",
  "ttl_timestamp": "ingestion_time",
  "resolutions": {
    "raw": {
      "ttl": 5
    },
    "hourly": {
      "ttl": 90
    },
    "daily": {
      "ttl": -1
    }
  },
  "measurements": {
    "clicks": {
      "aggregation": "sum"
    }
  },
  "rollup_tz_offset": "utc"
}

Response:

Content-Type: application/json
Content-Length: 20

{
  "status": "success"
}

Let's look at what these properties mean in this context:

Property name Description
reading_type We are using a reading type of instant which means each data point has a single timestamp
data_type We use an int because although each entry will be 1, the aggregated values e.g. daily totals will be much larger
ttl_timestamp Data will be purged N days after it is ingested into the system
resolutions The raw data (the data you ingest) will be deleted after 5 days. The hourly aggregations will be deleted after 90 days and the daily aggregations will not be deleted
measurements We are recording only one measurement (clicks), and we want TSGrid to sum the values into hourly and daily values
rollup_tz_offset We use a value of utc so the system will convert all timestamps into utc before aggregation. If we query for a "day" of data we will get the total number of page clicks between 00:00Z (inclusive) and 00:00Z (exclusive)

Smart meter example

Let's assume we want to store 15 minute smart meter readings. For the purposes of this example we will assume the readings have already been converted into 15 minute intervals, i.e. we are not dealing with raw cumulative values. The raw data is valuable, so we won't purge it at all.

We want to analyse how customers use their energy throughout the year. Working in UTC would not take the DST shift into account even though end users behave based on local time (e.g. they always get up at 07:30). We will therefore ask TSGrid to aggregate based on local time.

We want to store two measurements (channels) for each timestamp: consumption_kwh and power_kw. We want TSGrid to roll-up the raw consumption values by summing the measurements. However, it makes no sense to sum the raw power measurements, so we will instead calculate the mean:

Database properties

Property name Value
reading_type interval
data_type double
ttl_timestamp event_time
resolutions raw: -1 hourly: -1 daily: -1
measurements consumption_kwh: sum, power_kw: mean
rollup_tz_offset local

The HTTP request

Request:

POST /db HTTP/1.1
Host: localhost:8080
Content-Type: application/json

{
  "name": "meter-readings",
  "reading_type": "interval",
  "data_type": "int",
  "ttl_timestamp": "event_time",
  "resolutions": {
    "raw": {
      "ttl": -1
    },
    "hourly": {
      "ttl": -1
    },
    "daily": {
      "ttl": -1
    }
  },
  "measurements": {
    "consumption_kwh": {
      "aggregation": "sum"
    },
    "power_kw": {
      "aggregation": "mean"
    }
  },
  "rollup_tz_offset": "local"
}

Response:

Content-Type: application/json
Content-Length: 20

{
    "status": "success"
}

Let's look at what these properties mean in this context:

Property name Description
reading_type We are using a reading type of interval which means each reading has from and until timestamps
data_type We use a double
ttl_timestamp This field is actually irrelevant as we have set the TTL for each aggregation to -1. Nevertheless we still need to include it to pass the validation checks
resolutions Neither the raw data nor the aggregated values will be purged
measurements We record consumption and power, summing up consumption and averaging power to give us hourly and daily values
rollup_tz_offset We use a value of local meaning the system will ignore the timezone offset when aggregating data. This means that 09:00 GMT and 09:00 BST will be treated as equivalent

Tip

Why did we use Local as the rollup_tz_offset? We did so for a couple of reasons: Firstly, we want to reflect the fact that during the DST switch, days are actually longer or shorter than normal (which could affect energy consumption). Secondly, if we aggregate using UTC, a day's energy consumption would mean 00:00 to 00:00 during winter months, but 01:00 to 01:00 (at local time) during summer months. This is probably not what we want.