1d spread query - wrong timestamp (previous day 20:00 ?)

I built a query to get a daily spread value (so from midnight to midnight)

My query works as intended and the result is accurate.
However for some reason, the associated timestamp is the previous day at 20:00? why is it and why 20:00? is this timezone related and how can I change this so I get a timestamp within the spread period ? (tried playing with “tz” unsuccessfully)

When I check distinct values they all have correct timestamps within the period.

SELECT spread(“value”) FROM “mcsMQTT” WHERE (“device” = ‘!WEATHER_Acurite-Rain899_rain_mm’) AND time >= 1630296000000ms and time <= 1630382399000ms GROUP BY time(1d) fill(none);

I guess you’re using Influx? Maybe you could share more details about

In principle that should be the solution to this kind of behavior. See also Daily chart starting at midnight - #4 by svetb

Yes, using InfluxDB,
Thanks for the link.
But no matter which timezone I try to use, I get the following:
Tried both via the GUI and directly in the query.

“SELECT spread(“value”) FROM “mcsMQTT” WHERE (“device” = ‘!WEATHER_Acurite-Rain899_rain_mm’) AND time >= now() - 7d GROUP BY time(1d) fill(none) tz(‘America/Montreal’)”

error:"error parsing query: unable to find time zone America/Montreal"

other zone:

message:"InfluxDB Error: error parsing query: unable to find time zone America/Los_Angeles"

?

I also saw this: " The returned timestamps must be in RFC3339 format for the UTC offset or UTC DST to appear."

Does this look right ?

edit: tested with https://www.unixtimestamp.com/, seems right (correct EST time)

SELECT * FROM “mcsMQTT” WHERE (“device” = ‘!WEATHER_Acurite-Rain899_rain_mm’)

name: mcsMQTT
time device value


1629473157598529600 !WEATHER_Acurite-Rain899_rain_mm 33.528
1629479157306529600 !WEATHER_Acurite-Rain899_rain_mm 33.782
1629644980310749600 !WEATHER_Acurite-Rain899_rain_mm 38.862
1629645460017749900 !WEATHER_Acurite-Rain899_rain_mm 43.688
1629654939289750900 !WEATHER_Acurite-Rain899_rain_mm 47.752
1629666578546494900 !WEATHER_Acurite-Rain899_rain_mm 52.07
1630164906617531200 !WEATHER_Acurite-Rain899_rain_mm 52.324
1630165626436531900 !WEATHER_Acurite-Rain899_rain_mm 52.578
1630186685209222700 !WEATHER_Acurite-Rain899_rain_mm 52.832
1630196164480204800 !WEATHER_Acurite-Rain899_rain_mm 53.086
1630221842572229200 !WEATHER_Acurite-Rain899_rain_mm 53.34
1630226222331773200 !WEATHER_Acurite-Rain899_rain_mm 53.594
1630237501915280900 !WEATHER_Acurite-Rain899_rain_mm 53.848
1630262100013281900 !WEATHER_Acurite-Rain899_rain_mm 54.102
1630263780287279700 !WEATHER_Acurite-Rain899_rain_mm 54.356
1630274819264281100 !WEATHER_Acurite-Rain899_rain_mm 54.61
1630275059371283100 !WEATHER_Acurite-Rain899_rain_mm 54.864
1630275239197280800 !WEATHER_Acurite-Rain899_rain_mm 55.118
1630275599373280900 !WEATHER_Acurite-Rain899_rain_mm 55.372
1630275719432280700 !WEATHER_Acurite-Rain899_rain_mm 55.626
1630276859183281100 !WEATHER_Acurite-Rain899_rain_mm 55.88
1630317116622314600 !WEATHER_Acurite-Rain899_rain_mm 56.134
1630317176391315200 !WEATHER_Acurite-Rain899_rain_mm 56.388
1630317236691316300 !WEATHER_Acurite-Rain899_rain_mm 57.15
1630317296446314600 !WEATHER_Acurite-Rain899_rain_mm 58.166
1630317356735315000 !WEATHER_Acurite-Rain899_rain_mm 58.674
1630317416503316100 !WEATHER_Acurite-Rain899_rain_mm 59.436
1630317536559315100 !WEATHER_Acurite-Rain899_rain_mm 60.198
1630319036497316600 !WEATHER_Acurite-Rain899_rain_mm 60.706
1630319096259315800 !WEATHER_Acurite-Rain899_rain_mm 61.214
1630319156547315000 !WEATHER_Acurite-Rain899_rain_mm 61.722
1630319216314315100 !WEATHER_Acurite-Rain899_rain_mm 61.976
1630319276603314700 !WEATHER_Acurite-Rain899_rain_mm 62.484
1630319336369315400 !WEATHER_Acurite-Rain899_rain_mm 63.246
1630319396662316700 !WEATHER_Acurite-Rain899_rain_mm 63.754
1630319456431316500 !WEATHER_Acurite-Rain899_rain_mm 64.262
1630319576490314400 !WEATHER_Acurite-Rain899_rain_mm 64.77
1630319636249315900 !WEATHER_Acurite-Rain899_rain_mm 65.278
1630324256094314800 !WEATHER_Acurite-Rain899_rain_mm 65.532
1630324376152315100 !WEATHER_Acurite-Rain899_rain_mm 65.786
1630324616265314900 !WEATHER_Acurite-Rain899_rain_mm 66.04
1630326895779315200 !WEATHER_Acurite-Rain899_rain_mm 66.294
1630331336370316200 !WEATHER_Acurite-Rain899_rain_mm 66.548
1630331515624317200 !WEATHER_Acurite-Rain899_rain_mm 67.056
1630342555122315800 !WEATHER_Acurite-Rain899_rain_mm 67.31

Hm looks like you’ve hit an old/niche Influx bug: error parsing query: unable to find time zone · Issue #10196 · influxdata/influxdb · GitHub. Maybe you’re using an outdated Influx version?

(also “America/Montreal” is a deprecated time zone, so that first error is to be expected)

If you can’t get the tz() clause to work, you can also add an offset to the GROUP BY time() clause - see Explore data using InfluxQL | InfluxDB OSS 1.8 Documentation. It’s a bit more “manual”, but should yield the same result.

Not sure where the bug is as I’m using 1.8.4…
Anyways, thank you very much for that second option, works perfectly!
However, I guess this would need to be manually adjusted twice a year for DST ?? not good…
Any other potential troubleshooting to look into?

SELECT spread(“value”) FROM “mcsMQTT” WHERE (“device” = ‘!WEATHER_Acurite-Rain899_rain_mm’) AND time >= now() - 7d GROUP BY time(1d,4h) fill(none)

Yeah the DST part is tricky with a hardcoded offset. I can’t think of a nice solution to that.

In general I would recommend trying to find a solution to the InfluxDB timezone error. You haven’t shared much about your environment (OS, etc), but comments on the Github issue I linked to does provide some potential fixes for both Windows and Linux (as well as links to further resources on it). You could also check the Influx forum for anything relevant - and/or ask there.

In terms of general troubleshooting, it’s worth simply googling any error that you get - you’re rarely the first person to encounter it, and the search results can lead you to helpful resources.

Sounds good, will do further troubleshooting on the Influx side. Thanks for your assistance.

For reference: running Windows10 , Influx 1.8.5 and Grafana v7.4.3

I’ve tried installing Golang 1.17 Downloads - The Go Programming Language and restart as suggested but still not working. Guess I’m out of options; will follow the thread for any other solutions.
Thanks

This topic was automatically closed after 365 days. New replies are no longer allowed.