MySQL Function to calculate working hours between 2 dates

1. Introduction

This is a MySQL function to calculate working hours between 2 dates. I made this function when I was customizing a HelpDesk Tool to implement SLA's. The elapsed hours must be calculated in working days, ie I should desconsider holidays, weekends and the working time frame.

2. MySQL Elapsed Working Hours Functions

2.1. Pre-requisites

CREATE TABLE service_catalog_holidays (

  id INTEGER UNSIGNED  NOT NULL   AUTO_INCREMENT,

  date DATE  NOT NULL  ,

  name VARCHAR(255)  NULL    ,

PRIMARY KEY(id)  ,

UNIQUE INDEX ak_servicecatalogholidays(date))

TYPE=InnoDB;

2.2. Source Code

/*
 * ****************************************************************************
 * Function: elapsed_working_hour
 * Author..: Josemar Furegatti de Abreu Silva
 * E-mail..: josemarsilva@yahoo.com.br
 * TechBlog: http://josemarfuregattideabreusilva.blogspot.com.br/
 * Date....: 29/12/2012
 * ****************************************************************************
 */

delimiter ;
drop function elapsed_working_hours;
delimiter |
CREATE
  FUNCTION elapsed_working_hours
  ( 
    start_datetime           DATETIME,
    finish_datetime          DATETIME,
start_working_hour_time  TIME,
finish_working_hour_time TIME
  ) RETURNS FLOAT
BEGIN
  /*
   * Variables ...
   */
  DECLARE working_hour_per_day              FLOAT;
  DECLARE fulldays_between_start_finish     INTEGER;
  DECLARE holiday_days_between              INTEGER;
  DECLARE weekend_days_between_start_finish INTEGER;
  DECLARE is_start_datetime_a_holiday       TINYINT;
  DECLARE is_finish_datetime_a_holiday      TINYINT;
  DECLARE working_time_start_date           TIME;
  DECLARE working_time_finish_date          TIME;
  /*
   * woring_hour_per_day
   */
  IF start_working_hour_time IS NULL OR finish_working_hour_time IS NULL THEN
    SET working_hour_per_day = 24;
  ELSE
    SET working_hour_per_day = HOUR(  TIMEDIFF(start_working_hour_time, finish_working_hour_time)) 
                             + MINUTE(TIMEDIFF(start_working_hour_time, finish_working_hour_time)) /60 ;
  END IF;
  /*
   * fulldays_between_start_finish and weekend_days_between_start_finish
   */
  SET fulldays_between_start_finish = ABS(DATEDIFF( DATE(finish_datetime), DATE(start_datetime)));
  SET weekend_days_between_start_finish = TRUNCATE( fulldays_between_start_finish/7, 0) * 2;
  IF fulldays_between_start_finish > 1 /* only full days */ THEN 
    SET fulldays_between_start_finish = fulldays_between_start_finish - 1;
  ELSE
    SET fulldays_between_start_finish = 0;
  END IF;
  IF DATE_FORMAT(start_datetime, '%w') = 0 AND DATE_FORMAT(finish_datetime, '%w') IN ( 0 ) THEN
    SET weekend_days_between_start_finish = weekend_days_between_start_finish + 0;
  END IF;
  IF DATE_FORMAT(start_datetime, '%w') = 1  /* Mon */ AND DATE_FORMAT(finish_datetime, '%w') IN ( 0 /* Sun */ ) THEN
    SET weekend_days_between_start_finish = weekend_days_between_start_finish + 1;
  ELSEIF DATE_FORMAT(start_datetime, '%w') = 2  /* Tue */ AND DATE_FORMAT(finish_datetime, '%w') IN ( 0 /* Sun */ ) THEN
    SET weekend_days_between_start_finish = weekend_days_between_start_finish + 1;
  ELSEIF DATE_FORMAT(start_datetime, '%w') = 2  /* Tue */ AND DATE_FORMAT(finish_datetime, '%w') IN ( 1 /* Mon */ ) THEN
    SET weekend_days_between_start_finish = weekend_days_between_start_finish + 2;
  ELSEIF DATE_FORMAT(start_datetime, '%w') = 3  /* Wed */ AND DATE_FORMAT(finish_datetime, '%w') IN ( 0 /* Sun */ ) THEN
    SET weekend_days_between_start_finish = weekend_days_between_start_finish + 1;
  ELSEIF DATE_FORMAT(start_datetime, '%w') = 3  /* Wed */ AND DATE_FORMAT(finish_datetime, '%w') IN ( 1, 2 /* Mon, Tue */ ) THEN
    SET weekend_days_between_start_finish = weekend_days_between_start_finish + 2;
  ELSEIF DATE_FORMAT(start_datetime, '%w') = 4  /* Thu */ AND DATE_FORMAT(finish_datetime, '%w') IN ( 0 /* Sun */ ) THEN
    SET weekend_days_between_start_finish = weekend_days_between_start_finish + 1;
  ELSEIF DATE_FORMAT(start_datetime, '%w') = 4  /* Thu */ AND DATE_FORMAT(finish_datetime, '%w') IN ( 1, 2, 3 /* Mon, Tue, Wed */ ) THEN
    SET weekend_days_between_start_finish = weekend_days_between_start_finish + 2;
  ELSEIF DATE_FORMAT(start_datetime, '%w') = 5  /* Fri */ AND DATE_FORMAT(finish_datetime, '%w') IN ( 0 /* Sun */ ) THEN
    SET weekend_days_between_start_finish = weekend_days_between_start_finish + 1;
  ELSEIF DATE_FORMAT(start_datetime, '%w') = 5  /* Fri */ AND DATE_FORMAT(finish_datetime, '%w') IN ( 1, 2, 3, 4 /* Mon, Tue, Wed, Thu */ ) THEN
    SET weekend_days_between_start_finish = weekend_days_between_start_finish + 2;
  END IF;
  /*
   * holidays between start and finish dates
   */
  SET holiday_days_between = (
                               SELECT COUNT(*)
                               FROM   service_catalog_holidays
                               WHERE  date > date(start_datetime)
                               AND    date < date(finish_datetime)
                             );
  /*
   * is_start_datetime_a_holiday and is_finish_datetime_a_holiday
   */
  SET is_start_datetime_a_holiday  = (
                                      SELECT IF( count(*) > 0, 1, 0)
                                      FROM   service_catalog_holidays
                                      WHERE  date = date(start_datetime)
                                     );
  SET is_finish_datetime_a_holiday = (
                                      SELECT IF( count(*) > 0, 1, 0)
                                      FROM   service_catalog_holidays
                                      WHERE  date = date(finish_datetime)
                                     );
  /*
   * working_time_start_date
   */
  SET working_time_start_date = '00:00:00';
  IF is_start_datetime_a_holiday = 0  AND DATE_FORMAT(start_datetime, '%w') <> 6 /* Sat */  AND DATE_FORMAT(start_datetime, '%w') <> 0 /* Sun */ THEN
    IF finish_working_hour_time IS NULL THEN
      SET working_time_start_date = TIMEDIFF( TIME('24:00:00'), TIME(start_datetime) );
