Sep 19 2012 19 septembre 2012 19 septembre 2012 10Commentaires de David ANSEAUME

Des fonctions de traitement de dates pour Mysql

Posté dans

Après “Une classe de traitement sur les dates” quelques fonctions pouvant être utiles pour Mysql.

Un fonction native peu connue : LAST_DAY

Qui prend comme valeur le dernier jour du mois correspondant. Par exemple :

SELECT LAST_DAY('2012-02-05') as dernier

Donnera comme résultat : 2012-02-29

Calculer le lundi suivant

CREATE DEFINER=`root`@`localhost` FUNCTION `lundi_suivant`(`datevalue` CHAR(10))
	RETURNS date
	LANGUAGE SQL
	DETERMINISTIC
	CONTAINS SQL
	SQL SECURITY DEFINER
	COMMENT ''
RETURN DATE_ADD(datevalue, INTERVAL (9 - IF(DAYOFWEEK(datevalue)=1, 8, DAYOFWEEK(datevalue))) DAY)

Le jour de pâque

CREATE DEFINER=`root`@`localhost` FUNCTION `easter_date`(`annee` YEAR
)
	RETURNS varchar(255)
	LANGUAGE SQL
	DETERMINISTIC
	NO SQL
	SQL SECURITY DEFINER
	COMMENT 'Calculates easter day for a given year.'
BEGIN

DECLARE a SMALLINT DEFAULT annee % 19;
DECLARE b SMALLINT DEFAULT annee DIV 100;
DECLARE c SMALLINT DEFAULT annee % 100;
DECLARE d SMALLINT DEFAULT b DIV 4;
DECLARE e SMALLINT DEFAULT b % 4;
DECLARE f SMALLINT DEFAULT (b + 8) DIV 25;
DECLARE g SMALLINT DEFAULT (b - f + 1) DIV 3;
DECLARE h SMALLINT DEFAULT (19*a + b - d - g + 15) % 30;
DECLARE i SMALLINT DEFAULT c DIV 4;
DECLARE k SMALLINT DEFAULT c % 4;
DECLARE L SMALLINT DEFAULT (32 + 2*e + 2*i - h - k) % 7;
DECLARE m SMALLINT DEFAULT (a + 11*h + 22*L) DIV 451;
DECLARE v100 SMALLINT DEFAULT h + L - 7*m + 114;

RETURN STR_TO_DATE(
CONCAT(
annee
, '-'
, v100 DIV 31
, '-'
, (v100 % 31) + 1
)
, '%Y-%c-%e'
);

END

Calcul du nombre de jours ouvrés entre deux dates

pour cela on a besoin de deux fonctions, la première pour lister les jours pendant lesquels on ne travaille pas

CREATE DEFINER=`root`@`localhost` FUNCTION `jours_fermes`(`debut` DATE, `fin` DATE)
	RETURNS int(11)
	LANGUAGE SQL
	DETERMINISTIC
	CONTAINS SQL
	SQL SECURITY DEFINER
	COMMENT 'Attention ne fonctionne que sur 2 années consécutives'
