Hello there. I need to calculate a new temperature time serie averaging by day, BUT exactly from 8am to 14 (cutting off the rest of the data). So far I only manage to filter+plot data in the 8-14 time-window but not to get the average Temp in the 8-14 window by day.
Data are from two devices, Tindoor from dev1 in one room and Tindoor_Ref from dev2 in another room.
The 8-14 filter part of the query is a complex elaboration on TIMESTAMP and was provided by a third party PostgreSQL expert.
Thanks 4 help.
Here is my query, producing the graph below, which I need to convert into histogram with avgT(8-14) by day.
SELECT
–CONCAT('Tin ', SPLIT_PART(“Path”, ‘/’, 3)) as metric,
case
when SPLIT_PART(“Path”, ‘/’, 3) = ‘TestRoom’ then ‘Tindoor’
when SPLIT_PART(“Path”, ‘/’, 3) = ‘RefRoom’ then ‘Tindoor_Ref’
end as metric,
CAST(“Value” AS float) AS value,
time
FROM
( select *, CAST(“Time” AS TIMESTAMP WITH TIME ZONE) AS time
from “$schemaName”.“ElaboratedData”
WHERE “DeviceMetricID” IN (
SELECT “ID” from “$schemaName”.“DeviceMetrics” WHERE “Path” like ‘%aula-sicura%temperature_asis’)
AND CAST(“Time” AS TIMESTAMP WITH TIME ZONE) between ‘${__from:date}’ and ‘${__to:date}’
AND DATE_PART(‘hour’, CAST(“Time” AS TIMESTAMP WITH TIME ZONE)) >= 8
AND (DATE_PART(‘hour’, CAST(“Time” AS TIMESTAMP WITH TIME ZONE)) <= 13)
AND “Value” is not null and “Value” <> ‘null’
) AS ED
INNER JOIN “$schemaName”.“DeviceMetrics” DM on ED.“DeviceMetricID” = DM.“ID”
order by
time, metric
-
What Grafana version and what operating system are you using?
Grafana v8.0.3 -
What are you trying to achieve?
plotting mean values by day calculated in daily restricted time-window “8-14” -
How are you trying to achieve it?
adding GROUP BY on PostgreSQL query
tried to follow this solved issue. Cannot use FLUX.
GROUP BY time(1h) FILL(null) not working
-
What happened?
several syntax error -
What did you expect to happen?
-
Can you copy/paste the configuration(s) that you are having problems with?
-
Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
