Historical query?select fails on less than 207 minutes

Support
1

Low-Resolution tests

This query works, generating 207 minutes of low-resolution (the default) data from 2024-01-01 12:00 to 2024-01-01 15:27:

$ wget -q -O - 'http://IOTAWATT/query?select=[time.local.unix,FurnaceBlower.watts]&begin=2024-01-01T12:00&end=2024-01-01T15:27&group=auto&format=csv' | wc
206     414    3826

(The missing newline on the last output record makes wc count only 206 lines instead of 207.)

If I ask for 206 minutes of low-resolution data – one minute less (to 15:26) – I get nothing:

$ wget -q -O - 'http://IOTAWATT/query?select=[time.local.unix,FurnaceBlower.watts]&begin=2024-01-01T12:00&end=2024-01-01T15:26&group=auto&format=csv'
1704110400, null   (also missing newline)

If I ask again for 206 minutes of low-resolution data but shift the start time (12:01) instead of the end time I get nothing:

$ wget -q -O - 'http://IOTAWATT/query?select=[time.local.unix,FurnaceBlower.watts]&begin=2024-01-01T12:01&end=2024-01-01T15:27&group=auto&format=csv'
1704110460, null   (also missing newline)

Asking for JSON output gives: [[1704110460,null] (also missing newline and the brackets are not matched)

Asking for 206 minutes of low-resolution data from January 2025 gives the same empty result.

Asking for 206 minutes of low-resolution data from January 2026 gives 412 records (every 30 seconds):

$ wget -q -O - 'http://IOTAWATT/query?select=[time.local.unix,FurnaceBlower.watts]&begin=2026-01-01T12:00&end=2026-01-01T15:26&group=auto&format=csv' | wc
411     824    7526

High-Resolution tests

If I ask for 414 minutes of resolution=high data from 2024, it works:

$ wget -q -O - 'http://IOTAWATT/query?select=[time.local.unix,FurnaceBlower.watts]&begin=2024-01-01T12:00&end=2024-01-01T18:54&group=auto&resolution=high&format=csv' | wc
413     828    7638

If I ask for 413 minutes of resolution=high data from 2024 – one minute less (to 18:53) – I get nothing:

$ wget -q -O - 'http://IOTAWATT/query?select=[time.local.unix,FurnaceBlower.watts]&begin=2024-01-01T12:00&end=2024-01-01T18:53&group=auto&resolution=high&format=csv'
1704110400, null   (also missing newline)

Questions

  1. Why can’t I ask for fewer than 207 minutes of low-resolution data for past years?
  2. Why can’t I ask for fewer than 414 minutes of high-resolution data for past years?
  3. Why doesn’t the last record output (both CSV and JSON) have a newline on the end?
2

Short answer is that you have uncovered a couple of new problems with query. Details follow:

First lets clarify the terminology. When you say “low resolution” and “high resolution” you are referring to the resolution parameter of the query. That parameter causes query to compute an interval that results in 400 or 800 lines of response respectively when &group=auto. It has no effect when group is absolute as in “hourly”. I bring this up because there is another notion of resolution in the datalogs where the last 12 months have data at 5 second intervals and older data in the history log is at 1 minute intervals. If you query 206 minutes of data from the past 12 months, you will not see this problem.

This leads into the cause of the problem. As it happens, 207 minutes is the threshold where the “auto” algorithm produces an interval of 60 seconds, so it works fine when applied to the 60 second data in the history log. When less than 207 is requested, the algorithm sets the interval to 30 seconds.

The bug is that when query attempts to advance from the initial 12:00:00 entry to the 12:00:30 (or from the 12:01:00 to 12:01:30) the datalog subsystem returns the next best thing - the 12:00:00 record. Fail-safe code in query recognizes that it’s not moving forward and aborts the query.

What should happen?

First, we should never get this far. There is no useful 30 second data available in the history log, so if the query is outside of the scope of the current log, the auto interval should be set to 60 seconds.

Second, when the fail-safe mechanism triggers, some response has already been sent, so the CSV or JSON should be cleanly terminated and an error indication should be appended. At this point it can be too late to send an error HTTP code.

Is there a workaround?

Sort of. If you want less than 207 minutes of data, ask for 207 and subsequently discard the excess. The needed data will be at 1 minute intervals regardless.

Same issue. The &group=auto is trying to generate 800 lines of return rather than 400.

I believe that the json of a successful query has the appropriate syntax.

CSV format data does not have the CRLF at the end. It has never been a problem but while I am fixing the other issue, I’ll add that in.

1 Like
3

I’m tweaking my work-around code. So the “historical” archive (one minute resolution) is anything older than 365 days? I only need the work around if the start date of my query is more than 365 days in the past? Is “365 days before now” the right number here? I can of course run the query, have it fail, and then apply the work-around, but if there is a known number I can use to improve the odds of the query working the first time, I’d like to use it.

I note that IotaWatt code releases are years apart, so am I right that the fixes you are making may not appear for another year or so?

4

If you look at the status display, the beginning of the current log is displayed. You can get that using the API that status uses. It’s not documented but you can just turn on the browser debugger while status is updating to see all you need to know.

A simpler solution is, yes, assume anything older than 365 days from now is one minute only. The algorithm is actually to limit the current log to 365 x 24 x 60 x 12 entries and wrap when that is reached, so if you have any downtime, the log may start earlier, but you are guaranteed 365 days.

I have some other balls in the air right now but will probably cut off at the end of the summer and publish a release. I’m considering whether to try to roll in some improvements from the stalled ESP32 effort, but that is a one way upgrade so regression would not be possible. Can’t decide if or how to do that.

1 Like