clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3799181 fiddles created (41763 in the last week).

CREATE TABLE tv_smartdevicemeasurement_mqtt (pk int, insert_time timestamptz, data jsonb, sensor int); INSERT INTO tv_smartdevicemeasurement_mqtt VALUES (3003711,'2020-10-03 09:55:54.271738+00','{"smart_device_id": 12, "potenza_kw": 0, "temp": 20.8, "lux": 2.0}',12) , (3003692,'2020-10-03 09:54:54.289131+00','{"smart_device_id": 12, "potenza_kw": 0, "temp": 20.6, "lux": 2.0}',12) , (3003681,'2020-10-03 09:53:54.287502+00','{"smart_device_id": 12, "potenza_kw": 9.0, "temp": 20.5, "lux": 2.0}',12) , (3003670,'2020-10-03 09:52:54.284262+00','{"smart_device_id": 12, "potenza_kw": 9.0, "temp": 20.5, "lux": 2.0}',12) , (3003659,'2020-10-03 09:51:56.382746+00','{"smart_device_id": 12, "potenza_kw": 12, "temp": 20.5, "lux": 2.0}',12) , (3003648,'2020-10-03 09:50:54.279558+00','{"smart_device_id": 12, "potenza_kw": 9.0, "temp": 20.5, "lux": 2.0}',12) , (3003637,'2020-10-03 09:49:56.377988+00','{"smart_device_id": 12, "potenza_kw": 9.0, "temp": 20.5, "lux": 2.0}',12) -- additional data to demonstrate error , (3003630,'2020-10-03 09:48:56.377988+00','{"smart_device_id": 12, "potenza_kw": 0, "temp": 20.5, "lux": 2.0}',12) , (3003630,'2020-10-03 09:47:56.377988+00','{"smart_device_id": 12, "potenza_kw": 10, "temp": 20.5, "lux": 2.0}',12) , (3003630,'2020-10-03 09:46:56.377988+00','{"smart_device_id": 12, "potenza_kw": 10, "temp": 20.5, "lux": 2.0}',12) , (3003630,'2020-10-03 09:45:56.377988+00','{"smart_device_id": 12, "potenza_kw": 0, "temp": 20.5, "lux": 2.0}',12) ;
11 rows affected
 hidden batch(es)


-- 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;
minutes
7
 hidden batch(es)