How to Calculate Process Turnaround Time Excluding Non-Working Hours and Holidays

Introduction

This article describes how to calculate the actual process turnaround time (TAT) in Joget DX by excluding non-working hours, weekends, and holidays.

The Process Data Collector plugin is commonly used to measure the time taken to complete an activity or process based on the created date. However, the calculated duration includes non-working days and hours, which may result in inaccurate SLA or performance reporting.

Joget DX currently does not provide a native option to exclude non-working time from the Process Data Collector calculation.
Nevertheless, this requirement can be achieved by implementing a custom MySQL function and displaying the calculated result through a Database SQL Query–based List.

With this approach, the reported working time reflects only the actual working duration, excluding weekends and configured holidays.

How Does It Work?

Create and Configure a MySQL Function

Create a MySQL function in the Joget database to calculate non-working time between two timestamps.

Before executing the SQL script, configure weekends and holidays according to your organization's calendar.

Configure Weekends

SET weekends = '1,7';

Configure Holidays

Add or remove holiday dates as required. The dates must follow the dd-mm-yyyy format.

SELECT '27-02-2025'
UNION ALL SELECT '03-03-2025'
UNION ALL SELECT '14-03-2025';

DROP FUNCTION IF EXISTS calculate_overlapped_time;

CREATE FUNCTION calculate_overlapped_time(starttime DATETIME, endtime DATETIME)
RETURNS INT
DETERMINISTIC
BEGIN
  DECLARE overlapped_seconds INT DEFAULT 0;
  DECLARE current_day DATE;
  DECLARE current_time_x DATETIME;
  DECLARE is_weekend BOOLEAN;
  DECLARE is_holiday BOOLEAN;
  DECLARE holiday_entry VARCHAR(10);
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE weekends VARCHAR(10);
  DECLARE holiday_cursor CURSOR FOR
    SELECT holiday_date FROM (
      SELECT '27-02-2025' AS holiday_date
      UNION ALL SELECT '03-03-2025'
      UNION ALL SELECT '14-03-2025'
    ) AS holiday_list;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  SET weekends = '1,7';
  SET current_time_x = starttime;

  WHILE current_time_x <= endtime DO
    SET current_day = DATE(current_time_x);
    SET is_weekend = FIND_IN_SET(DAYOFWEEK(current_day), weekends) > 0;
    SET is_holiday = FALSE;
    OPEN holiday_cursor;
    holiday_loop: LOOP
      FETCH holiday_cursor INTO holiday_entry;
      IF done THEN LEAVE holiday_loop; END IF;
      IF holiday_entry = DATE_FORMAT(current_day, '%d-%m-%Y') THEN
        SET is_holiday = TRUE;
        LEAVE holiday_loop;
      END IF;
    END LOOP;
    CLOSE holiday_cursor;

    IF is_weekend OR is_holiday THEN
      IF current_time_x = starttime THEN
        SET overlapped_seconds = overlapped_seconds + LEAST(TIMESTAMPDIFF(SECOND, current_time_x, endtime), 86400 - TIME_TO_SEC(TIME(current_time_x)));
      ELSEIF DATE(current_time_x) = DATE(endtime) THEN
        SET overlapped_seconds = overlapped_seconds + TIME_TO_SEC(TIME(endtime));
      ELSE
        SET overlapped_seconds = overlapped_seconds + 86400;
      END IF;
    END IF;
    SET current_time_x = DATE_ADD(current_time_x, INTERVAL 1 DAY);
  END WHILE;

  RETURN overlapped_seconds;
END;

Create a List Using Database SQL Query

1. Go to List Builder.
2. Set Data Store to Database SQL Query.
3. Use the following query:

SELECT *,
       calculate_overlapped_time(startedTime, finishTime) AS nonWorkingTime,
       SEC_TO_TIME(timeConsumingFromStartedTime) AS duration_time,
       (timeConsumingFromStartedTime -
        calculate_overlapped_time(startedTime, finishTime)) AS working_time
FROM app_report_process_instance;

Display Working Time

Use the working_time column in the list to display the actual working duration, excluding weekends and holidays.

Download Sample App