Time zone and group by day in influxdb

Posted on by Joachim L

All right, time for a slightly technical one.

At my current job, we do a lot of work on time series values, and we have recently started using InfluxDb, a blazingly fast timeseries database written in go. In order for our customers to get an overview of the devices they are operating, they want to see a certain metric, on a per-day basis. This meant we had to find a way to group the data by day. InfluxDb, being written for tasks like this, has a very nice syntax for time grouping:

select mean(value) from reportedValues where time > 1508283700000000000 group by time(1d), deviceId

The query above returns a nice list of exactly what we asked for – a list of the devices in question and their average value, grouped by day:

name: reportedValues
tags: deviceId='bathroom-window'
time mean
---- ----
2017-10-17T00:00:00Z
2017-10-18T00:00:00Z 1.02

name: reportedValues
tags: deviceId='kitchen-window'
time mean
---- ----
2017-10-17T00:00:00Z 0.4
2017-10-18T00:00:00Z 0.75

We did run into an issue, however, with time zones. Our customers are in different time zones (none of the UTC, which all values in influxdb are stored as), so when grouping on days especially, we had to find a way to group on day-in-the-timezone-in-question.

Luckily, v1.3 of influxdb introduced the time zone clause. Appending TZ(‘Europe/Oslo’) to the query above, should, in theory, give us the same time series grouped slightly differently. We did run into a slight roadblock here, though. The query

select mean(value) from reportedValues where time > 1508283700000000000 group by time(1d), deviceId TZ('Europe/Oslo')

returned

ERR: error parsing query: unable to find time zone Europe/Oslo

and we got the same result regardless of which time zone we tried (even the one mentioned in the documentation, “America/Los_Angeles”, failed.

I then tried the exact same query on a linux VM I had running, and lo and behold:

name: reportedValues
tags: deviceId='bathroom-window'
time mean
---- ----
2017-10-18T00:00:00+02:00 1.02

name: reportedValues
tags: deviceId='kitchen-window'
time mean
---- ----
2017-10-18T00:00:00+02:00 0.6333333333333334

(note that both the averages are different because of the time difference and that the time stamps reflect the time zone of the query and result.)

So obviously, this was something windows specific. I noticed that the github PR which added the TZ clause uses a go library called time, calling the LoadLocation function. The docs for that function states that “The time zone database needed by LoadLocation may not be present on all systems, especially non-Unix systems”, so I was obviously on to something. There’s a go user reporting something similar at at https://github.com/golang/go/issues/21881, and the title of that issue solved this for me: To get this to work on my local windows machine, all I had to do was

install go and restart the influx daemon (influxd.exe)