By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t (tag varchar(12), value int, timestamp datetime(4));
INSERT INTO t (tag, value, timestamp) VALUES
('sts_downtime', 0, '2019-01-03 09:31:40.8240'),
('sts_downtime', 1, '2019-01-03 09:50:23.0310'),
('sts_downtime', 3, '2019-01-03 09:53:07.7750'),
('sts_downtime', 4, '2019-01-03 09:53:40.6060'),
('sts_downtime', 0, '2019-01-04 08:48:27.1020'),
('sts_downtime', 0, '2019-01-04 13:30:26.5180'),
('sts_downtime', 10, '2019-01-04 14:19:56.3740'),
('sts_downtime', 10, '2019-01-07 08:49:03.8480'),
('sts_downtime', 10, '2019-01-07 09:34:25.0850'),
('sts_downtime', 0, '2019-01-07 09:34:53.9940'),
('sts_downtime', 0, '2019-01-07 12:59:21.3210');
WITH cte1 AS (
SELECT *, CASE
WHEN LAG(value, 1, 0) OVER (ORDER BY timestamp) = 0 AND value > 0 THEN 1
WHEN LAG(value, 1, 0) OVER (ORDER BY timestamp) > 0 AND value = 0 THEN 1 END AS chg
FROM t
), cte2 AS (
SELECT *, SUM(chg) OVER (ORDER BY timestamp) AS grp
FROM cte1
)
SELECT SUM(value) AS `Sum of value`, MIN(timestamp) AS `Start`, MAX(timestamp) AS `End`
FROM cte2
GROUP BY grp
HAVING SUM(value) > 0
| Sum of value | Start | End |
|---|---|---|
| 8 | 2019-01-03 09:50:23.0310 | 2019-01-03 09:53:40.6060 |
| 30 | 2019-01-04 14:19:56.3740 | 2019-01-07 09:34:25.0850 |