Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
/* ASSUMING:
- Postgres 15.
- All columns are actually `NOT NULL`, you just forgot to declare columns as such.
- Evey "light" has a distinct `id_dec` and a distinct `label`. Having both in `switch_times` is redundant. (Normalization!)
- A light is "switched on" if the most recent earlier entry has `is1 IS TRUE`. Else it's considered "off".
- The order of rows is established by `ts` (**not** `id` as used incorrectly in your query.
- Consecutive entries that do not have to *change* the state.
- No duplicate entries for `(id_dec, ts)`. (There is a unque index enforcing that.)
- There is no minimum or maximum time interval between entries.
- "The 25th" is supposed to mean 'tstzrange [2022-11-25 0:0+02, 2022-11-26 0:0+02)' (Note the time zone offset.)
- You want results for all labels that were switched on at all during the given time interval.
- There is a table "labels" with one distinct entry per relevant light. If you don't have one, create it.
*/
CREATE TABLE switch_times (
  id serial PRIMARY KEY
, is1 boolean
, id_dec integer
, label text
, ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE


-- reduced sample
INSERT INTO switch_times (id, is1, id_dec, label, ts) VALUES
-- added "on" events before the 25th
  (-1, true, 43, '0x2B MH2', '2022-11-24 19:56:43.210707+02')
, (0, true, 91, '0x5B tekninen tila', '2022-11-24 21:00+02')
  
-- reduced sample
,  (1, false, 43, '0x2B MH2', '2022-11-25 19:56:43.210707+02')
, (2, false, 91, '0x5B tekninen tila', '2022-11-25 19:56:43.23333+02')
, (3, false, 44, '0x2C MH3', '2022-11-25 19:56:43.370094+02')
, (4, false, 91, '0x5B tekninen tila', '2022-11-25 19:57:11.714872+02')
, (5, false, 11, '0xB viinikellari', '2022-11-25 19:57:12.21112+02')
, (6, false, 12, '0xC autotalli ', '2022-11-25 19:57:12.259479+02')
, (7, false, 13, '0xD etupihan valot', '2022-11-25 19:57:12.322777+02')
, (8, false, 14, '0xE KHH ja KH', '2022-11-25 19:57:12.385126+02')
, (9, false, 41, '0x29 MH1', '2022-11-25 19:57:12.496921+02')
, (10, false, 42, '0x2A parvi', '2022-11-25 19:57:12.547813+02')
, (11, false, 43, '0x2B MH2', '2022-11-25 19:57:12.617379+02')

, (51, true, 91, '0x5B tekninen tila', '2022-11-25 20:05:21.043614+02')
, (52, true, 11, '0xB viinikellari', '2022-11-25 20:05:21.600961+02')
, (53, true, 12, '0xC autotalli ', '2022-11-25 20:05:21.659+02')
, (54, true, 14, '0xE KHH ja KH', '2022-11-25 20:05:21.77076+02')
, (55, true, 63, '0x3F olohuone', '2022-11-25 20:05:22.165713+02')
, (56, true, 64, '0x40 ruokapöytä', '2022-11-25 20:05:22.228235+02')
, (57, true, 65, '0x41 keittiö', '2022-11-25 20:05:22.277202+02')
, (58, true, 21, '0x15 eteinen', '2022-11-25 20:05:22.806794+02')
, (59, true, 41, '0x29 MH1', '2022-11-25 20:05:24.861676+02')
, (60, true, 42, '0x2A parvi', '2022-11-25 20:05:24.91413+02')
, (61, true, 43, '0x2B MH2', '2022-11-25 20:05:24.978211+02')

, (350, false, 91, '0x5B tekninen tila', '2022-11-25 22:07:06.370971+02')
, (351, false, 13, '0xD etupihan valot', '2022-11-25 22:21:52.017244+02')
, (352, false, 14, '0xE KHH ja KH', '2022-11-25 22:21:53.093651+02')
, (353, false, 63, '0x3F olohuone', '2022-11-25 22:30:23.191829+02')
, (354, false, 62, '0x3E aula', '2022-11-25 22:30:38.704402+02')
, (355, true, 63, '0x3F olohuone', '2022-11-25 22:43:19.221019+02')
, (356, true, 64, '0x40 ruokapöytä', '2022-11-25 22:43:20.286785+02')
, (357, true, 65, '0x41 keittiö', '2022-11-25 22:43:21.291029+02')
, (358, false, 12, '0xC autotalli ', '2022-11-25 22:48:52.67657+02')
, (359, false, 11, '0xB viinikellari', '2022-11-25 23:12:41.298537+02')
, (360, false, 91, '0x5B tekninen tila', '2022-11-25 23:12:41.452908+02')
, (361, false, 12, '0xC autotalli ', '2022-11-25 23:12:41.464225+02')
, (362, false, 13, '0xD etupihan valot', '2022-11-25 23:12:41.533159+02')
, (363, false, 14, '0xE KHH ja KH', '2022-11-25 23:12:41.592385+02')
, (364, false, 41, '0x29 MH1', '2022-11-25 23:12:41.717548+02')
, (365, false, 42, '0x2A parvi', '2022-11-25 23:12:41.766813+02')
, (366, false, 43, '0x2B MH2', '2022-11-25 23:12:41.817682+02')

, (376, false, 91, '0x5B tekninen tila', '2022-11-25 23:14:37.238353+02')
, (377, false, 11, '0xB viinikellari', '2022-11-25 23:14:37.406018+02')
, (378, false, 12, '0xC autotalli ', '2022-11-25 23:14:37.488624+02')
, (379, false, 13, '0xD etupihan valot', '2022-11-25 23:14:37.528407+02')
, (380, false, 14, '0xE KHH ja KH', '2022-11-25 23:14:37.67618+02')
, (381, false, 41, '0x29 MH1', '2022-11-25 23:14:37.793035+02')
, (382, false, 42, '0x2A parvi', '2022-11-25 23:14:37.903583+02')
, (383, false, 43, '0x2B MH2', '2022-11-25 23:14:38.014394+02')

, (394, false, 91, '0x5B tekninen tila', '2022-11-25 23:27:32.656946+02')
, (395, false, 41, '0x29 MH1', '2022-11-25 23:27:32.76919+02')
, (396, false, 42, '0x2A parvi', '2022-11-25 23:27:32.831537+02')
, (397, false, 43, '0x2B MH2', '2022-11-25 23:27:32.894664+02')
  
-- a couple rows past the 25th
, (423, true, 91, '0x5B tekninen tila', '2022-11-26 00:40:54.704444+02')
, (424, true, 62, '0x3E aula', '2022-11-26 00:41:00.79628+02')
, (425, false, 42, '0x2A parvi', '2022-11-26 00:43:11.52526+02')
, (426, false, 43, '0x2B MH2', '2022-11-26 00:43:11.565265+02')
, (427, false, 91, '0x5B tekninen tila', '2022-11-26 00:43:11.6017+02')
, (428, false, 44, '0x2C MH3', '2022-11-26 00:43:11.611368+02')
, (429, false, 11, '0xB viinikellari', '2022-11-26 00:43:12.104256+02')
, (430, false, 12, '0xC autotalli ', '2022-11-26 00:43:12.152317+02')
, (431, false, 13, '0xD etupihan valot', '2022-11-26 00:43:12.216297+02')
, (432, false, 14, '0xE KHH ja KH', '2022-11-26 00:43:12.280313+02')
, (433, false, 21, '0x15 eteinen', '2022-11-26 00:43:12.344206+02')
, (434, false, 41, '0x29 MH1', '2022-11-26 00:43:12.392086+02')
, (435, false, 62, '0x3E aula', '2022-11-26 00:43:12.663992+02')
, (436, false, 63, '0x3F olohuone', '2022-11-26 00:43:12.727941+02')
, (437, false, 64, '0x40 ruokapöytä', '2022-11-26 00:43:12.791944+02')
, (438, false, 65, '0x41 keittiö', '2022-11-26 00:43:12.855841+02')
, (439, true, 62, '0x3E aula', '2022-11-26 08:12:13.111152+02')
, (440, true, 64, '0x40 ruokapöytä', '2022-11-26 08:44:26.187976+02')
, (441, true, 14, '0xE KHH ja KH', '2022-11-26 08:44:28.686321+02')

INSERT 0 72


-- If switch_times is big, these INDEXES make everything **fast**:
CREATE INDEX ON switch_times (id_dec, ts DESC);
CREATE INDEX ON switch_times (ts);

CREATE INDEX



CREATE INDEX


-- If you don't have a table "labels", create it
CREATE TABLE labels AS
WITH RECURSIVE cte AS (
   (
   SELECT id_dec, label
   FROM   switch_times
   ORDER  BY 1
   LIMIT  1
   )

   UNION ALL
   (
   SELECT s.id_dec, s.label
   FROM   cte c
   JOIN   switch_times s ON s.id_dec > c.id_dec
   ORDER  BY 1
   LIMIT  1
   )
   )
TABLE cte;

ALTER TABLE labels
  ADD PRIMARY KEY (id_dec)
, ALTER COLUMN label SET NOT NULL
, ADD CONSTRAINT label_uni UNIQUE (label)  
;

TABLE labels;

SELECT 14



ALTER TABLE




id_dec
label


11
0xB viinikellari

12
0xC autotalli

13
0xD etupihan valot

14
0xE KHH ja KH

21
0x15 eteinen

41
0x29 MH1

42
0x2A parvi

43
0x2B MH2

44
0x2C MH3

62
0x3E aula

63
0x3F olohuone

64
0x40 ruokapöytä

65
0x41 keittiö

91
0x5B tekninen tila



SELECT 14


-- main query (only labels with "on" time)
WITH bounds(lo, hi) AS (
   SELECT timestamptz '2022-11-25 0:0+02'  -- enter time interval here *once*
        , timestamptz '2022-11-26 0:0+02'
   )
, snapshot AS (
   SELECT id_dec, label, is1, ts
   FROM   switch_times s, bounds b
   WHERE  s.ts >= b.lo
   AND    s.ts <  b.hi

   UNION ALL
   SELECT s.*  -- must be separate   
   FROM   labels l
   JOIN   LATERAL ( -- latest earlier entry 
      SELECT s.id_dec, s.label, s.is1, b.lo AS ts  -- cut off at lower bound
      FROM   switch_times s, bounds b
      WHERE  s.id_dec = l.id_dec
      AND    s.ts < b.lo
      ORDER  BY s.ts DESC
      LIMIT  1
      ) s ON s.is1  -- ... if it's "on" (none in your org sample was, but I added some)
   )
SELECT label, sum(z - a) AS duration
FROM  (
   SELECT label
        , lag(is1, 1, false) OVER w AS last_is1
        , lag(ts) OVER w AS a
        , ts AS z
   FROM   snapshot
   WINDOW w AS (PARTITION BY label ORDER BY ts ROWS UNBOUNDED PRECEDING)
   ) sub
WHERE  last_is1
GROUP  BY 1;


label
duration


0x29 MH1
03:07:16.855872

0x2A parvi
03:07:16.852683

0x2B MH2
23:04:00.050178

0x3F olohuone
02:25:01.026116

0x40 ruokapöytä
02:37:58.05855

0x41 keittiö
02:37:59.013827

0x5B tekninen tila
21:58:28.560687

0xB viinikellari
03:07:19.697576

0xC autotalli
02:43:31.01757

0xE KHH ja KH
02:16:31.322891



SELECT 10
id_dec	label
11	0xB viinikellari
12	0xC autotalli
13	0xD etupihan valot
14	0xE KHH ja KH
21	0x15 eteinen
41	0x29 MH1
42	0x2A parvi
43	0x2B MH2
44	0x2C MH3
62	0x3E aula
63	0x3F olohuone
64	0x40 ruokapöytä
65	0x41 keittiö
91	0x5B tekninen tila
label	duration
0x29 MH1	03:07:16.855872
0x2A parvi	03:07:16.852683
0x2B MH2	23:04:00.050178
0x3F olohuone	02:25:01.026116
0x40 ruokapöytä	02:37:58.05855
0x41 keittiö	02:37:59.013827
0x5B tekninen tila	21:58:28.560687
0xB viinikellari	03:07:19.697576
0xC autotalli	02:43:31.01757
0xE KHH ja KH	02:16:31.322891