Calculating processing time between two dates in mysql

Question

I work as jr system administrator in a small outsourcing company where we offer IT services to other companies. We use osTicket as a Tickets Management System but reports and exports in this are quite weird and not really clever. I manage to make KPIs for my IT team and once a month I need to export all tickets from osTicket web platform and organize them in an Excel worksheet where I add all needed infos such as the number of tickets opened per category, per operator, per company etc.

In order to automate the process, I used Metabase to read the osTicket DB, I just miss the last information to have it perfectly work as I would: the processing time intercurring between a ticket is opened and closed.

The "easy" calculation is quite simple:

ROUND(SUM(TIMESTAMPDIFF(SECOND, ost_ticket.created, ost_ticket.closed)/3600)) AS 'Processing Time'

The problem is that this simple operation calculates the total time in hours (with 2 decimals) between opened and closed: as you can imagine, to correctly analyze SLAs the query should take into account that we don't work 24/7 but only weekdays from 09:00 am to 1:00 pm and from 2:00 pm to 6:00 pm.

I tried to read tons of forums, even asking to ChatGPT but it's only a month that I started writing queries and this just isn't up to me. Could you help me to better understand the logic I should use to achieve the result?

I tried the following:

TIMESTAMPDIFF(
     MINUTE
    ,CASE WHEN DAYOFWEEK(ost_ticket.created) = 6
          THEN DATE_ADD(DATE(ost_ticket.created), INTERVAL 18 HOUR) -- Friday
          WHEN DAYOFWEEK(ost_ticket.created) = 7
          THEN DATE_ADD(DATE(ost_ticket.created), INTERVAL 10 HOUR) -- Saturday
          ELSE ost_ticket.created
     END
    ,CASE WHEN DAYOFWEEK(ost_ticket.closed) = 6
          THEN DATE_ADD(DATE(ost_ticket.closed), INTERVAL 10 HOUR) -- Saturday
          WHEN DAYOFWEEK(ost_ticket.closed) = 7
          THEN DATE_ADD(DATE(ost_ticket.closed), INTERVAL 18 HOUR) -- Sunday
          ELSE ost_ticket.closed
     END
) / 60 AS 'Tempo di evasione'

as suggested by ChatGPT but it's too complicated and the result obtained in minutes seems incorrect.

