Time issue with Continuous Query

, ,
1

Hi all,
Let me start saying I’m quite new with Influx and databases in general.
With my Rasberry I’m trying to build a system to monitor energy consumpion.

Hi have cretated a Influx DB callen “Energy_Meter” and the following retention policies:

> show retention policies
name         duration  shardGroupDuration replicaN default
----         --------  ------------------ -------- -------
autogen      48h0m0s   24h0m0s            1        false
2_days       48h0m0s   24h0m0s            1        true
hour_year    8736h0m0s 168h0m0s           1        false
day_infinite 0s        168h0m0s           1        false

I have also create two continuous queries;

> show continuous queries
name: _internal
name query
---- -----

name: Energy_Meter
name         query
----         -----
cq_1h_energy CREATE CONTINUOUS QUERY cq_1h_energy ON Energy_Meter BEGIN SELECT integral(value) / 3600 AS value INTO Energy_Meter.hour_year."shellies/shellyem-C45BBE77DB37/emeter/0/power" FROM Energy_Meter."2_days"."shellies/shellyem-C45BBE77DB37/emeter/0/power" GROUP BY time(1h), entity_id TZ('Europe/Rome') END
cq_1d_energy CREATE CONTINUOUS QUERY cq_1d_energy ON Energy_Meter BEGIN SELECT integral(value) / 3600 AS value INTO Energy_Meter.day_infinite."shellies/shellyem-C45BBE77DB37/emeter/0/power" FROM Energy_Meter."2_days"."shellies/shellyem-C45BBE77DB37/emeter/0/power" GROUP BY time(1d), entity_id TZ('Europe/Rome') END
>
The problem I have is that while with the default retention I can see the correct value of the last two days, using the "day_infinite" retention I could see the measurement of all days (as it shoul be) but shifted of one day.
I mean the measure of day n (bottom graph) is visble as day n-1 (top graph).

grafana
grafana960×720 23.4 KB

Not sure what I did wrong.
Thanks for your support.

2

Hello @solarflor,
Welcome!
First may I ask if you’re new to InfluxDB, why are you running 1.x instead of 2.x?
Can you help me understand your problem better? The first CQ is running as expected but the second CQ is shifting the result by 1 day? If so that’s strange, the retention policy shouldn’t have an effect on the way that CQ transforms the data (just the number of points potentially).

3

Hi Anaisdg,
yes, I’m new to InfluxDB. I’m using Influx 1.8.10 on my Raspberry Pi but don’t ask me why.
Just following a guideline I found on intranet.
Do you suggest I go to 2.x? it works on Debian 32 bit?

Yes, the problem is that I can see the results of yesterday 4th Apr generated with raw data

image
image2000×987 231 KB

plotted as 3rd Apr if I use the agreggation mentioned above

image
image2000×993 234 KB

4

Hi all,
any idea to solve this timing issue?
Thanks