clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3799110 fiddles created (41779 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) ;
7 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.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
hours
0.06
 hidden batch(es)