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

#	Created	field	fromString	toString	id
1	2022-06-20 13:08:26.000	status	Open	Closed	50850
2	2022-06-20 13:16:53.000	status	Closed	Reopened	50850
3	2022-06-20 13:27:12.000	status	Open	Closed	50850
4	2022-06-20 13:27:18.000	status	Closed	Reopened	50850
5	2022-06-20 13:37:44.000	status	Open	Closed	50850
6	2022-06-20 13:51:15.000	status	Closed	Reopened	50850
7	2022-06-21 15:40:44.000	status	Open	Closed	50850
8	2022-06-22 07:59:29.000	status	Closed	Reopened	50850
9	2022-06-22 08:04:59.000	status	Open	Closed	50850
10	2022-06-22 10:58:12.000	status	Closed	Reopened	50850
11	2022-06-22 19:27:42.000	status	Open	Closed	50850
12	2022-06-22 19:28:33.000	status	Closed	Reopened	50850
13	2022-06-22 19:29:13.000	status	Open	Closed	50850
14	2022-06-22 19:29:27.000	status	Closed	Reopened	50850
15	2022-06-23 16:24:36.000	status	Open	Closed	50850
16	2022-06-23 16:29:09.000	status	Closed	Reopened	50850
17	2022-06-23 16:30:37.000	status	Open	Closed	50850
18	2022-06-23 16:33:18.000	status	Closed	Reopened	50850
19	2022-06-23 16:33:48.000	status	Open	Closed	50850
20	2022-06-23 16:34:27.000	status	Closed	Reopened	50850
21	2022-06-23 16:34:46.000	status	Open	Closed	50850

Solution

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;