-- original
WITH cte AS (
SELECT mt.insert_time,
(DATE_PART('day', lag(mt.insert_time) OVER w - mt.insert_time) * 24 + --Calculates the delta between the two measurements in seconds
DATE_PART('hour', lag(mt.insert_time) OVER w - mt.insert_time)* 60 +
DATE_PART('minute', lag(mt.insert_time) OVER w - mt.insert_time) * 60 +
DATE_PART('second', lag(mt.insert_time) OVER w - mt.insert_time)) as total_seconds
FROM tv_smartdevicemeasurement_mqtt mt
WHERE (mt.data->>'smart_device_id')::Int = 12 -- Filter Section
AND (mt.data->>'potenza_kw')::float > 1
AND date_part('month', mt.insert_time) = 10
AND date_part('year', mt.insert_time) = 2020
WINDOW w AS (order by insert_time desc)
)
-- calculate total sum of hours and days over temp cte table
SELECT TRUNC(sum("total_seconds")::numeric/3600, 2) AS "Hours" --Hours
FROM cte;
Hours
0.06
…
hidden batch(es)
-- simpler, faster:
SELECT trunc((EXTRACT(epoch FROM max(insert_time) - min(insert_time)) / 3600)::numeric, 2) AS hours
FROM tv_smartdevicemeasurement_mqtt
WHERE (data->>'smart_device_id')::int = 12 -- filter section
AND (data->>'potenza_kw')::float > 1
AND insert_time >= '2020-10-01' -- assuming current time zone
AND insert_time < '2020-11-01' -- like jjanes already suggested
