Two seemingly incompatible "ft³" measurements - how to combine?

I’m using InfluxDB to store Home Assistant state data, and the units of measurement for my gas meter recently changed from “ft3” to “ft³”. Initially, InfluxDB had two separate measurements, one “ft3” and one “ft³” before I realized the change. I wanted to combine the data under the new “ft³” measurement, so I dropped all “ft3” measurements and then used the “Write data” function from Chronograf to re-add the same data under the “ft³” measurement. Now Chronograf shows only one measurement for my gas data, “ft³”, but the data prior to the change cannot be viewed in the same visualization as the data after the change. For example, this is a table spanning pre-change and post-change data:

SELECT mean("value") AS "mean_value" FROM "home_assistant"."autogen"."ft³" WHERE time > now() - 23d AND time < now() - 15d AND "entity_id"='meter_gas' GROUP BY time(1d) FILL(null)

There is no data shown on 9/4 or later, which is the date when the change from “ft3” to “ft³” occurred. However, if I adjust ONLY the timespan in the query, as below, the table displays correctly data for each day (though I can’t embed another image as a new user).

SELECT mean("value") AS "mean_value" FROM "home_assistant"."autogen"."ft³" WHERE time > now() - 17d AND time < now() - 10d AND "entity_id"='meter_gas' GROUP BY time(1d) FILL(null)

Oddly, if the query starts at time < now() - 18d or time < now() - 19d, there are no results, regardless of time span submitted.

It seems like Influx still doesn’t understand that the measurement for all this data is the same, despite Chronograf only showing “ft³” and no measurements of “ft3”. Is there some metadata that I need to clear out or regenerate, or is there another solution?

hmm I’m not sure. Have you tried querying for the data with the API to verify whether or not it’s an issue with Chronograf or InfluxDB?

I had not tried that, but now I have, and the results are the same.

If I query without specifying a time range, I get results up until 2021-09-04T19:34:33.245552896Z, via the command below:

curl -G 'http://localhost:8086/query?pretty=true&u=debug&p=password' --data-urlencode "db=home_assistant" --data-urlencode "q=SELECT \"value\" FROM \"ft³\" WHERE \"entity_id\"='meter_gas'"

If I include AND \"time\">'2021-09-04T19:34:33.245552896Z' at the end of that command, I get no results:

    "results": [
            "statement_id": 0

However, if I start the query after the change, e.g. AND \"time\">'2021-09-06T00:00:00.000000000Z', I get all results from 2021-09-06 up to now.

I did not change the “ft³” part of the query at all, just the time ranges.

Ahh @strikeir13,
Wait so it’s working now if you include the time clause? Or only when you include that specific timestamp? That’s very odd.

It’s not working correctly now - when querying the data, it exhibits the same odd phenomenon:

  • Starting the time range before 9/4/2021 (or omitting the time clause) yields results up to 9/4/2021 but not after 9/4/2021.
  • Starting the time range after 9/6/2021 yields results on and after 9/6/2021.
  • Any query that spans the 9/4/2021 - 9/6/2021 time range only includes results before 9/4/2021, despite data existing after 9/6/2021 for the same “ft³” measurement

The expected behavior is that since the “ft³” measurement is the same before and after 9/4/2021, querying for a time range that spans pre-9/4/2021 - post-9/6/2021 would return continuous results. However, despite “ft³” measurement data existing before 9/4/2021 and after 9/6/2021, no query that I can write returns results from both before and after those dates, which seems incorrect.

Maybe this will be clearer - I’ve linked to the output of two different queries:

  • The first query was for data where time > 9-1-2021
curl -G 'http://localhost:8086/query?pretty=true&u=debug&p=password' --data-urlencode "db=home_assistant" --data-urlencode "q=SELECT \"value\" FROM \"ft³\" WHERE \"entity_id\"='meter_gas' AND \"time\">'2021-09-01T00:00:00.000000000Z'"

results here:

  • The second query was for data where time > 9-5-2021
curl -G 'http://localhost:8086/query?pretty=true&u=debug&p=password' --data-urlencode "db=home_assistant" --data-urlencode "q=SELECT \"value\" FROM \"ft³\" WHERE \"entity_id\"='meter_gas' AND \"time\">'2021-09-05T00:00:00.000000000Z'"

No results returned::

    "results": [
            "statement_id": 0
  • The third query was for data where time > 9-6-2021 and < 9-10-2021 (abridged for clarity, omitting the < 9-10-2021 clause yields data up to right now)
curl -G 'http://localhost:8086/query?pretty=true&u=debug&p=password' --data-urlencode "db=home_assistant" --data-urlencode "q=SELECT \"value\" FROM \"ft³\" WHERE \"entity_id\"='meter_gas' AND \"time\">'2021-09-06T00:00:00.000000000Z' AND \"time\"<'2021-09-10T00:00:00.000000000Z'"

results here:

As you can see, despite the first query including the time ranges spanned by the second and third query, no results are returned by the first query for those time ranges.

Anything else I can try?

Hello @strikeir13,
Your results make sense to me. It looks like the last point you wrote was at “2021-09-04T19:34:33.245552896Z”. So the other query would miss it.

Then I’m not explaining the situation clearly - the same query, edited only for time ranges, yields inconsistent results:

  • Querying for all “ft³” measurements with no time clause only yields results up to 9-4-2021
  • Querying for “ft³” measurements where time > 9-6-2021 shows that there IS data for the “ft³” measurement after 9-4-2021
  • Any query with time > 9-4-2021 does not show any results after 9-4-2021, despite there being data for the “ft³” measurement after 9-4-2021 (as shown by querying with time > 9-6-2021)

The expected result, since all the data has the same “ft³” measurement now, is that querying for ALL data (no time clause) would include results before 9-4-2021 and after 9-4-2021. This is not the case.