BEGIN
	DECLARE nb INT(11);

	DECLARE date_debut DATE;
	DECLARE date_fin DATE;	

	IF (debut > fin) THEN 
		SET date_debut = fin;
		SET date_fin = debut;
	ELSE
		SET date_debut = debut;
		SET date_fin = fin;	
	END IF;	

	SELECT SUM(weekend) INTO nb
	FROM
	(
		SELECT CONCAT(YEAR(date_debut), '-01-01') AS jour_non_travaille, IF( WEEKDAY(CONCAT(YEAR(date_debut), '-01-01')) IN (5,6) , 0 , 1) AS weekend  		
		UNION SELECT CONCAT(YEAR(date_debut), '-05-01') AS jour_non_travaille, IF( WEEKDAY(CONCAT(YEAR(date_debut), '-05-01')) IN (5,6) , 0 , 1) AS weekend 
		UNION SELECT CONCAT(YEAR(date_debut), '-05-08') AS jour_non_travaille, IF( WEEKDAY(CONCAT(YEAR(date_debut), '-05-08')) IN (5,6) , 0 , 1) AS weekend 
		UNION SELECT CONCAT(YEAR(date_debut), '-07-14') AS jour_non_travaille, IF( WEEKDAY(CONCAT(YEAR(date_debut), '-07-14')) IN (5,6) , 0 , 1) AS weekend 			
		UNION SELECT CONCAT(YEAR(date_debut), '-08-15') AS jour_non_travaille, IF( WEEKDAY(CONCAT(YEAR(date_debut), '-08-15')) IN (5,6) , 0 , 1) AS weekend 			
		UNION SELECT CONCAT(YEAR(date_debut), '-11-01') AS jour_non_travaille, IF( WEEKDAY(CONCAT(YEAR(date_debut), '-11-01')) IN (5,6) , 0 , 1) AS weekend 			
		UNION SELECT CONCAT(YEAR(date_debut), '-11-11') AS jour_non_travaille, IF( WEEKDAY(CONCAT(YEAR(date_debut), '-11-11')) IN (5,6) , 0 , 1) AS weekend 			
		UNION SELECT CONCAT(YEAR(date_debut), '-12-25') AS jour_non_travaille, IF( WEEKDAY(CONCAT(YEAR(date_debut), '-12-25')) IN (5,6) , 0 , 1) AS weekend 			
		UNION SELECT DATE_ADD(easter_date(YEAR(date_debut)), INTERVAL 1 DAY) AS jour_non_travaille, 1 AS weekend 		
		UNION SELECT DATE_ADD(easter_date(YEAR(date_debut)), INTERVAL 39 DAY) AS jour_non_travaille, 1 AS weekend 			
		UNION SELECT DATE_ADD(easter_date(YEAR(date_debut)), INTERVAL 50 DAY) AS jour_non_travaille, 1 AS weekend 

		UNION	SELECT CONCAT(YEAR(date_fin), '-01-01') AS jour_non_travaille, IF( WEEKDAY(CONCAT(YEAR(date_fin), '-01-01')) IN (5,6) , 0 , 1) AS weekend  		
		UNION SELECT CONCAT(YEAR(date_fin), '-05-01') AS jour_non_travaille, IF( WEEKDAY(CONCAT(YEAR(date_fin), '-05-01')) IN (5,6) , 0 , 1) AS weekend 
		UNION SELECT CONCAT(YEAR(date_fin), '-05-08') AS jour_non_travaille, IF( WEEKDAY(CONCAT(YEAR(date_fin), '-05-08')) IN (5,6) , 0 , 1) AS weekend 
		UNION SELECT CONCAT(YEAR(date_fin), '-07-14') AS jour_non_travaille, IF( WEEKDAY(CONCAT(YEAR(date_fin), '-07-14')) IN (5,6) , 0 , 1) AS weekend 			
		UNION SELECT CONCAT(YEAR(date_fin), '-08-15') AS jour_non_travaille, IF( WEEKDAY(CONCAT(YEAR(date_fin), '-08-15')) IN (5,6) , 0 , 1) AS weekend 			
		UNION SELECT CONCAT(YEAR(date_fin), '-11-01') AS jour_non_travaille, IF( WEEKDAY(CONCAT(YEAR(date_fin), '-11-01')) IN (5,6) , 0 , 1) AS weekend 			
		UNION SELECT CONCAT(YEAR(date_fin), '-11-11') AS jour_non_travaille, IF( WEEKDAY(CONCAT(YEAR(date_fin), '-11-11')) IN (5,6) , 0 , 1) AS weekend 			
		UNION SELECT CONCAT(YEAR(date_fin), '-12-25') AS jour_non_travaille, IF( WEEKDAY(CONCAT(YEAR(date_fin), '-12-25')) IN (5,6) , 0 , 1) AS weekend 			
		UNION SELECT DATE_ADD(easter_date(YEAR(fin)), INTERVAL 1 DAY) AS jour_non_travaille, 1 AS weekend 		
		UNION SELECT DATE_ADD(easter_date(YEAR(fin)), INTERVAL 39 DAY) AS jour_non_travaille, 1 AS weekend 			
		UNION SELECT DATE_ADD(easter_date(YEAR(fin)), INTERVAL 50 DAY) AS jour_non_travaille, 1 AS weekend 		

	) jours

	WHERE 
		jour_non_travaille BETWEEN date_debut AND date_fin	 ;

	IF (nb IS null)
	then SET nb = 0;
	END IF;

	RETURN nb;
END

La seconde, qui compte le nombre de jours entre deux dates, et qui extrait les jours non travaillés

CREATE DEFINER=`root`@`localhost` FUNCTION `jours_travailles`(`debut` DATE, `fin` DATE)
	RETURNS int(11)
	LANGUAGE SQL
	DETERMINISTIC
	CONTAINS SQL
	SQL SECURITY DEFINER
	COMMENT ''
BEGIN
	DECLARE nb INT(11);

	DECLARE date_debut DATE;
	DECLARE date_fin DATE;	

	IF (debut > fin) THEN 
		SET date_debut = fin;
		SET date_fin = debut;
	ELSE
		SET date_debut = debut;
		SET date_fin = fin;	
	END IF;

	SELECT 		
		dd.iNbjours - dd.iNbjoursWeekend - jours_fermes(date_debut, date_fin) - 1 INTO nb
	FROM 
		( SELECT
			dd.iNbjours,
			((dd.iSemaines * 2) + 
			IF (dd.iSamediDiff >= 0 AND dd.iSamediDiff < dd.iJours, 1, 0) + 
			IF (dd.iDimancheDiff >= 0 AND dd.iDimancheDiff < dd.iJours, 1, 0)) AS iNbjoursWeekend 	
		FROM
			( SELECT
		      dd.iNbjours,
		      FLOOR(dd.iNbjours / 7) AS iSemaines,
		      dd.iNbjours % 7 AS iJours, 
		      5 - dd.iJourDebut AS iSamediDiff,
		      6 - dd.iJourDebut AS iDimancheDiff
			FROM
			( SELECT 
				1 + ABS(DATEDIFF(date_fin, date_debut)) AS iNbjours,
				WEEKDAY(date_debut) AS iJourDebut 
			) AS dd 
		) AS dd
	) AS dd;	

	if nb <0 
	then 
		set nb = 0;
	end if;
	RETURN nb;
END

Et que l’on appellera de la manière suivante

select jours_travailles('2012-02-05', '2012-03-14') as jours_ouvres

Voila, quelques petites fonctions qui aident a faire des calculs de dates.