SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE periods (
    "id" INTEGER PRIMARY KEY,
    "begin" DATETIME,
    "end" DATETIME);
INSERT INTO periods VALUES 
(1, '2022-01-01 12:00:00', '2022-01-01 13:30:00'),
(2, '2022-01-01 22:00:00', '2022-01-02 01:00:00'),
(3, '2022-01-02 17:00:00', '2022-01-04 05:00:00'),
(4, '2022-01-04 07:00:00', '2022-01-04 13:00:00'),
(5, '2022-01-04 19:00:00', '2022-01-04 22:00:00');
SELECT * FROM periods;


id
begin
end


1
2022-01-01 12:00:00
2022-01-01 13:30:00

2
2022-01-01 22:00:00
2022-01-02 01:00:00

3
2022-01-02 17:00:00
2022-01-04 05:00:00

4
2022-01-04 07:00:00
2022-01-04 13:00:00

5
2022-01-04 19:00:00
2022-01-04 22:00:00


WITH RECURSIVE
dates AS (
    SELECT DATE(MIN("begin")) daydate, 
           DATE(MIN("begin"), '+1 DAY') nextdate,
           DATE(MAX("end")) enddate
    FROM periods
  UNION ALL
    SELECT nextdate,
           DATE(nextdate, '+1 DAY'),
           enddate
    FROM dates
    WHERE daydate < enddate
)
SELECT dates.daydate, 
       SUM(  strftime('%s',MIN(dates.nextdate, periods."end")) 
           - strftime('%s', MAX(dates.daydate, periods."begin"))) total_duration
FROM dates
JOIN periods ON dates.daydate < periods."end"
            AND periods."begin" < dates.nextdate
GROUP BY dates.daydate


daydate
total_duration


2022-01-01
12600

2022-01-02
28800

2022-01-03
86400

2022-01-04
50400

id	begin	end
1	2022-01-01 12:00:00	2022-01-01 13:30:00
2	2022-01-01 22:00:00	2022-01-02 01:00:00
3	2022-01-02 17:00:00	2022-01-04 05:00:00
4	2022-01-04 07:00:00	2022-01-04 13:00:00
5	2022-01-04 19:00:00	2022-01-04 22:00:00

daydate	total_duration
2022-01-01	12600
2022-01-02	28800
2022-01-03	86400
2022-01-04	50400