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)
