-- 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.11
…
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';
hours
0.11
…
hidden batch(es)
-- correct (pessimistic, lower estimate):
SELECT trunc((EXTRACT(epoch FROM sum(work))/ 3600)::numeric, 2) AS hours
FROM (
SELECT max(insert_time) FILTER (WHERE kw >= 1) -- last row above threshold per group
- min(insert_time) AS work
FROM (
SELECT *
, count(*) FILTER(WHERE kw >= 1 AND last_kw < 1) OVER (ORDER BY insert_time) AS grp
FROM (
SELECT insert_time, (data->>'potenza_kw')::float AS kw
, lag((data->>'potenza_kw')::float) OVER (ORDER BY insert_time) AS last_kw
FROM tv_smartdevicemeasurement_mqtt
WHERE (data->>'smart_device_id')::int = 12 -- filter section
AND insert_time >= '2020-10-01' -- assuming current time zone
AND insert_time < '2020-11-01'
ORDER BY insert_time
) sub1
) sub2
GROUP BY grp
) sub3
hours
0.08
…
hidden batch(es)
-- alternative (not pessimistic)
SELECT count(*) AS minutes
FROM tv_smartdevicemeasurement_mqtt
WHERE (data->>'smart_device_id')::int = 12
AND insert_time >= '2020-10-01'
AND insert_time < '2020-11-01'
AND (data->>'potenza_kw')::float >= 1;
