Query API¶

Overview¶

The IoTaWatt Query API provides access to the historical data in the datalogs using a restful interface that produces a table of JSON or CSV data. The table columns can be time, IoTaWatt inputs and IoTaWatt outputs. The table rows are datalog values grouped by a fixed time period or relative time periods like days, weeks, months and years.

Values can be requested for a variety of measurements such as Watts, Volts or Amps.

The Query API provides data to the Graph+ utility.

Query types¶

There are two basic queries currently supported:

show¶

Used to obtain a list of all inputs and outputs available to the query.

select¶

Used to select a set of series for a particular time period and return a table of values in JSON or CSV format.

query?show¶

This is the only form of the show query:

HTTP://iotawatt.local/query?show=series

This query simply lists all of the available inputs and outputs along with their respective primary unit of measure. The format is always JSON.

The response lists the series names that the select query will recognize. Here is a typical response from a simple configuration:

{"series":[{"name":"voltage","unit":"Volts"},{"name":"mains1","unit":"Watts"},
{"name":"mains2","unit":"Watts"},{"name":"solar","unit":"Watts"},
{"name":"heat_pump","unit":"Watts"},{"name":"mains","unit":"Watts"},
{"name":"used","unit":"Watts"}]}

query?select¶

select=[series1 [, series2 ...]]¶

Required parameter. Specifies the series to be returned in the columns of the response. There are three types of series that can be requested:

time [ .local | .utc] [ .iso | .unix]
Returns the time of the beginning of the reporting group. Modifiers can be used to specify local (default) or utc time, and iso date format(default) or unix seconds since 1/1/1970 format.

<voltage input or output> [ .volts | .hz] [.d<n>]

A voltage input or output. A unit modifier can be used to specify:

.volts (default)

.hz (frequency).

The .d<n> modifier overides the default number of decimal digits.

<power input or output> [ .watts | .amps | .wh | .va | .var | .varh | .pf] [.d<n>]

A power input or output. A unit modifier can be used to specify:

.watts (default)

.amps

.wh (watt-hours)

.va (volt-ampere)

.var (volt-ampere-reactive)

.varh (volt-ampere-reactive hours)

.pf (power factor)

The .d<n> modifier overides the default number of decimal digits.

An example might be:
select=[time.local.unix,mains.watts.d0,solar.wh.d1]

&begin=<time specifier>¶

Required parameter. Time can be specified in a variety of relative and absolute formats. See time specifiers

&end=<time specifier>¶

Required parameter. Time can be specified in a variety of relative and absolute formats. See time specifiers. end must be greater than begin.

&group={ auto | all | <n> {s | m | h | d | w | M | y}}¶

Optional parameter. The datalog contains measurements at 5 second (current log) and 1 minute (history log) intervals. This parameter specifies how to group those measurements into rows in the response table. The simple way, useful for detailed examination of short time periods, is to use some fixed number of seconds or minutes. When looking at longer time periods, it can be useful to group by hour, day, week etc. IoTaWatt knows where these boundaries are and will return the correct grouping taking into account daylight time changes, days-in-month, and leap years.

The default is auto, which selects a fixed time group to yield about 360 rows (resolution=low) or 720 rows (resolution=high).

all will cause all of the data in the time period to be treated as a single group. For most units, this will result in the average value over the entire period. For Wh, it will result in the total Wh for the entire period.

To overide, specify a time unit preceeded by a multiplier, as in:

10s (ten seconds)

5m (five minutes)

1h (one hour)

1M (one month) note case sensitive m=minutes, M=months

&format={ json | csv}¶

Optional parameter specifies the format of the query response. The default is json.

json:

Json format where the response is a Json object enclosed in brackets {} and the data table is a json array "data":[[series1,series2,..],[series1...]]

csv:

Comma Separated Values table.

&header={ no | yes }¶

Optional parameter specifies if a header is to be included to describe the columns (series) included in the response. Default is no.

For &format=csv, a row is prepended to the data with a comma delimited list of the series names.

For &format=json, the array "labels":[series1 [,series2 ....]] is added to the response. Another array "range":[begin, end] is added where begin and end are the 10 digit absolute unix begin and end times of the response.

&missing={ null | skip | zero}¶

Optional parameter specifies what to do when a missing value is encountered when building a response row.

null:

Use the value null.

zero:

Use the value zero.

skip:

Suppress the entire response row.

&resolution={ low | high }¶

Optional parameter specifies the relative resolution of the response table when &group=auto. The default is low. For more information see &group= above.

&limit={n | none }¶

Optional parameter overides the default output limit. The default is 1,000 lines.

n:

Maximum lines generated

none:

No limit, query runs to completion

Query is a blocking request. The IoTaWatt does not sample power while responding to a query. Short queries, as issued by Graph+, are of little consequence. They process in a second or less. To avoid unintended long lapses, a limit is placed on the number of lines (groups) that are returned by the query. To understand the time required for longer queries, you can experiment with a subset and scale the time up.

