salix/db/routines/vn/functions/workerNigthlyHours_calculate.sql

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` FUNCTION `vn`.`workerNigthlyHours_calculate`(vTimeIn DATETIME, vTimeOut DATETIME)
	RETURNS decimal(5,2)
	NOT DETERMINISTIC
	READS SQL DATA
BEGIN
/**
 * Calcula el número decimal de horas nocturnas comprendidas entre 2 horas dadas
 *
 * @param vTimeIn
 * @param vTimeOut
 * @return vNigthlyHours
 */
	DECLARE vNigthlyHours DECIMAL(5,2); 
	DECLARE vSecondsPerHour INT(4) DEFAULT 3600;
	
	SELECT GREATEST(0, TIMESTAMPDIFF(SECOND,
				IF(TIME(vTimeIn) BETWEEN endNightlyHours AND startNightlyHours, 
						CONCAT(DATE(vTimeIn), ' ', startNightlyHours), 
						vTimeIn),
				IF(TIME(vTimeOut) BETWEEN endNightlyHours AND startNightlyHours, 
						CONCAT(DATE(vTimeOut), ' ', endNightlyHours), 
						vTimeOut))) / vSecondsPerHour INTO vNigthlyHours
		FROM vn.workerTimeControlConfig;

	RETURN vNigthlyHours;
END$$
DELIMITER ;