Des fonctions de traitement de dates pour Mysql

closeCet article a été publié il y a 5 ans 2 jours, il est donc possible qu’il ne soit plus à jour. Les informations proposées sont donc peut-être expirées.

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.

  1. Tu le mets pas souvent à jour mais quand tu le fais, ça mérite qu’on le mette en favoris !

  2. :mrgreen: Ben, ça me prend de temps en temps 😳

  3. Merci pour ces fonctions que je recherchais depuis un bout de temps.
    Par contre j’ai eu un problème d’enregistrement avec la version 5.0.51a de MySQL de la fonction jours_fermes. Les UNION posaient problèmes.
    En version 5.0.96 elle est correctement passée.

    Du coup j’ai du réécrire la fonction:

    CREATE FUNCTION jours_fermes_test(debut DATE, fin DATE) RETURNS int(11)
    	LANGUAGE SQL
    	DETERMINISTIC
        COMMENT 'Calcul le nb de jours fériés'
    BEGIN
    	DECLARE nb INT(11) default 0;
    	DECLARE date_debut DATE;
    	DECLARE date_fin DATE;
    
    	DECLARE jour_non_travaille DATE;
    	DECLARE weekend TINYINT default 0;
    	DECLARE CUR_SELECT_DATE CURSOR FOR
    	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;
    
    	DECLARE NOMORE INT DEFAULT FALSE;
    	DECLARE CONTINUE HANDLER FOR NOT FOUND SET NOMORE = TRUE;
    
    	-- On remet les dates dans l'ordre
    	IF (debut > fin) THEN
    		SET date_debut = fin;
    		SET date_fin = debut;
    	ELSE
    		SET date_debut = debut;
    		SET date_fin = fin;
    	END IF;
    
    
    	OPEN CUR_SELECT_DATE;
    	read_loop: LOOP
    		FETCH CUR_SELECT_DATE INTO jour_non_travaille, weekend;
    			IF NOMORE THEN
    			LEAVE read_loop;
    		END IF;
    	
    		-- On compte les jours non travaillés
    		if weekend=1 and jour_non_travaille BETWEEN date_debut AND date_fin then
    			set nb = nb + 1;
    		end if;
    	END LOOP;
    	CLOSE CUR_SELECT_DATE;
    	
    	
    	IF (nb IS null)
    	then 
    		SET nb = 0;
    	END IF;
    	
    	RETURN nb;
    END;
    

    Et tout passe bien maintenant.

  4. en remplacement du code précédent:

    CREATE FUNCTION jours_fermes(debut DATE, fin DATE) RETURNS int(11)
    	LANGUAGE SQL
    	DETERMINISTIC
        COMMENT 'Calcul le nb de jours fériés'
    BEGIN
    	DECLARE nb INT(11) default 0;
    	DECLARE date_debut DATE;
    	DECLARE date_fin DATE;
    
    	DECLARE jour_non_travaille DATE;
    	DECLARE weekend TINYINT default 0;
    
    	DECLARE NOMORE INT DEFAULT FALSE;
    	
    	
    	DECLARE CUR_SELECT_DATE CURSOR FOR
    	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;
    
    	DECLARE CONTINUE HANDLER FOR NOT FOUND SET NOMORE = TRUE;
    
    	-- On remet les dates dans l'ordre
    	IF (debut > fin) THEN
    		SET date_debut = fin;
    		SET date_fin = debut;
    	ELSE
    		SET date_debut = debut;
    		SET date_fin = fin;
    	END IF;
    
    
    	OPEN CUR_SELECT_DATE;
    	read_loop: LOOP
    		FETCH CUR_SELECT_DATE INTO jour_non_travaille, weekend;
    			IF NOMORE THEN
    			LEAVE read_loop;
    		END IF;
    	
    		-- On compte les jours non travaillés
    		if weekend=1 and jour_non_travaille BETWEEN date_debut AND date_fin then
    			set nb = nb + 1;
    		end if;
    	END LOOP;
    	CLOSE CUR_SELECT_DATE;
    	
    	
    	IF (nb IS null)
    	then 
    		SET nb = 0;
    	END IF;
    	
    	RETURN nb;
    END;
    
    

    Merci.

  5. Quelle était l’erreur rencontrée?

  6. bonjour, la fonction du lundi de pâques n’est pas fiable, en 2017, la fonction donne 2017-04-16 alors que c’est le 2017-04-17.
    Dommage !
    Tous les commentaires que j’ai lu, dises la même chose, qu’il n’est pas possible de faire un calcul fiable.
    Bonne continuation

  7. toutes mes excuses, je me suis focalisée sur le lundi alors que vous donnez le jour de pâques, je viens de nouveau faire les tests pour 2017, 2018 et 2019, ça fonctionne.

  8. 😆 Heureusement, je l’utilise sur plusieurs reportings, merci pour tes commentaires

Laisser un commentaire


NOTE - Vous pouvez utiliser les éléments et attributs HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>