Querying Water Data

Overview

API Route

Querying water usage data is one of the most important pieces of the backend from a user facing perspective. We have developed a simple query structure for asking a range of time based questions about water usage. A query object has the following structure:

Field	Data Type	Required	Default	Description
request_id	String	true	N/A	This is the key for the query, when you send multiple queries, this key will contain the answer as the value.
since_datetime	Datetime	true	N/A	Restrict the query range to data samples since this datetime. This value has no offset and represents time in the time-zone the device is sending from.
until_datetime	Datetime	false	Now()	Restrict the query range to data samples until this datetime. This value has no offset and represents time in the time-zone the device is sending from.
bucket	String `MIN, HR, DAY, MON, YR`	true	N/A	The bucket grouping of the data we are querying.
group_multiplier	Integer	false	1	Apply this multiplier to the bucket grouping. Defaults to 1. E.g. a `group_multiplier` of 3 and `bucket` of MON would group the data in 3 month groupings.
operation	String `SUM, AVG, MIN, MAX, CNT`	false	null	The optional type of aggregate/accumulate operation to perform.
units	String (GALLONS, LITERS, CUBIC_FEET, or CUBIC_METERS)	false	GALLONS	Specifies the unit of measurement that water usage will be returned in.
sort_direction	String (ASC or DESC)	false	ASC	Which way to sort the results.
types	Array of strings	false	['all']	See Querying By Appliance for details

Example 1 - Simple

Give me the daily totals for the current month

POST Body

{
  "queries": [
    {
      "bucket": "DAY",
      "since_datetime": "2016-03-01 00:00:00",
      "until_datetime": "2016-03-31 00:00:00",
      "request_id": "5705766de1914"
    }
  ]
}

Response

{
  "success": true,
  "code": 602,
  "message": "Request OK",
  "http_code": 200,
  "http_message": "OK",
  "detailed": null,
  "data": [
    {
      "5705766de1914": [
        {
          "datetime": "2016-03-01 00:00:00",
          "value": 114933
        },
        {
          "datetime": "2016-03-02 00:00:00",
          "value": 115911
        },
        {
          "datetime": "2016-03-03 00:00:00",
          "value": 114526
        },
        {
          "datetime": "2016-03-04 00:00:00",
          "value": 116139
        },
        ...
      ]
    }
  ]
}

Notes

bucket represents the time groupings of the data
All datetime's are in the TZ of where the device is sending data from. The data is contained as: since_datetime <= results < until_datetime
operation is how to aggregate the data, if desired. For example if you want the avg, min, max or sum of the data use this field. It is null by default
For each query you send, you must provide a request_id so you are able to match up with the results
The results will always be the first element in the data array

Example 2 - Aggregate

Give me the average over the hours between 2016-03-01 and 2016-03-02. Also, give me the minimum daily usage between 2016-03-01 and 2016-03-09

POST Body

{
  "queries": [
    {
      "bucket": "HR",
      "since_datetime": "2016-03-01 00:00:00",
      "until_datetime": "2016-03-02 00:00:00",
      "operation": "avg",
      "request_id": "xyz"
    },
    {
      "bucket": "DAY",
      "since_datetime": "2016-03-01 00:00:00",
      "until_datetime": "2016-03-09 00:00:00",
      "operation": "min",
      "request_id": "abc"
    }
  ]
}

Response

{
  "success": true,
  "code": 602,
  "message": "Request OK",
  "http_code": 200,
  "http_message": "OK",
  "detailed": null,
  "data": [
    {
      "xyz": [
        {
          "value": 4788.875
        }
      ],
      "abc": [
        {
          "value": 115407
        }
      ]
    }
  ]
}

Notes

Here we ask for two separate queries, and get two separate responses. Each response corresponds to the provided request_ids
Notice how the operation is used for each query, and each result only contains a single value without a datetime

Example 3 - Aggregate 2

Give me the running total so far for this month. Assuming the current month is April.

POST Body

{
  "queries": [
    {
      "bucket": "MON",
      "since_datetime": "2016-04-01 00:00:00",
      "operation": "sum",
      "request_id": "5705766de1914"
    }
  ]
}

Response

{
  "success": true,
  "code": 602,
  "message": "Request OK",
  "http_code": 200,
  "http_message": "OK",
  "detailed": null,
  "data": [
    {
      "5705766de1914": [
        {
          "value": 69588
        }
      ]
    }
  ]
  "count": 0,
  "pagination": null
}

Example 4 - Grouping

Give me two hours of data in 30 minute intervals.

POST Body

{
  "queries": [
    {
      "bucket": "MIN",
      "since_datetime": "2016-03-01 00:00:00",
      "until_datetime": "2016-03-01 02:00:00",
      "group_multiplier": 30,
      "request_id": "5705766de1914"
    }
  ]
}

Response

{
  "success": true,
  "code": 602,
  "message": "Request OK",
  "http_code": 200,
  "http_message": "OK",
  "detailed": null,
  "data": [
    {
      "5705766de1914": [
        {
            "datetime": "2016-03-01T00:00:00",
            "value": 2.7648644
        },
        {
            "datetime": "2016-03-01T00:30:00",
            "value": 2.5365528
        },
        {
            "datetime": "2016-03-01T01:00:00",
            "value": 2.7943592
        },
        {
            "datetime": "2016-03-01T01:30:00",
            "value": 2.802006
        }
      ]
    }
  ]
}

Notes

This query uses the group_multiplier field to achieve the 30 minute intervals. This field groups buckets by the number specified.