MYSQL Date diff between multiply dates in the same rows

Question

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

Answer 1

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;

Answer 2

You have to somehow get (log) rows containing two timestamps to do some diff the way your are doing it currently based upon that "other table".

So the base table for the query is your log table, delivering the timestamp of e.g. "ticket open" actions. Now join the same table a second time for getting the timestamp of "ticket close" actions. Join criteria is the ticket id and the closing timestamp to be the minimal but later then the opening one as well as the correct source ("open") and target stati ("close").

... resulting in some query like this (untested and column names changed for better understanding, just get the idea ):

SELECT ticket_id,timediff(l2_min_time,l1_time)
FROM ( SELECT l1.ticket_id,l1.time l1_time,min(l2.time) l2_min_time
    FROM log_table l1
    JOIN log_table l2
        ON  l1.ticket_id=l2.ticket_id
        AND l1.time<=l2.time
        AND l1.targetStatus="open"
        AND l2.targetStatus="closed"
    GROUP BY l1.ticket_id,l1.time
    ) t1

Please be aware, that you are working on a cross product of "two" potencially large tables, that probably are not optimized for such joins. This query costs a good deal of CPU and/or RAM and should be a base for well set indices before letting loose on production.