Timezone bug

[Theofilos-Chamalis]

Greetings,

and thank you all for developing such an amazing product as Influxdb!

I want to report a bug that I ve noticed with the new 1.3.1 version of InfluxDB regarding the new and useful Timezone feature you added. The bug is that when I query my database to get me some sensor readings that happened within a time range, for example at the date: 26-07-2017 and I add the new tz('Europe/Athens') function at the end, which is +03 hours from UTC, it does not display the readings that happened at 25-07-2017 between 21:00 - 24:00 and also displays some that normally do not belong there, which are the ones from 21:00 - 24:00 of 26-07-2017 that belong to 27-07-2017 in my Timezone.

The reason for this I believe is that the query first fetches the UTC values of that specific date and then applies the Timezone function tz(), where the correct thing to do is to first apply the tz() function and then see which readings belong to the requested time range.

Below I show you an example to be more clear on this:

> select * from events where dev_id='yyyyyyyyyyyy' and time > '2017-07-25' and time < '2017-07-26' tz('Europe/Athens')
name: events
time                      alert country dev_id       host         location
----                      ----- ------- ------       ----         ------------
2017-07-25T05:50:50+03:00 true  FRA     yyyyyyyyyyyy xxxxxxxxxxxx Test_Home1
2017-07-25T08:41:40+03:00 true  FRA     yyyyyyyyyyyy xxxxxxxxxxxx Test_Home1
2017-07-25T11:32:50+03:00 true  FRA     yyyyyyyyyyyy xxxxxxxxxxxx Test_Home1
2017-07-25T14:24:00+03:00 true  FRA     yyyyyyyyyyyy xxxxxxxxxxxx Test_Home1
2017-07-25T17:15:10+03:00 true  FRA     yyyyyyyyyyyy xxxxxxxxxxxx Test_Home1
2017-07-25T20:05:50+03:00 true  FRA     yyyyyyyyyyyy xxxxxxxxxxxx Test_Home1
2017-07-25T22:57:20+03:00 true  FRA     yyyyyyyyyyyy xxxxxxxxxxxx Test_Home1
2017-07-26T01:48:30+03:00 true  FRA     yyyyyyyyyyyy xxxxxxxxxxxx Test_Home1


> select * from events where dev_id='yyyyyyyyyyyy' and time > '2017-07-26' and time < '2017-07-27' tz('Europe/Athens')
name: events
time                      alert country dev_id       host         location
----                      ----- ------- ------       ----         ------------
2017-07-26T06:21:20+03:00 true  FRA     yyyyyyyyyyyy xxxxxxxxxxxx Test_Home1
2017-07-26T09:42:30+03:00 true  FRA     yyyyyyyyyyyy xxxxxxxxxxxx Test_Home1
2017-07-26T13:04:30+03:00 true  FRA     yyyyyyyyyyyy xxxxxxxxxxxx Test_Home1
2017-07-26T16:26:00+03:00 true  FRA     yyyyyyyyyyyy xxxxxxxxxxxx Test_Home1
2017-07-26T19:48:00+03:00 true  FRA     yyyyyyyyyyyy xxxxxxxxxxxx Test_Home1
2017-07-26T23:09:40+03:00 true  FRA     yyyyyyyyyyyy xxxxxxxxxxxx Test_Home1

As you can see the last reading from the 1st query is wrongly placed there and is
missing from the 2nd query that I did for the next day.

Hope this helps

[jsternberg]

It looks like the times are parsed in UTC so you get a start and end time that are in UTC.

For this use case, it can be fixed because the method of parsing date literals doesn't have time zone information. For parsing anything with times, you often have to include the time zone offsets in the time because RFC3339 format contains time zone information.

I'll fix this so time literals without time zone information get parsed with the time zone and we'll have to clarify the other behavior in the docs.

[Theofilos-Chamalis]

Thank you very much for the quick response and all the work on the TICK stack,

A section for the Timezone handling/usage in the docs would be very useful indeed as this is used by many companies that have customers across the world.

Have a good day.

[sebito91]

This is something we've seen too, and get away with it at visualization time (set grafana to local tz). Not ideal, but works.

Having this actual thing offset in the query language itself is awesome, so a big 👍 from me. We did something along these lines in the logparser telegraf plugin:

influxdata/telegraf#2882

[Theofilos-Chamalis]

Yes, Grafana has that useful feature and I use it too, but alongside Grafana Dashboards I have also made an Android app that visualizes the data and it has issues there, so I will definitely need it for this.

I will try to find a temporary workaround for it until it gets fixed.