MYSQL Date diff between multiply dates in the same rows

I have table with changelog, every row give me specify inormation about actions.

Example:

• Ticket closed on 2022-06-26 14:12:07.000
• Ticket reopened on 2022-06-28 08:35:59.000
• Ticket closed on 2022-06-28 12:55:11.000
• Ticket reopened on 2022-06-30 17:04:34.000

In other table I have information about creation and closed date for tickets and for now I just make a datediff between those 2 dates. But it is not transparent for my stakeholder.

I need to count exactly number of days when ticket was opened and when it was closed from changelog.

Data from changelog

I found similiar problems like this one MySQL Date difference between two rows but here is max and min but I need to count every change

Here is I think all data is needed to do this Table

As your sample changelog data starts with the status change from Open to Closed, I have assumed that the initial Open datetime needs to come from the ticket itself.

In your question you stated:

I need to count exactly number of days when ticket was opened and when it was closed from changelog.

but the number of days between 2022-06-20 13:16:53 and 2022-06-20 13:27:12 is obviously 0, so in these examples I have done all calculations in seconds.

If you are only doing this calculation for Closed tickets (so there's a closed status for every open/reopened status) then you can use a simple GROUP BY:

SELECT id,
    SUM(
        CASE
            WHEN toString = 'closed' THEN TO_SECONDS(created)
            WHEN toString IN ('open', 'reopened') THEN - TO_SECONDS(created)
        END
    ) AS duration_sec
FROM (
    SELECT created, toString, id FROM changelog WHERE field = 'status'
    UNION ALL
    SELECT created, 'Open', id FROM tickets
) t
GROUP BY id;

Another approach is to use a correlated subquery to get the closed time, and if the ticket is not closed use current datetime to calculate how long it has been open for:

SELECT id, SUM(TIMESTAMPDIFF(SECOND, opened, IFNULL(closed, NOW()))) AS duration_sec
FROM (
    SELECT id, created AS opened, (SELECT created FROM changelog WHERE id = t.id AND field = 'status' AND toString = 'closed' AND created > t.created ORDER BY created ASC LIMIT 1) AS closed
    FROM tickets t
    UNION ALL
    SELECT id, created AS opened, (SELECT created FROM changelog WHERE id = cl.id AND field = 'status' AND toString = 'closed' AND created > cl.created ORDER BY created ASC LIMIT 1) AS closed
    FROM changelog cl
    WHERE cl.field = 'status' AND toString IN ('reopened')
) t
GROUP BY id;

Another approach, using the LEAD() window function to get the value of created from the next row, and Common Table Expressions (CTEs) to split up the query:

WITH ticket_status_changes AS (
    SELECT created, toString, id FROM changelog WHERE field = 'status'
    UNION ALL
    SELECT created, 'open', id FROM tickets
),
ticket_open_close AS (
    SELECT
        id, toString, created AS opened,
        IF(
            toString IN ('open', 'reopened'),
            LEAD(created) OVER (PARTITION BY id ORDER BY created ASC),
            NULL
        ) AS closed
    FROM ticket_status_changes
)
SELECT id, SUM(TIMESTAMPDIFF(SECOND, opened, IFNULL(closed, NOW()))) AS duration_sec
FROM ticket_open_close
WHERE toString IN ('open', 'reopened')
GROUP BY id;