ELSEIF TIME(start_datetime) > finish_working_hour_time  THEN
      SET working_time_start_date = '00:00:00';
    ELSE
      SET working_time_start_date = TIMEDIFF( finish_working_hour_time, TIME(start_datetime) );
    END IF;
  END IF;
  /*
   * working_time_finish_date
   */
  SET working_time_finish_date = '00:00:00';
  IF is_finish_datetime_a_holiday = 0  AND DATE_FORMAT(finish_datetime, '%w') <> 6 /* Sat */  AND DATE_FORMAT(finish_datetime, '%w') <> 0 /* Sun */ THEN
    IF start_working_hour_time IS NULL THEN
      SET working_time_finish_date = TIMEDIFF( TIME(finish_datetime), '00:00:00' );
ELSEIF TIME(finish_datetime) < start_working_hour_time  THEN
      SET working_time_finish_date = '00:00:00';
    ELSE
      SET working_time_finish_date = TIMEDIFF( TIME(finish_datetime), start_working_hour_time );
    END IF;
  END IF;
  /*
   * return elapsed working hours between 2 dates, considering a specific working time-frame
   */
  RETURN (
           fulldays_between_start_finish
           - weekend_days_between_start_finish
           - holiday_days_between
         ) * working_hour_per_day
         + HOUR( working_time_start_date )  + MINUTE( working_time_start_date )/60
         + HOUR( working_time_finish_date ) + MINUTE( working_time_finish_date )/60 ;
END; /* CREATE FUNCTION */
|
delimiter ;

2.3. Examples

mysql> insert into service_catalog_holidays values ( null, '2012-12-25', 'Christmans Holiday');
mysql> 
mysql> select elapsed_working_hours('2012-12-21 09:49:06', '2012-12-26 14:04:37', '09:00:00', '18:00:00' ) elapsed_working_hours from dual;

+-----------------------+

| elapsed_working_hours |

+-----------------------+

|       22.233333587646 |

+-----------------------+

1 row in set (0.01 sec)

3. References

• n/a