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.
Tu le mets pas souvent à jour mais quand tu le fais, ça mérite qu’on le mette en favoris !
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:
Et tout passe bien maintenant.
en remplacement du code précédent:
Merci.
Quelle était l’erreur rencontrée?
L’erreur Mysql était qu’il ne pouvait pas faire un INTO avec des UNION.
En supprimant (pour le test) toutes les unions la fonction se créait mais du moment ou j’ajoutais un union j’avais l’erreur.
Ce qui m’étonne c’est que sur la version 5.0.51a cela ne fonctionne pas et sur une version 5.0.96 cela fonctionnait.
C’est peut être lié au bug référencé ici : http://bugs.mysql.com/bug.php?id=23345
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
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.
😆 Heureusement, je l’utilise sur plusieurs reportings, merci pour tes commentaires