Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
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)
;

CREATE TABLE



INSERT 0 11


-- 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



SELECT 1


-- 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



SELECT 1


-- 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



SELECT 1


-- 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



SELECT 1