Month and year data tags not in local timezone

[mcbirse]

I've noticed what seems to be an issue with tagging of data by month/year, in that it seems to tag the data points in UTC regardless of the tz('your/timezone') used in the CQ query.

Here's an example, where you see the "month" tag of my data is changing month at 10am, since my timezone is +10 (at the moment) 😞

You can see the issue if you query the InfluxDB directly.

Three query examples below:

1. First showing query using time format in my timezone (for start of Sep, note month tag shows "Aug")
2. Second query is the same, but used time format in UTC and added tz() just to ensure same data is output
3. Third query shows data returned for time from 9:55am to 10:05 am - you can see this is where the month tag changes over to "Sep"

InfluxDB shell version: 1.8.10
> SELECT * FROM autogen.http WHERE time >= '2022-09-01T00:00:00+10:00' AND time <= '2022-09-01T00:05:00+10:00'
name: http
time                from_grid          from_pw            home               host month percentage         solar              to_grid            to_pw url year
----                ---------          -------            ----               ---- ----- ----------         -----              -------            ----- --- ----
1661954400000000000 7.636363636363637  316.3636363636364  318.34090909090907      Aug   56.326253953908726 -8.818181818181818 1.0909090909090908 0         2022
1661954460000000000 3.909090909090909  317.27272727272725 313.6818181818182       Aug   56.281066425666516 -8.909090909090908 7.909090909090909  0         2022
1661954520000000000 8.363636363636363  302.72727272727275 299.6818181818182       Aug   56.23587889742431  -8.727272727272727 2.272727272727273  0         2022
1661954580000000000 14.090909090909092 294.54545454545456 296.8863636363636       Aug   56.19069136918211  -8.818181818181818 1.9090909090909092 0         2022
1661954640000000000 9                  298.1818181818182  303.77272727272725      Aug   56.1455038409399   -8.636363636363637 2.5454545454545454 0         2022
1661954700000000000 9.818181818181818  300                299.52272727272725      Aug   56.09278505799066  -8.636363636363637 0.6363636363636364 0         2022
> SELECT * FROM autogen.http WHERE time >= '2022-08-31T14:00:00Z' AND time <= '2022-08-31T14:05:00Z' tz('Australia/Sydney')
name: http
time                from_grid          from_pw            home               host month percentage         solar              to_grid            to_pw url year
----                ---------          -------            ----               ---- ----- ----------         -----              -------            ----- --- ----
1661954400000000000 7.636363636363637  316.3636363636364  318.34090909090907      Aug   56.326253953908726 -8.818181818181818 1.0909090909090908 0         2022
1661954460000000000 3.909090909090909  317.27272727272725 313.6818181818182       Aug   56.281066425666516 -8.909090909090908 7.909090909090909  0         2022
1661954520000000000 8.363636363636363  302.72727272727275 299.6818181818182       Aug   56.23587889742431  -8.727272727272727 2.272727272727273  0         2022
1661954580000000000 14.090909090909092 294.54545454545456 296.8863636363636       Aug   56.19069136918211  -8.818181818181818 1.9090909090909092 0         2022
1661954640000000000 9                  298.1818181818182  303.77272727272725      Aug   56.1455038409399   -8.636363636363637 2.5454545454545454 0         2022
1661954700000000000 9.818181818181818  300                299.52272727272725      Aug   56.09278505799066  -8.636363636363637 0.6363636363636364 0         2022
> SELECT * FROM autogen.http WHERE time >= '2022-09-01T09:55:00+10:00' AND time <= '2022-09-01T10:05:00+10:00'
name: http
time                from_grid          from_pw home               host month percentage        solar             to_grid            to_pw              url year
----                ---------          ------- ----               ---- ----- ----------        -----             -------            -----              --- ----
1661990100000000000 0.6363636363636364 0       392.74999999999983      Aug   65.5294472059045  5994              631.9090909090909  4960.909090909091      2022
1661990160000000000 0                  0       408.56818181818164      Aug   66.09429130893206 5899.909090909091 582.2727272727273  4904.545454545455      2022
1661990220000000000 2.272727272727273  0       372.4318181818182       Aug   66.58382286488929 6012.727272727273 1212.5454545454545 4420.909090909091      2022
1661990280000000000 0                  0       387.5681818181818       Aug   67.15619822262389 7179.272727272727 1797.3636363636363 4992.727272727273      2022
1661990340000000000 4.363636363636363  0       381.74999999999994      Aug   67.72857358035849 6794.363636363636 1478.8181818181818 4934.545454545455      2022
1661990400000000000 0                  0       387.1136363636362       Sep   68.28588642867902 6869.181818181818 1483.8181818181818 4992.727272727273      2022
1661990460000000000 2.5454545454545454 0       375.24999999999983      Sep   68.79048049405031 5788.454545454545 822.9090909090909  4585.454545454545      2022
1661990520000000000 5.909090909090909  0       389.9318181818181       Sep   69.33273083295677 5745.818181818182 814.1818181818181  4557.272727272727      2022
1661990580000000000 6.545454545454546  0       368.81818181818176      Sep   69.80719987949993 4919.727272727273 289.6363636363636  4255.454545454545      2022
1661990640000000000 9.909090909090908  0       369.70454545454544      Sep   70.2440126525079  3786.181818181818 9.545454545454545  3417.2727272727275     2022
1661990700000000000 5                  0       375.6590909090908       Sep   70.65070040668775 5927.909090909091 1192.2727272727273 4366.363636363636      2022

