How to create a MYSQL View of task time per day given start date, finish date and pauses in between

I'll give it a try and create VIEWs for each step, to make things more clear. In the end I will give an all-in-one query using CTEs, so that the real answer can be found down at step #5.

Preparation:

CREATE TABLE tasks (
  id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
  task_start datetime,
  task_end datetime
);

CREATE TABLE pauses (
  id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
  task_id bigint NOT NULL REFERENCES tasks(id),
  pause_start datetime,
  pause_end datetime
);

INSERT INTO tasks(task_start, task_end) VALUES
  ('2022-07-02 07:45:01', NULL),
  ('2022-07-02 13:32:17', '2022-07-10 14:23:28');

INSERT INTO pauses(task_id, pause_start, pause_end) VALUES
  (1, '2022-07-02 19:00:00', '2022-07-03 07:00:00'),
  (2, '2022-07-05 14:00:00', '2022-07-05 14:05:00'),
  (2, '2022-07-08 19:00:00', '2022-07-09 07:00:00');

Step #1: Transform anything to a series of events. Some task or pauses may have started but not yet ended, so fake dates are used:

CREATE OR REPLACE VIEW task_events AS 
SELECT * FROM (  
    SELECT id AS task_id, 'task_start' AS event_type, task_start as event_time FROM tasks
    UNION ALL
    SELECT id, 'task_end', coalesce(task_end, date_add(task_start, INTERVAL 10 YEAR)) FROM tasks
    UNION ALL
    SELECT task_id, 'pause_start', pause_start FROM pauses
    UNION ALL
    SELECT task_id, 'pause_end', coalesce(pause_end, date_add(pause_start, INTERVAL 10 YEAR)) FROM pauses
) e
ORDER BY event_time;

Step #2: Transform the events back to periods:

CREATE OR REPLACE VIEW event_periods AS
SELECT e.*,
  lag(event_type) OVER (PARTITION BY task_id ORDER BY event_time DESC) AS next_event_type,
  lag(event_time) OVER (PARTITION BY task_id ORDER BY event_time DESC) AS next_event_time
FROM task_events e;

Step #3: Extract only those periods which started some work, that is whenever a task started or a pause ended.

CREATE OR REPLACE VIEW working_periods AS
SELECT * FROM event_periods WHERE event_type IN ('task_start', 'pause_end')
ORDER BY task_id, event_time;

Step #4: Use the working_periods for reporting. E. g. to get the working time between 2022-07-09 and 2022-07-11:

SELECT p.*, timediff(next_event_time, event_time) AS working_time 
  FROM (SELECT task_id,
               event_type, greatest(event_time, '2022-07-09') AS event_time,
               next_event_type, least(next_event_time, '2022-07-11') AS next_event_time
          FROM working_periods
         WHERE next_event_time >= '2022-07-09' and event_time <= '2022-07-11') p;

Step #5: rewrite using CTEs

WITH task_events AS (  
        SELECT id AS task_id, 'task_start' AS event_type, task_start as event_time FROM tasks
        UNION ALL
        SELECT id, 'task_end', coalesce(task_end, date_add(task_start, INTERVAL 10 YEAR)) FROM tasks
        UNION ALL
        SELECT task_id, 'pause_start', pause_start FROM pauses
        UNION ALL
        SELECT task_id, 'pause_end', coalesce(pause_end, date_add(pause_start, INTERVAL 10 YEAR)) FROM pauses
    ),
    event_periods AS (
        SELECT e.*,
          lag(event_type) OVER (PARTITION BY task_id ORDER BY event_time DESC) AS next_event_type,
          lag(event_time) OVER (PARTITION BY task_id ORDER BY event_time DESC) AS next_event_time
        FROM task_events e
    ),
    working_periods AS (
        SELECT * 
          FROM event_periods WHERE event_type IN ('task_start', 'pause_end')
         ORDER BY task_id, event_time
    ),
    report_periods AS (
        SELECT task_id,
               event_type, greatest(event_time, '2022-07-09') AS event_time,
               next_event_type, least(next_event_time, '2022-07-11') AS next_event_time
          FROM working_periods
         WHERE next_event_time >= '2022-07-09' and event_time <= '2022-07-11'
    )
SELECT p.*, timediff(next_event_time, event_time) AS working_time
  FROM report_periods p;

Result:

I'm pretty sure, it's far from being perfect, but perhaps it can serve as a starting point.