Skip to content

Inserting and deleting data

You insert data into TSGrid by making a PUT request to /db/{db_name}/data The structure of the payload depends on the reading type you chose when creating the database. As should be apparent from the HTTP verb, insertion into TSGrid is an upsert operation. Let's look at two examples:

Page visits example

You should PUT a JSON array with N readings. Each reading should include these properties:

Property Description
sensor_id Sensor id
time A timestamp in ISO 8601 offset date time format e.g. 2020-01-01T00:00:00Z
values A map of measurement name to numeric value i.e. { "clicks": 1 }

Tip

You can think of these properties as being conceptually similar to this SQL statement:
INSERT INTO table_name (sensor_id, time, measurement_one, measurement_two) VALUES ...

Request:

PUT /db/page-visits/data HTTP/1.1
Host: localhost:8080
Content-Type: application/json

[
  {
    "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
    }
  }
]

Response:

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

{
  "status": "success",
  "readings_inserted": 4
}

Smart meter example

You should PUT a JSON array with N readings, each reading should include these properties

Property Description
sensor_id Sensor id
from A timestamp in ISO 8601 offset date time format representing the start of the interval (inclusive)
until A timestamp in ISO 8601 offset date time format representing the end of the interval (exclusive)
values A map of measurement name to numeric value i.e. { "consumption_kwh": 100, "power_kw": 400 }

Tip

You can think of these properties as being conceptually similar to this SQL statement:
INSERT INTO table_name (sensor_id, from, until, measurement_one, measurement_two) VALUES ...

Request:

PUT /db/meter-readings/data HTTP/1.1
Host: localhost:8080
Content-Type: application/json

[
  {
    "sensor_id": 1,
    "from": "2020-01-01T00:00:00+00:00",
    "until": "2020-01-01T00:15:00+00:00",
    "values": {
      "consumption_kwh": 100,
      "power_kw": 400
    }
  },
  {
    "sensor_id": 1,
    "from": "2020-01-01T00:15:00+00:00",
    "until": "2020-01-01T00:30:00+00:00",
    "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
    }
  }
]

Response:

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

{
  "status": "success",
  "readings_inserted": 4
}

Deleting data

You delete readings by making a DELETE call to TSGrid, passing a JSON document:

Property Description
sensor_ids The sensors for which data should be deleted
from A timestamp in ISO 8601 offset date time format representing the start of the interval (inclusive)
until A timestamp in ISO 8601 offset date time format representing the end of the interval (exclusive)

Tip

You can think of these properties as being conceptually similar to this SQL statement:
DELETE FROM table_name WHERE sensor_id IN (1,2) AND time >= from AND time < until

Request:

DELETE /db/page-visits/data HTTP/1.1
Host: localhost:8080
Content-Type: application/json

{
  "sensor_ids": [1,2],
  "from": "2020-01-01T00:00:00Z",
  "until": "2020-12-31T00:00:00Z"
}

Response:

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

{
  "status": "success",
  "readings_deleted": 4
}