Because of this issue, grouping by month/year tags seems completely pointless and useless, unless you live in UTC timezone and do not have daylight savings!

Is anyone else seeing this issue, or is it just a problem with my setup/system?

I have done some further digging to understand where the month/year tags come from, and I think this is configured in telegraf.conf?

[[processors.date]]
	tag_key = "month"
	date_format = "Jan"

[[processors.date]]
	tag_key = "year"
	date_format = "2006"

Checking docs for telegraf, and I discovered the below:

Configuration

# Dates measurements, tags, and fields that pass through this filter.
[[processors.date]]
  ## New tag to create
  tag_key = "month"

  ## New field to create (cannot set both field_key and tag_key)
  # field_key = "month"

  ## Date format string, must be a representation of the Go "reference time"
  ## which is "Mon Jan 2 15:04:05 -0700 MST 2006".
  date_format = "Jan"

  ## If destination is a field, date format can also be one of
  ## "unix", "unix_ms", "unix_us", or "unix_ns", which will insert an integer field.
  # date_format = "unix"

  ## Offset duration added to the date string when writing the new tag.
  # date_offset = "0s"

  ## Timezone to use when creating the tag or field using a reference time
  ## string.  This can be set to one of "UTC", "Local", or to a location name
  ## in the IANA Time Zone database.
  ##   example: timezone = "America/Los_Angeles"
  # timezone = "UTC"

I wonder if this is the problem, and telegraf.conf should be including a timezone value in the [[processors.date]] section to ensure tags for month/year are localized?

[mcbirse]

I have done some testing of this issue. I'm still unsure if this is a problem only on my system, or other people experience the same problem?

To test this, I changed my system time to the beginning of Oct and let it start logging data (without changing anything else at this point).

The logged data was being tagged with a month value of "Sep" (when it should be Oct) which can be seen on the yellow plot line below.

I then edited telegraf.conf and added a timezone value as below:

[[processors.date]]
        tag_key = "month"
        date_format = "Jan"
        timezone = "Australia/Sydney"

[[processors.date]]
        tag_key = "year"
        date_format = "2006"
        timezone = "Australia/Sydney"

The month tag was then being set to "Oct" which is correct for my timezone and can be seen on the green plot line above.

I tried changing the timezone value in telegraf.conf to "Local" but that didn't work - it changed the tag back to "Sep". I'm guessing this could be due to the timezone in the docker containers actually being in UTC?

# docker exec -it telegraf sh -c "cat /etc/timezone; date"
Etc/UTC
Fri Sep 30 16:31:32 UTC 2022

Given this, it seems the timezone should be configured in telegraf.conf to the users timezone, to ensure the month/year tags that are applied to data points of InfluxDB are actually correct?

[jasonacox]

Awesome work @mcbirse !!! You are brilliant. This is a bug that has been there since the start (so unfortunately our tags are all bad). I'm seeing this on my system showing a bad month tag change happening at 8/31 @ 17:00 localtime which makes sense since I'm UTC-7 in Los Angeles.

I expect this is causing errors in any the graphs that use the tags for grouping. Correct me if I'm wrong, but none of the dashboard graphs are currently using these tags which explains why this wasn't discovered before now. Getting the correct tags will be helpful for addition visualization as well as auxiliary scripts or tools using this data.

Again, thank you @mcbirse ! Great detective work!

[mcbirse]

No worries Jason, I'm glad I found this, so we can fix it! 😊

I expect this is causing errors in any the graphs that use the tags for grouping. Correct me if I'm wrong, but none of the dashboard graphs are currently using these tags which explains why this wasn't discovered before now.

Yes it would be if tags were used for grouping. But you are correct - fortunately I cannot find any queries in the dashboards where a GROUP BY "month" has actually been used.

Going forward though, it will be good to ensure this is working correctly. It does give options to query data by the "month" tag which could be useful, for instance as per the heatmap discussion.

I started looking into how to fix the tags in existing data. It's not simple. 😞

There seems to be a lot of limitations with InfluxDB, so you cannot simply update tag values. The process seems to be: export data to a file, change data points with the wrong tags, import the data back.

Then you need to delete the existing data in InfluxDB with the wrong tags, as the import will not "update" existing points, it will add additional points with the correct tag.

Deleting data is another issue: you can't delete measurements from specific retention policies - only all of them.

i.e. this won't work:

DELETE FROM autogen.http WHERE month='Jun' AND time >= '2022-07-01T00:00:00+10:00' AND time < '2022-08-01T00:00:00+10:00'
ERR: error parsing query: retention policy not supported at line 1, char 1

But you could do this:

DELETE FROM http WHERE month='Jun' AND time >= '2022-07-01T00:00:00+10:00' AND time < '2022-08-01T00:00:00+10:00'

However, it will delete the data in "http" of ALL retention policies. i.e. autogen, raw, kwh, daily, grid, vitals, etc.

As long as the data that is imported back includes all of that though, I guess it is doable. I haven't fully worked out the process yet though.