DataYours - timezone offset

I’m collecting some data on a 24h basis. When I put the mouse over the bar chart as you can see below, the chart shows 17:00:00 - not 00:00:00 as one would expect for 24 hour collection. Suspiciously that’s 7 hours off from GMT which matches my timezone right now. Is it somehow using GMT for the collection of the data? See attached. This is with openLuup and Grafana.

Is your locale correctly set on the host system?

Yes, as far as I can tell, the timezone and location are set properly. Is there something specific that I should double check? Running on the Raspberry Pi, openLuup in this case.

You could confirm that data returns your actual time zone on the command line. Also, is your database on a different machine that the one producing the data?

Yep, date is correct. And it is all on the same RPi. I can see that it is offset in AltUI as well, so it is not something to do with Grafana.

This is a case where I’m using the storage aggregation method of “sum”. Not sure if that matters or not. I believe it is summing correctly, just not over the correct 24 hour period.

…not sure what you mean by this? Are you saying that the time axis on AltUI plots is incorrect?

see the attached - the transitions are at 5:00 PM local time, not at 00:00:00.

OK, here’s what I think I know:

[ul][li]In the Whisper database, timestamps are stored in UTC. See The Whisper Database — Graphite 1.2.0 documentation
[li]aggregation intervals shorter than a day should work correctly (they are not synced to day boundaries)[/li]
[li]aggregation intervals longer than a day are synced to UTC day boundaries.[/li][/ul]

It turns out that this problem is not unknown, and is actually rather fundamental to the design choice for Whisper using UTC, see this thread and in particular a very good summary in the last post #9 (from 2012!)

Whilst the solution of time-shifting the display doesn’t change the underlying averaging interval (hence the calculation is not really correct) it would look better. The time-shift function is not currently available in my implementation of the Graphite API, but it could be.

A better solution might be to integrate separately over 1 hour aggregations, the result could then be constructed over the correct interval.

So I don’t believe there’s an implementation error here, in so far as I have perpetuated the design flaw in my translation from Python to Lua. At least we know what is going on, and the graphical display reflects exactly what is happening. I’ll give some more thought to how it might be fixed… one possible hack is to NOT use UTC for the Whisper timestamp, but this would be incompatible with existing archives, and also break my adherence to the industry standard.

Ideas welcomed.

The flaw is more or less in the aggregation - when you think of a “day”, you think of a day in your own timezone. (And one that can be an extra hour longer or shorter when daylight savings changes.) You generally don’t think of a day in UTC. I can see why this is done - it’s uniform and consistent. There must be some trigger to the database to make it aware of the aggregation step - can the time that this occurs be changed to the local timezone? But as you point out, this would then no longer be exactly compatible with reference implementation (might be better, though).

This is not a huge deal for me - I could just plot hourly data, but I do think it is nice to be able to see data aggregated over a “local” day in some situations. (By the way, the way I first realized this is that it was indicating that I had run the air conditioning “tomorrow” - and since tomorrow isn’t here yet, that seemed flawed.) Thanks.

Yes, of course that’s true.

There must be some trigger to the database to make it aware of the aggregation step - can the time that this occurs be changed to the local timezone?

No, in fact that’s not the case. Aggregation is done every time a new bit of data is added to the database. The boundaries for each aggregation are simply the (UTC) timestamp rounded to the nearest minute/hour/whatever interval. So, in fact, hourly aggregation would suffer from the same problem in a timezone which had a half-hour offset.

This is not a huge deal for me - I could just plot hourly data, but I do think it is nice to be able to see data aggregated over a "local" day in some situations.

Yes, I can see that’s desirable. I think, in the end, you just have to accept the limitations of what is a rather simple implementation of a data historian - it’s not a fully-fledged database with a footprint to match. I decided on Whisper originally because it had a fixed file size and wouldn’t tax Vera hardware. Now we’re running it on more capable machines.

The Graphite_API backend to which Grafana interfaces has a plug-in feature which makes it easy to add new databases. The implementation is already able to federate data from both Whisper and dataMine. It would be relatively straight-forward to add an SQL database. You’d have to arrange to write data to it, but Grafana could pull it out and plot it along with other data. Grafana, itself, of course, already has its own interfaces to: CloudWatch, elasticsearch, InfluxDB, openTSDB, and Prometheus. It would also be simple enough to register any one of these as an AltUI Data Storage Provider (it already has emoncms and thingspeak.)

We continue this part of the discussion here: DataYours: InfluxDB as an AltUI Data Storage Provider