CREATE OR REPLACE FUNCTION DIFFHOURTIME_WITHOUTHOLIDAY_FUN (
STARTTIME VARCHAR(20),
ENDTIME VARCHAR(20) )
RETURNS DOUBLE
LANGUAGE SQL
NOT DETERMINISTIC
EXTERNAL ACTION
READS SQL DATA
INHERIT SPECIAL REGISTERS
------------------------------------------------------------------------
-- FUNCTION：DIFFHOURTIME_WITHOUTHOLIDAY_FUN
-- 说明：获取2个时间之前的有效工作时间，单位为小时，去除节假日，每天按8小时计算，
-- 传入的开始时间必须小于等于传入的结束时间
-- 表holidays_settings为节假日设置表，holiday为节假日日期，如：2015-01-01
------------------------------------------------------------------------
BEGIN ATOMIC
DECLARE start_holiday INTEGER;--开始时间是否为节假日，是则返回1，不是则返回0
DECLARE end_holiday INTEGER;--结束时间是否为节假日，是则返回1，不是则返回0
DECLARE holidays INTEGER;--开始时间与结束时间之间的节假日天数，包含开始、结束时间
DECLARE start_time timestamp;--开始时间转化为时间格式
DECLARE end_time timestamp;--结束时间转化为时间格式
DECLARE start_work_first timestamp;--开始时间当天的上班时间，目前为08:30
DECLARE start_noon_start timestamp;--当天的午休开始时间，目前为12:00
DECLARE start_noon_end timestamp;--当天的午休结束时间，目前为13:30
DECLARE start_work_end timestamp;--开始时间当天的下班时间，目前为18:00
DECLARE end_work_first timestamp;--结束时间当天的上班时间，目前为08:30
DECLARE end_noon_start timestamp;--当天的午休开始时间，目前为12:00
DECLARE end_noon_end timestamp;--当天的午休结束时间，目前为13:30
DECLARE end_work_end timestamp;--结束时间当天的下班时间，目前为18:00
DECLARE diff_date DOUBLE;--开始时间与结束时间的相差天数
DECLARE noon_difftime double;--当天的午休时间
SET start_holiday = (SELECT count (*) FROM holidays_settings WHERE holiday = date (STARTTIME));
SET end_holiday = (SELECT count (*) FROM holidays_settings WHERE holiday = date (ENDTIME));
SET holidays = (SELECT count (*) FROM holidays_settings WHERE holiday BETWEEN date (STARTTIME) AND date (ENDTIME));
SET start_time = timestamp (STARTTIME);
SET end_time = timestamp (ENDTIME);
SET start_work_first = timestamp (SUBSTR (char (STARTTIME), 1, 10) || ' 08:30:00');
SET start_work_end = timestamp (SUBSTR (char (STARTTIME), 1, 10) || ' 18:00:00');
SET start_noon_start = timestamp (SUBSTR (to_char(STARTTIME,'yyyy-mm-dd hh24:mi:ss'), 1, 10) || ' 12:00:00');
SET start_noon_end = timestamp (SUBSTR (to_char(STARTTIME,'yyyy-mm-dd hh24:mi:ss'), 1, 10) || ' 13:30:00');
SET end_work_first = timestamp (SUBSTR (char (ENDTIME), 1, 10) || ' 08:30:00');
SET end_work_end = timestamp (SUBSTR (char (ENDTIME), 1, 10) || ' 18:00:00');
SET end_noon_start = timestamp (SUBSTR (to_char(ENDTIME,'yyyy-mm-dd hh24:mi:ss'), 1, 10) || ' 12:00:00');
SET end_noon_end = timestamp (SUBSTR (to_char(ENDTIME,'yyyy-mm-dd hh24:mi:ss'), 1, 10) || ' 13:30:00');
SET diff_date = cast ( timestampdiff (16,char (timestamp (SUBSTR (char (ENDTIME), 1, 10)) - timestamp (SUBSTR (char (STARTTIME), 1, 10)))) AS DECIMAL (20, 2));
SET noon_difftime = cast (timestampdiff (2,char (end_noon_end - end_noon_start)) AS DECIMAL (20, 2)); IF date (STARTTIME) = date (ENDTIME)--如果开始时间=结束时间
THEN
IF start_holiday > 0--开始时间为节假日，返回0小时
THEN
RETURN 0;
ELSE--开始时间不是节假日
IF start_time - start_work_first < 0--开始时间在上班前
THEN
IF end_time - end_work_first < 0--开始时间在上班前,结束时间也在上班前返回0小时
THEN
RETURN 0;
ELSEIF end_time - end_noon_start < 0--开始时间在上班前，结束时间在上午上班时间内，返回时间为结束时间-上班时间
THEN
RETURN cast (
timestampdiff (
2,
char (end_time - end_work_first)) AS DECIMAL (20, 2))
/ 3600;
ELSEIF end_time - end_noon_end <0 --开始时间在上班前，结束时间午休内,返回时间为午休开始时间-上班时间
THEN
RETURN cast (
timestampdiff (
2,
char (end_noon_start - end_work_first)) AS DECIMAL (20, 2))
/ 3600;
ELSEIF end_time - end_work_end <0 --开始时间在上班前，结束时间在下午上班时间内,返回时间为结束时间-上班时间-午休时间
THEN
RETURN (cast (
timestampdiff (
2,
char (end_time - end_work_first)) AS DECIMAL (20, 2))-noon_difftime)
/ 3600;
ELSE--开始时间在上班前，结束时间在下班后，返回时间8小时
RETURN 8;
END IF;
ELSEIF start_time - start_noon_start < 0 --开始时间在上午上班时间内
THEN
IF end_time - end_noon_start < 0 --开始时间在上午上班时间内，结束时间在上午上班时间内，返回时间为结束时间-开始时间
THEN
RETURN cast (
timestampdiff (
2,
char (end_time - start_time)) AS DECIMAL (20, 2))
/ 3600;
ELSEIF end_time - end_noon_end < 0 --开始时间在上午上班时间内,结束时间在午休内，返回时间为午休开始时间-开始时间
THEN
RETURN cast (
timestampdiff (
2,
char (end_noon_start - start_time)) AS DECIMAL (20, 2))
/ 3600;
ELSEIF end_time - end_work_end < 0 --开始时间在上午上班时间内,结束时间下午上班时间内，返回时间为结束时间-开始时间-午休时间
THEN
RETURN (cast (
timestampdiff (
2,
char (end_time - start_time)) AS DECIMAL (20, 2))-noon_difftime)
/ 3600;
ELSE --开始时间在上午上班时间内,结束时间在下班时间，返回时间为下午下班时间-开始时间-午休时间
RETURN (cast (
timestampdiff (
2,
char (end_work_end - start_time)) AS DECIMAL (20, 2))-noon_difftime)
/ 3600;
END IF;
ELSEIF start_time - start_noon_end <0 --开始时间在午休时间内
THEN
IF end_time - end_noon_end<0 --结束时间也在午休时间内
THEN
RETURN 0;
ELSEIF end_time - end_work_end<0 --结束时间在下午上班时间内,返回时间为结束时间-午休结束时间
THEN
RETURN cast (
timestampdiff (
2,
char (end_time - end_noon_end)) AS DECIMAL (20, 2))
/ 3600;
ELSE --结束时间在下午下班后，返回时间为结束时间下午下班时间-午休结束时间
RETURN cast (
timestampdiff (
2,
char (end_work_end - end_noon_end)) AS DECIMAL (20, 2))
/ 3600;
END IF;
ELSEIF start_time - start_work_end <0 --开始时间在下午上班时间内
THEN
IF end_time - end_work_end <0 --结束时间在下午上班时间内，返回时间为结束时间-开始时间
THEN
RETURN cast (
timestampdiff (
2,
char (end_time - start_time)) AS DECIMAL (20, 2))
/ 3600;
ELSE --结束时间在下午下班后,返回时间为下班时间-开始时间
RETURN cast (
timestampdiff (
2,
char (end_work_end - start_time)) AS DECIMAL (20, 2))
/ 3600;
END IF;
ELSE --开始时间在下班后，结束时间也肯定在下班后，返回0
RETURN 0;
END IF;
END IF;
ELSE --开始时间与结束时间不是一天
IF start_holiday > 0 --开始时间为节假日
THEN
IF end_holiday > 0 --结束时间为节假日，返回时间为（结束与开始天数差-中间节假日天数+1）*8
THEN
RETURN (diff_date - holidays + 1) * 8;
ELSE --结束时间不为节假日
IF end_time - end_work_first < 0 --结束时间在上班时间前，返回时间为（结束与开始天数差-中间节假日天数）*8
THEN
RETURN (diff_date - holidays) * 8;
ELSEIF end_time - end_noon_start < 0 --结束时间在上午上班时间内，返回时间为（结束与开始天数差-中间节假日天数）*8+(结束时间-上班时间)
THEN
RETURN (diff_date - holidays) * 8
+ cast (
timestampdiff (
2,
char (endtime - end_work_first)) AS DECIMAL (20, 2))
/ 3600;
ELSEIF end_time - end_noon_end < 0 --结束时间在午休内，返回时间为（结束与开始天数差-中间节假日天数）*8+(午休开始时间-上午上班时间)
THEN
RETURN (diff_date - holidays) * 8
+ cast (
timestampdiff (
2,
char (end_noon_start - end_work_first)) AS DECIMAL (20, 2))
/ 3600;
ELSEIF end_time - end_work_end < 0--结束时间在下午上班时间内，返回时间为（结束与开始天数差-中间节假日天数）*8+(结束时间-结束时间的上班时间-午休时间)
THEN
RETURN (diff_date - holidays) * 8
+ (cast (
timestampdiff (
2,
char (end_time - end_work_first)) AS DECIMAL (20, 2))-noon_difftime)
/ 3600;
ELSE --结束时间大于下班时间，算一天,返回时间为（结束与开始天数差-中间节假日天数+1）*8
RETURN (diff_date - holidays + 1) * 8;
END IF;
END IF;
ELSE --开始时间不为节假日
IF end_holiday > 0 --结束时间为节假日
THEN
IF start_time - start_work_first < 0 --开始时间小于上班时间，算一天，返回时间为（结束与开始天数差-中间节假日天数+1）*8
THEN
RETURN (diff_date - holidays + 1) * 8;
ELSEIF start_time - start_noon_start < 0 --开始时间在上午上班时间内，返回时间为（结束与开始天数差-中间节假日天数）*8+(开始时间下班时间-开始时间-午休时间)
THEN
RETURN (diff_date - holidays) * 8
+ (cast (
timestampdiff (
2,
char (start_work_end - start_time)) AS DECIMAL (20, 2))-noon_difftime)
/ 3600;
ELSEIF start_time - start_noon_end < 0 --开始时间在午休时间内，返回时间为（结束与开始天数差-中间节假日天数）*8+(开始时间下班时间-午休结束时间)
THEN
RETURN (diff_date - holidays) * 8
+ cast (
timestampdiff (
2,
char (start_work_end - start_noon_end)) AS DECIMAL (20, 2))
/ 3600;
ELSEIF start_time - start_work_end < 0 --开始时间在下午上班时间内，返回时间为（结束与开始天数差-中间节假日天数）*8+(开始时间下班时间-开始时间)
THEN
RETURN (diff_date - holidays) * 8
+ cast (
timestampdiff (
2,
char (start_work_end - start_time)) AS DECIMAL (20, 2))
/ 3600;
ELSE --开始时间大于下班时间，返回时间为（结束与开始天数差-中间节假日天数）*8
RETURN (diff_date - holidays) * 8;
END IF;
ELSE --结束时间不为节假日，以开始时间为参照，列出不同开始时间下、不同结束时间下应该返回的时间
IF start_time - start_work_first < 0 --开始时间小于上班时间
THEN
IF end_time - end_work_first < 0 --结束时间小于上班时间，返回时间为（结束与开始天数差-中间节假日天数）*8
THEN
RETURN (diff_date - holidays) * 8;
ELSEIF end_time - end_noon_start < 0 --结束时间在上午上班时间内，返回时间为（结束与开始天数差-中间节假日天数）*8+(结束时间-结束时间上班时间)
THEN
RETURN (diff_date - holidays) * 8
+ cast (
timestampdiff (
2,
char (end_time - end_work_first)) AS DECIMAL (20, 2))/ 3600;
ELSEIF end_time - end_noon_end < 0 --结束时间在午休时间内，返回时间为（结束与开始天数差-中间节假日天数）*8+(午休开始时间-结束时间上班时间)
THEN
RETURN (diff_date - holidays) * 8
+ cast (
timestampdiff (
2,
char (end_noon_start - end_work_first)) AS DECIMAL (20, 2))/ 3600;
ELSEIF end_time - end_work_end < 0 --结束时间在下午上班时间内，返回时间为（结束与开始天数差-中间节假日天数）*8+(结束时间-上班时间-午休时间)
THEN
RETURN (diff_date - holidays) * 8
+ (cast (
timestampdiff (
2,
char (end_time - end_work_first)) AS DECIMAL (20, 2))-noon_difftime)/ 3600;
ELSE --结束时间大于下班时间,算一天，返回时间为（结束与开始天数差-中间节假日天数+1）*8
RETURN (diff_date - holidays + 1) * 8;
END IF;
ELSEIF start_time - start_noon_start < 0--开始时间在上午上班时间内
THEN
IF end_time - end_work_first < 0 --结束时间小于上班时间，返回时间为（结束与开始天数差-中间节假日天数- 1）*8+(开始时间的下班时间-开始时间-午休时间)
THEN
RETURN (diff_date - holidays- 1) * 8+ (cast (
timestampdiff (
2,
char (start_work_end - start_time)) AS DECIMAL (20, 2))-noon_difftime)/ 3600;
ELSEIF end_time - end_noon_start < 0 --结束时间在上午上班时间内，返回时间为（结束与开始天数差-中间节假日天数-1）*8+(开始时间的下班时间-开始时间-午休时间)+(结束时间-结束时间上班时间)
THEN
RETURN (diff_date - holidays- 1) * 8+ (cast (
timestampdiff (
2,
char (start_work_end - start_time)) AS DECIMAL (20, 2))-noon_difftime)/ 3600
+ cast (
timestampdiff (
2,
char (end_time - end_work_first)) AS DECIMAL (20, 2))/ 3600;
ELSEIF end_time - end_noon_end < 0 --结束时间在午休时间内，返回时间为（结束与开始天数差-中间节假日天数-1）*8+(开始时间的下班时间-开始时间-午休时间)+(午休开始时间-结束时间上班时间)
THEN
RETURN (diff_date - holidays- 1) * 8
+ (cast (
timestampdiff (
2,
char (start_work_end - start_time)) AS DECIMAL (20, 2))-noon_difftime)/ 3600
+ cast (
timestampdiff (
2,
char (end_noon_start - end_work_first)) AS DECIMAL (20, 2))/ 3600;
ELSEIF end_time - end_work_end < 0 --结束时间在下午上班时间内，返回时间为（结束与开始天数差-中间节假日天数-1）*8+(开始时间的下班时间-开始时间-午休时间)+(结束时间-上班时间-午休时间)
THEN
RETURN (diff_date - holidays- 1) * 8
+ (cast (
timestampdiff (
2,
char (start_work_end - start_time)) AS DECIMAL (20, 2))-noon_difftime)/ 3600
+ (cast (
timestampdiff (
2,
char (end_time - end_work_first)) AS DECIMAL (20, 2))-noon_difftime)/ 3600;
ELSE --结束时间大于下班时间,算一天，返回时间为（结束与开始天数差-中间节假日天数-1+1）*8+(开始时间的下班时间-开始时间-午休时间)
RETURN (diff_date - holidays ) * 8
+ (cast (
timestampdiff (
2,
char (start_work_end - start_time)) AS DECIMAL (20, 2))-noon_difftime)/ 3600;
END IF;
ELSEIF start_time - start_noon_end < 0--开始时间在午休时间内
THEN
IF end_time - end_work_first < 0 --结束时间小于上班时间，返回时间为（结束与开始天数差-中间节假日天数- 1）*8+(开始时间的下班时间-午休结束时间)
THEN
RETURN (diff_date - holidays- 1) * 8+ (cast (
timestampdiff (
2,
char (start_work_end - start_noon_end)) AS DECIMAL (20, 2)))/ 3600;
ELSEIF end_time - end_noon_start < 0 --结束时间在上午上班时间内，返回时间为（结束与开始天数差-中间节假日天数-1）*8+(开始时间的下班时间-午休结束时间)+(结束时间-结束时间上班时间)
THEN
RETURN (diff_date - holidays- 1) * 8+ (cast (
timestampdiff (
2,
char (start_work_end - start_noon_end)) AS DECIMAL (20, 2)))/ 3600
+ cast (
timestampdiff (
2,
char (end_time - end_work_first)) AS DECIMAL (20, 2))/ 3600;
ELSEIF end_time - end_noon_end < 0 --结束时间在午休时间内，返回时间为（结束与开始天数差-中间节假日天数-1）*8+(开始时间的下班时间-午休结束时间)+(午休开始时间-结束时间上班时间)
THEN
RETURN (diff_date - holidays- 1) * 8
+ (cast (
timestampdiff (
2,
char (start_work_end - start_noon_end)) AS DECIMAL (20, 2)))/ 3600
+ cast (
timestampdiff (
2,
char (end_noon_start - end_work_first)) AS DECIMAL (20, 2))/ 3600;
ELSEIF end_time - end_work_end < 0 --结束时间在下午上班时间内，返回时间为（结束与开始天数差-中间节假日天数-1）*8+(开始时间的下班时间-午休结束时间)+(结束时间-上班时间-午休时间)
THEN
RETURN (diff_date - holidays- 1) * 8
+ (cast (
timestampdiff (
2,
char (start_work_end - start_noon_end)) AS DECIMAL (20, 2)))/ 3600
+ (cast (
timestampdiff (
2,
char (end_time - end_work_first)) AS DECIMAL (20, 2))-noon_difftime)/ 3600;
ELSE --结束时间大于下班时间,算一天，返回时间为（结束与开始天数差-中间节假日天数-1+1）*8+(开始时间的下班时间-午休结束时间)
RETURN (diff_date - holidays ) * 8
+ (cast (
timestampdiff (
2,
char (start_work_end - start_noon_end)) AS DECIMAL (20, 2)))/ 3600;
END IF;
ELSEIF start_time - start_work_end < 0--开始时间在下午上班时间内
THEN
IF end_time - end_work_first < 0 --结束时间小于上班时间，返回时间为（结束与开始天数差-中间节假日天数-1）*8+(开始时间下班时间-开始时间)
THEN
RETURN (diff_date - holidays - 1) * 8
+ (cast (
timestampdiff (
2,
char (start_work_end - start_time)) AS DECIMAL (20, 2)))/ 3600;
ELSEIF end_time - end_noon_start < 0 --结束时间在上午上班时间内，返回时间为（结束与开始天数差-中间节假日天数-1）*8+(开始时间下班时间-开始时间)+(结束时间-结束时间上班时间)
THEN
RETURN (diff_date - holidays - 1) * 8
+ (cast (
timestampdiff (
2,
char (start_work_end - start_time)) AS DECIMAL (20, 2)))/ 3600
+ (cast (
timestampdiff (
2,
char (end_time - end_work_first)) AS DECIMAL (20, 2)))/ 3600 ;
ELSEIF end_time - end_noon_end < 0 --结束时间在午休时间内，返回时间为（结束与开始天数差-中间节假日天数-1）*8+(开始时间下班时间-开始时间)+(结束时间午休开始时间-结束时间上午上班时间)
THEN
RETURN (diff_date - holidays - 1) * 8
+ (cast (
timestampdiff (
2,
char (start_work_end - start_time)) AS DECIMAL (20, 2)))/ 3600
+ (cast (
timestampdiff (
2,
char (end_noon_start - end_work_first)) AS DECIMAL (20, 2)))/ 3600 ;
ELSEIF end_time - end_work_end < 0 --结束时间下午上班时间内，返回时间为（结束与开始天数差-中间节假日天数-1）*8+(开始时间下班时间-开始时间)+(结束时间-结束时间上午上班时间-午休时间)
THEN
RETURN (diff_date - holidays - 1) * 8
+ (cast (
timestampdiff (
2,
char (start_work_end - start_time)) AS DECIMAL (20, 2)))/ 3600
+ (cast (
timestampdiff (
2,
char (end_time - end_work_first)) AS DECIMAL (20, 2))-noon_difftime)/ 3600;
ELSE --结束时间大于下班时间，算一天，返回时间为（结束与开始天数差-中间节假日天数-1+1）*8+(开始时间下班时间-开始时间)
RETURN (diff_date - holidays) * 8
+ (cast (
timestampdiff (
2,
char (start_work_end - start_time)) AS DECIMAL (20, 2)))/ 3600;
END IF;
ELSE --开始时间大于下班时间
IF end_time - end_work_first < 0 --结束时间小于上班时间，返回时间为（结束与开始天数差-中间节假日天数-1）*8
THEN
RETURN (diff_date - holidays - 1) * 8;
ELSEIF end_time - end_noon_start < 0 --结束时间在上午上班时间内，返回时间为（结束与开始天数差-中间节假日天数-1）*8+(结束时间-结束时间上班时间)
THEN
RETURN (diff_date - holidays - 1) * 8
+ (cast (
timestampdiff (
2,
char (end_time - end_work_first)) AS DECIMAL (20, 2)))/ 3600 ;
ELSEIF end_time - end_noon_end < 0 --结束时间在午休时间内，返回时间为（结束与开始天数差-中间节假日天数-1）*8+(结束时间午休开始时间-结束时间上午上班时间)
THEN
RETURN (diff_date - holidays - 1) * 8
+ (cast (
timestampdiff (
2,
char (end_noon_start - end_work_first)) AS DECIMAL (20, 2)))/ 3600 ;
ELSEIF end_time - end_work_end < 0 --结束时间下午上班时间内，返回时间为（结束与开始天数差-中间节假日天数-1）*8+(结束时间-结束时间上午上班时间-午休时间)
THEN
RETURN (diff_date - holidays - 1) * 8
+ (cast (
timestampdiff (
2,
char (end_time - end_work_first)) AS DECIMAL (20, 2))-noon_difftime)/ 3600;
ELSE --结束时间大于下班时间，算一天，返回时间为（结束与开始天数差-中间节假日天数-1+1）*8
RETURN (diff_date - holidays) * 8;
END IF;
END IF;
END IF;
END IF;
END IF;
END;
需要建一张表holidays_settings配置节假日，然后就可以使用了。