[Tips for asking a good Structured Query Language (SQL) question](//meta.stackoverflow.com/questions/271055), #5 and #3. — Akina, Aug 08 '23 at 13:57
Your question lacks detail. Do you need to account for public holidays? Can tickets be created outside of business hours? Can tickets be closed outside of business hours? Is it possible for a ticket to be created and closed before start of business hours (created 08:15 and closed 08:45)? Would this need to be included in your report, even though 0 business time worked? Are the majority of tickets created and closed on the same business day? The best approach is heavily dependent on answers to the above. — user1191247, Aug 10 '23 at 12:15

score 0 · Answer 1 · answered Aug 08 '23 at 18:34

I like to tackle these problems in stages. The solution may not be the most elegant or efficient, but it works.

First, let's get some data we can work with:

DROP TEMPORARY TABLE IF EXISTS OST_TICKET;
CREATE TEMPORARY TABLE OST_TICKET (
    TKT_ID   INT AUTO_INCREMENT PRIMARY KEY,
    CREATED  DATETIME,
    CLOSED   DATETIME
);

INSERT INTO OST_TICKET (CREATED, CLOSED)
VALUES
    ('2023-07-31 08:15:00', '2023-08-04 14:33:48'),
    ('2023-07-30 07:15:00', '2023-08-03 19:33:12'),
    ('2023-08-01 09:45:00', '2023-08-01 12:33:27'),
    ('2023-08-03 12:45:00', '2023-08-03 17:21:18'),
    ('2023-08-03 14:13:00', '2023-08-03 16:11:33');

Now, what we need to know is where the timespans overlap the shifts. I took the approach of finding all the shifts that overlap the timespan, adjusting the first and last shift and then adding them together. The first thing to do is find all the days in the time span. We can do that with the following recursive CTE:

Code Segment 1

WITH RECURSIVE ALL_DAYS AS (
    SELECT
            TKT_ID,
            CREATED,
            CLOSED,
            DATE(CREATED) AS WORK_DATE
      FROM
            OST_TICKET
    UNION ALL
    SELECT
            TKT_ID,
            CREATED,
            CLOSED,
            DATE_ADD(WORK_DATE, INTERVAL 1 DAY)
      FROM
            ALL_DAYS
     WHERE
            WORK_DATE < DATE(CLOSED)
),

For the sample data set, this produces:

+--------+---------------------+---------------------+------------+
| TKT_ID |       CREATED       |       CLOSED        | WORK_DATE  |
+--------+---------------------+---------------------+------------+
|      1 | 2023-07-31 08:15:00 | 2023-08-04 14:33:48 | 2023-07-31 |
|      1 | 2023-07-31 08:15:00 | 2023-08-04 14:33:48 | 2023-08-01 |
|      1 | 2023-07-31 08:15:00 | 2023-08-04 14:33:48 | 2023-08-02 |
|      1 | 2023-07-31 08:15:00 | 2023-08-04 14:33:48 | 2023-08-03 |
|      1 | 2023-07-31 08:15:00 | 2023-08-04 14:33:48 | 2023-08-04 |
|      2 | 2023-07-30 07:15:00 | 2023-08-03 19:33:12 | 2023-07-30 |
|      2 | 2023-07-30 07:15:00 | 2023-08-03 19:33:12 | 2023-07-31 |
|      2 | 2023-07-30 07:15:00 | 2023-08-03 19:33:12 | 2023-08-01 |
|      2 | 2023-07-30 07:15:00 | 2023-08-03 19:33:12 | 2023-08-02 |
|      2 | 2023-07-30 07:15:00 | 2023-08-03 19:33:12 | 2023-08-03 |
|      3 | 2023-08-01 09:45:00 | 2023-08-01 12:33:27 | 2023-08-01 |
|      4 | 2023-08-03 12:45:00 | 2023-08-03 17:21:18 | 2023-08-03 |
|      5 | 2023-08-03 14:13:00 | 2023-08-03 16:11:33 | 2023-08-03 |
+--------+---------------------+---------------------+------------+

From there, we can create rows for each shift by cross joining ALL_DAYS with a time table. This stage can also eliminate those weekends. So the second CTE is:

Code Segment 2

ALLSHIFTS AS (
    SELECT
            TKT_ID,
            CREATED,
            CLOSED,
            TIMESTAMPADD(HOUR, SHIFTS.S_START, WORK_DATE) AS SHIFT_START,
            TIMESTAMPADD(HOUR, SHIFTS.S_END, WORK_DATE)   AS SHIFT_END
      FROM
            ALL_DAYS
            CROSS JOIN ( SELECT 9 AS S_START
                               ,13 AS S_END
                         UNION ALL
                         SELECT 14
                               ,18) AS SHIFTS
     WHERE
            DAYOFWEEK(WORK_DATE) NOT IN (1, 7)
),

Which produces:

+--------+---------------------+---------------------+---------------------+---------------------+
| TKT_ID |       CREATED       |       CLOSED        |     SHIFT_START     |      SHIFT_END      |
+--------+---------------------+---------------------+---------------------+---------------------+
|      1 | 2023-07-31 08:15:00 | 2023-08-04 14:33:48 | 2023-07-31 09:00:00 | 2023-07-31 13:00:00 |
|      1 | 2023-07-31 08:15:00 | 2023-08-04 14:33:48 | 2023-07-31 14:00:00 | 2023-07-31 18:00:00 |
|      1 | 2023-07-31 08:15:00 | 2023-08-04 14:33:48 | 2023-08-01 09:00:00 | 2023-08-01 13:00:00 |
|      1 | 2023-07-31 08:15:00 | 2023-08-04 14:33:48 | 2023-08-01 14:00:00 | 2023-08-01 18:00:00 |
|      1 | 2023-07-31 08:15:00 | 2023-08-04 14:33:48 | 2023-08-02 09:00:00 | 2023-08-02 13:00:00 |
|      1 | 2023-07-31 08:15:00 | 2023-08-04 14:33:48 | 2023-08-02 14:00:00 | 2023-08-02 18:00:00 |
|      1 | 2023-07-31 08:15:00 | 2023-08-04 14:33:48 | 2023-08-03 09:00:00 | 2023-08-03 13:00:00 |
|      1 | 2023-07-31 08:15:00 | 2023-08-04 14:33:48 | 2023-08-03 14:00:00 | 2023-08-03 18:00:00 |
|      1 | 2023-07-31 08:15:00 | 2023-08-04 14:33:48 | 2023-08-04 09:00:00 | 2023-08-04 13:00:00 |
|      1 | 2023-07-31 08:15:00 | 2023-08-04 14:33:48 | 2023-08-04 14:00:00 | 2023-08-04 18:00:00 |
|      2 | 2023-07-30 07:15:00 | 2023-08-03 19:33:12 | 2023-07-31 09:00:00 | 2023-07-31 13:00:00 |
|      2 | 2023-07-30 07:15:00 | 2023-08-03 19:33:12 | 2023-07-31 14:00:00 | 2023-07-31 18:00:00 |
|      2 | 2023-07-30 07:15:00 | 2023-08-03 19:33:12 | 2023-08-01 09:00:00 | 2023-08-01 13:00:00 |
|      2 | 2023-07-30 07:15:00 | 2023-08-03 19:33:12 | 2023-08-01 14:00:00 | 2023-08-01 18:00:00 |
|      2 | 2023-07-30 07:15:00 | 2023-08-03 19:33:12 | 2023-08-02 09:00:00 | 2023-08-02 13:00:00 |
|      2 | 2023-07-30 07:15:00 | 2023-08-03 19:33:12 | 2023-08-02 14:00:00 | 2023-08-02 18:00:00 |
|      2 | 2023-07-30 07:15:00 | 2023-08-03 19:33:12 | 2023-08-03 09:00:00 | 2023-08-03 13:00:00 |
|      2 | 2023-07-30 07:15:00 | 2023-08-03 19:33:12 | 2023-08-03 14:00:00 | 2023-08-03 18:00:00 |
|      3 | 2023-08-01 09:45:00 | 2023-08-01 12:33:27 | 2023-08-01 09:00:00 | 2023-08-01 13:00:00 |
|      3 | 2023-08-01 09:45:00 | 2023-08-01 12:33:27 | 2023-08-01 14:00:00 | 2023-08-01 18:00:00 |
|      4 | 2023-08-03 12:45:00 | 2023-08-03 17:21:18 | 2023-08-03 09:00:00 | 2023-08-03 13:00:00 |
|      4 | 2023-08-03 12:45:00 | 2023-08-03 17:21:18 | 2023-08-03 14:00:00 | 2023-08-03 18:00:00 |
|      5 | 2023-08-03 14:13:00 | 2023-08-03 16:11:33 | 2023-08-03 09:00:00 | 2023-08-03 13:00:00 |
|      5 | 2023-08-03 14:13:00 | 2023-08-03 16:11:33 | 2023-08-03 14:00:00 | 2023-08-03 18:00:00 |
+--------+---------------------+---------------------+---------------------+---------------------+

Now it's time to pull in the cases where start and end times are inside the shifts. Note that we also want to eliminate any situations that don't make sense. This CTE does both steps:

Code Segment 4

ACTUALS AS (
    SELECT
            TKT_ID,
            CREATED,
            SHIFT_START,
            GREATEST(CREATED, SHIFT_START) AS ACTUAL_START,
            CLOSED,
            SHIFT_END,
            LEAST(CLOSED, SHIFT_END)        AS ACTUAL_END
     FROM
            ALLSHIFTS
    WHERE
            GREATEST(CREATED, SHIFT_START) < LEAST(CLOSED, SHIFT_END)
    
)

Producing:


+--------+---------------------+---------------------+
| TKT_ID |    ACTUAL_START     |     ACTUAL_END      |
+--------+---------------------+---------------------+
|      1 | 2023-07-31 09:00:00 | 2023-07-31 13:00:00 |
|      1 | 2023-07-31 14:00:00 | 2023-07-31 18:00:00 |
|      1 | 2023-08-01 09:00:00 | 2023-08-01 13:00:00 |
|      1 | 2023-08-01 14:00:00 | 2023-08-01 18:00:00 |
|      1 | 2023-08-02 09:00:00 | 2023-08-02 13:00:00 |
|      1 | 2023-08-02 14:00:00 | 2023-08-02 18:00:00 |
|      1 | 2023-08-03 09:00:00 | 2023-08-03 13:00:00 |
|      1 | 2023-08-03 14:00:00 | 2023-08-03 18:00:00 |
|      1 | 2023-08-04 09:00:00 | 2023-08-04 13:00:00 |
|      1 | 2023-08-04 14:00:00 | 2023-08-04 14:33:48 |
|      2 | 2023-07-31 09:00:00 | 2023-07-31 13:00:00 |
|      2 | 2023-07-31 14:00:00 | 2023-07-31 18:00:00 |
|      2 | 2023-08-01 09:00:00 | 2023-08-01 13:00:00 |
|      2 | 2023-08-01 14:00:00 | 2023-08-01 18:00:00 |
|      2 | 2023-08-02 09:00:00 | 2023-08-02 13:00:00 |
|      2 | 2023-08-02 14:00:00 | 2023-08-02 18:00:00 |
|      2 | 2023-08-03 09:00:00 | 2023-08-03 13:00:00 |
|      2 | 2023-08-03 14:00:00 | 2023-08-03 18:00:00 |
|      3 | 2023-08-01 09:45:00 | 2023-08-01 12:33:27 |
|      4 | 2023-08-03 12:45:00 | 2023-08-03 13:00:00 |
|      4 | 2023-08-03 14:00:00 | 2023-08-03 17:21:18 |
|      5 | 2023-08-03 14:13:00 | 2023-08-03 16:11:33 |
+--------+---------------------+---------------------+

And with that, all that is left is to calculated the date differences and sum the hours.

Code Segment 4

  SELECT
         TKT_ID,
         ROUND(
                SUM(
                     TIMESTAMPDIFF( SECOND
                                   ,ACTUAL_START
                                   ,ACTUAL_END )
                   ) / 3600.0
               ,2
              ) AS HOURS_WORKED
    FROM
         ACTUALS
GROUP BY
         TKT_ID
ORDER BY
         TKT_ID
;

Which, for my sample set, produces these results:


+--------+--------------+
| TKT_ID | HOURS_WORKED |
+--------+--------------+
|      1 |        36.56 |
|      2 |        32.00 |
|      3 |         2.81 |
|      4 |         3.61 |
|      5 |         1.98 |
+--------+--------------+

To run the actual solution you have to combine all four code segments into one query. You will also need to adjust field and table names to suit your unique situation.

Calculating processing time between two dates in mysql

1 Answers1