Daily continuous query gets timestamp of previous day

nonplus · November 3, 2020, 4:01pm

Strange issue: I have a continuous query on InfluxDB 1.8 which sums hourly values into daily totals. The summing works just fine… it’s just that the timestamp added to the sum is one day behind.
So the continuous query reads hourly values for 03-11-2020 correctly, sums them up correctly, but then timestamps the sum with 02-11-2020 00:00 GMT.

I have this with all my daily CQs, here is one example:

CREATE CONTINUOUS QUERY “cq_1d_gas” ON “home_assistant” BEGIN SELECT sum(“value”) AS value INTO “infinite”.“m3” FROM “autogen”.“m3/h” GROUP BY time(1d), entity_id FILL(previous) tz(‘Europe/Amsterdam’) END

So source hourly values are in autogen.m3/h, daily values are written to infinite.m3. All working great, except for the magic 24h time shift. Of course, I checked the time stamps of the hourly values, they are correct. E.g. input hourly values in this case:

1604296800000 0.106
… … …
1604350800000 0.096

leading to a daily summed CQ of
1604268000000 3.419

Many thanks for your help!

Anaisdg · November 4, 2020, 8:56pm

Hello @nonplus,
According to the documentation, " CQs execute at the same interval as the cq_query ’s GROUP BY time() interval, and they run at the start of the InfluxDB database’s preset time boundaries. If the GROUP BY time() interval is one hour, the CQ executes at the start of every hour."

So I think that makes sense with your query. What version of InfluDB are you using? If you’re using 1.8, the easiest solution is for you to enable Flux and use the timeShift() function during query to shift the timestamp: https://docs.influxdata.com/influxdb/v2.0/reference/flux/stdlib/built-in/transformations/timeshift/.
Alternatively, you can use Kapacitor to do the same. https://docs.influxdata.com/kapacitor/v1.5/nodes/shift_node/

nonplus · November 4, 2020, 9:33pm

Thanks for your response – but here’s the problem I’m facing:
CQ executes at 04-11-2020 00:00 and sums up all values starting 03-11-2020 00:00 until 03-11-2020 23:59:59 - which is correct.
The resulting value however is assigned the timestamp 02-11-2020 00:00. While that should be 04-11-2020 00:00, which is the time the CQ executes. So the timestamp is even two days off?!
I really don’t understand what’s going wrong here.

Giovanni_Luisotto · November 4, 2020, 11:19pm

The timestamp assigned to the result of the query is not the last, but the first of the considered range.

CQ basic explanation (skip it if you know how CQ works)

I’ll make an example with hours because the principle is exactly the same (as influxdb is not aware of what a day/month/year is… it just knows how to translate it into a time interval)

CREATE CONTINUOUS QUERY _cq_ ON _db_ BEGIN 
SELECT stuff {...}
GROUP BY time(1h),*
END

This query will run with:

frequency - every hour at hh:00:00
time range - 1 hour
time grouping interval - 1 hour

as a sample, if it runs at 15:00:00

will consider the data from 14:00:00 to 15:59:59
the data will be grouped into a single point (1h)
the time of this point will be 14:00:00
since no timezone is specified UTC is used

In your case I expect your data to be saved at the beginning of the day (beginning of the considered interval).
Luckily you can easily replicate the CQ behavior by doing a query manually

SELECT sum(“value”) AS value INTO “infinite”.“m3_test” FROM “autogen”.“m3/h” WHERE time >= '2020-11-02T00:00:00Z' AND time <='2020-11-04T00:00:00Z' GROUP BY time(1d), entity_id FILL(previous) tz(‘Europe/Amsterdam’)

I expect it to create 2 data points in the new measurement “m3_test”, one at 2020-11-02 00:00:00 and the other at 2020-11-03 00:00:00.

I store the data in a different measurement “m3_test” so it’s easy to delete them (using a simple DROP MEASUREMENT “m3_test”).

try it out and let us know the result

nonplus · November 5, 2020, 9:11pm

Thanks Giovanni, but this does not address my problem. My problem is that (hourly) data recorded on THREE November, is summed up by the CQ into a value that is timestamped TWO November 00:00.
Following your logic (which is indeed what I’d expect to happen), the CQ should run FOUR November 00:00 (which indeed it does, given that it picks up the right hourly values), and should generate a daily summed value timestamped THREE November 00:00 (the beginning of the period concerned). But it’s timestamped TWO.
Similarly, the hourly data generated on 2 Nov is aggregated by the CQ into a daily value timestamped 1 Nov. Et ceterera.
So all the summations are correct, but the daily values generated by the CQ consistently run one day behind.

Giovanni_Luisotto · November 7, 2020, 12:55pm

well, that adds something for sure… (that even hourly data are saved with a 1d offset)
I think you are better open an issue on github about this one.

Just out of curiosity, what if you run the same query “manually” as described in my previous post? does its result get a 1d offset too?

nonplus · November 7, 2020, 12:56pm

My bad. Thanks for your suggestion, it made me discover that the problem is probably with Grafana. I am on UTC+1, which means that daily continuous queries run at 00:00 UTC+1, which equals 23:00 UTC on the previous day. and receive corresponding timestamp. Grafana also uses UTC+1, but nonetheless for some reason allocates the observation to the previous day – which is true in UTC, but not in my timezone, UTC+1.
So continuing the search for a solution in the Grafana realm and leaving this here for reference… thanks!