Hi everybody,
got problems with time delay on grouping data by time with mysql. First: Timestamp is stored as local time in ms with Datatype BigINT in MariaDB. When i use a query without time-grouping for example:
SELECT
ts/1000 AS “time”,
val as “Temperatur”
FROM ts_number
where id = 39 and val >0
everything is shown correct (Time-Settings in Grafana to local time, by changing to UTC, offset local to UTC is present, so in my opinion it is correct).
When i try to group data by time for example with:
SELECT
$__unixEpochGroup(ts/1000,‘1d’) as “time”,
max(ts/1000) as max,
min(ts/1000) as min,
(max(ts/1000)-min(ts/1000))/3600 as testen,
avg(val)*(max(ts/1000)-min(ts/1000))/3600000 as “Hausverbrauch”,
sum(val)/360000 as “Int”
from ts_number
where id = 39 and val>0 and $__unixEpochFilter(ts/1000)
group by time
i wil get an offset of time from local to utc in the grouped data. When i change to UTC in Grafana, time is shown correct, but the time-frame inside the grouping is still the offset (tried out with min and max timestamp for each row)
Not sure, what the problem is. spent a lot of time without results.
Appreciated for help to solve the problem.
Grafana Version: 8.0.2, working in Docker on a Synology NAS. DB is MARIADB10, Data in DB comes from SQL Adapter in IOBroker.
thanks in advance,
domi