If the limit is reached, output will stop with a full line. If the format is json and header=yes, the response will include an object called "limit" with a value of the UTC timestamp of the next line that would have been produced. If the format is CSV, the following message will be appended with the UTC timestamp of the next line that would have been produced.

Limit exceeded at <UTCtime>

time specifiers¶

A time specifier can define a date/time in absolute or relative terms. Three different formats are allowed:

Unix time
ISO time
Relative time

Unix time¶

Unix time is the count of seconds or milliseconds since Jan 1, 1970. a Unix time specifier is simply a 10 digit integer for seconds or a 13 digit integer for milliseconds. IoTaWatt will always round the time to a multiple of 5 seconds.

ISO time¶

A subset of the ISO 8601 standard can be used to specify an absolute date and time. The supported format is:

YYYY [-MM [-DD [Thh [:mm [ :ss [Z]]]]]]

As you can see, the only thing required is the year, which must be four digits. That is optionally followed by:

-MM
a two digit month 01-12

-DD
a two digit day in month 01-31

Thh
two digit hours 00-23

:mm
two digit minutes 00-59

:ss
two digit seconds 00-59

Z
indicates the time is UTC rather than local time

Some examples are:

2018-01-01
Start of the year 2018, equal to 2018-01-01T00:00:00 or just 2018

2019-04-15T11:42:15
April 15, 2019 11:42:15

Relative time¶

Specifies a point in time relative to the current time. Makes it possible to specify "today", "yesterday", "last week" etc. All relative time specifiers begin with a base date or time as follows:

Relative dates all begin at 00:00:00 local IoTaWatt time.

y - Jan 1, of the current year
M - The first day of the current month
w - The first day of the current week (weeks start on Sunday)
d - The current day

Relative time.

h - first minute and second of the current hour.
m - First second of the current minute.
s - The current second (rounded down to 5 second multiple).

So if "today" is 2019-04-15T16:11:42:

Base

ISO time

y

2019-01-01T00:00:00

M

2019-04-01T00:00:00

w

2019-04-14T00:00:00

d

2019-04-15T00:00:00

h

2019-04-15T16:00:00

m

2019-04-15T16:11:00

s

2019-04-15T16:11:40

Base time may be followed by one or more offset modifiers to add or subtract from the base time. The format is:

{ + | -} [n] { y | M | w | d | h | m | s }

Examples:

Base with modifiers	Effective time
d-1d	00:00:00 yesterday
d-18h	06:00:00 yesterday
s-3h	Three hours ago
y-1M	Last December
w-1w+3d+12h	Noon on Wednesday of last week
s	Now

By using relative time for both begin and end, relative time periods can be specified:

begin	end	period
d-1d	d	yesterday
M-1M	M	Last month
d	s	Today to date
s-12h	s	Last 12 hours
w-1w+2d	w-1w+3d	Tuesday of last week
y	s	Year to date

Responses¶

400 invalid query.¶

The query has a missing or invalid specification. The response is a json object "error":"<error details>".

query:
HTTP:// ... /query?select=[time.iso,heap_pump,misc]&begin=d-1d&end=d&group=h
response:
{"error":"invalid query. Invalid series: heap_pump"}

200 Success¶

The query succeeded and the response is sent.

csv:: Response is the table of csv formatted lines.

query:

/query?select=[time.iso,Heat_Pump,misc]&begin=d-1d&end=d&group=h&format=csv&header=yes

response:

Time, Heat_Pump, misc
2019-10-16T00:00:00, 333, 125.5
2019-10-16T01:00:00, 332.2, 121.4
2019-10-16T02:00:00, 446.8, 116.8
2019-10-16T03:00:00, 416.8, 114.3
2019-10-16T04:00:00, 415.4, 109.9
2019-10-16T05:00:00, 582.9, 111.4
2019-10-16T06:00:00, 711.8, 113.3
2019-10-16T07:00:00, 783.5, 117.1
2019-10-16T08:00:00, 619.6, 117.5
2019-10-16T09:00:00, 333, 116.4
2019-10-16T10:00:00, 339.8, 164.5
2019-10-16T11:00:00, 345.1, 180.6
2019-10-16T12:00:00, 345.6, 114.5
2019-10-16T13:00:00, 345.3, 111.8
2019-10-16T14:00:00, 344.3, 130.9
2019-10-16T15:00:00, 343.4, 302.5
2019-10-16T16:00:00, 343.1, 271.6
2019-10-16T17:00:00, 342, 264.5
2019-10-16T18:00:00, 342.3, 114.1
2019-10-16T19:00:00, 343, 117
2019-10-16T20:00:00, 342.7, 118
2019-10-16T21:00:00, 343.9, 136
2019-10-16T22:00:00, 344.9, 120.2
2019-10-16T23:00:00, 345.7, 124.2``

json:: Response is a json object.