Category Archives: Non classé

Des fonctions de traitement de dates pour Mysql

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.