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.

Une classe de traitement sur les dates

Pour un projet, j’ai eu besoin de faire pas mal de calculs de dates, j’ai donc travaillé sur une petite classe, que j’améliorerai et documenterais avec le temps.

Cette petite classe permet de calculer les Numéros de semaines, de connaitre la date du lundi de la semaine en cours, le lundi suivant, le lundi précédent… et surtout, de calculer un tableau donnant les jours fériés d’une année.

<?php
class InfosDates {
    static $mois = array(
        1=>'Janvier',
        2=>'Février',
        3=>'Mars',
        4=>'Avril',
        5=>'Mai',
        6=>'Juin',
        7=>'Juillet',
        8=>'Août',
        9=>'Septembre',
        10=>'Octobre',
        11=>'Novembre',
        12=>'Decembre'
    );

    static public function is_date($j_date) {
        return preg_match( '`^\d{1,2}/\d{1,2}/\d{4}$`' , $j_date ) ;
    }

    static public function get_liste_mois() {
        return self::$mois;
    }

    static public function get_mois($id_mois) {
        return self::$mois[$id_mois];
    }

    static public function get_num_semaine($date) {
	list($annee, $mois, $jour) = explode('-', $date);
        return date("W", mktime(0, 0, 0, $mois, $jour, $annee));
    }

    static public function get_semaine($date) {
        list($annee, $mois, $jour) = explode('-', self::get_lundi($date));
        $semaine = array();
        for ($i=1; $i<=7; $i++) {
            $semaine[] = date("Y-m-d", mktime(0, 0, 0, $mois, $jour+$i-1, $annee));
        }
        return $semaine;
    }

    static public function get_lundi($date) {
	list($annee, $mois, $jour) = explode('-', $date);
        $jour_semaine = date("N", mktime(0, 0, 0, $mois, $jour, $annee));
        return date("Y-m-d", mktime(0, 0, 0, $mois, $jour - $jour_semaine + 1, $annee));
    }

    static public function get_lundi_suivant($date) {
        list($annee, $mois, $jour) = explode('-', $date);
        return self::get_lundi($jour+7, $mois, $annee);
    }

    static public function get_lundi_precedent($date) {
        list($annee, $mois, $jour) = explode('-', $date);
        return self::get_lundi($jour-7, $mois, $annee);
    }

    static public function premier_jour_mois($date) {
	list($annee, $mois, $jour) = explode('-', $date);
        return date("Y-m-d", mktime(0, 0, 0, $mois, 1, $annee));
    }

    static public function dernier_jour_mois($date) {
	list($annee, $mois, $jour) = explode('-', $date);
        $jour = self::nombre_jours_mois($mois, $annee);
        return date("Y-m-d", mktime(0, 0, 0, $mois, $jour, $annee));
    }

    static public function numero_premier_jour_mois($date) {
	list($annee, $mois, $jour) = explode('-', $date);
        return date("N", mktime(0, 0, 0, $mois, 1, $annee));
    }

    static public function nombre_jours_mois($date) {
	list($annee, $mois, $jour) = explode('-', $date);
        return date('t', mktime(0, 0, 0, $mois, 1, $annee));
    }

    static public function fr_en($date) {
        list($jour, $mois, $annee) = explode('/', $date);
        return $annee.'-'.$mois.'-'.$jour;
    }

    static public function en_fr($date) {
        list($annee, $mois, $jour) = explode('-', $date);
        return $jour.'/'.$mois.'/'.$annee;
    }

    static public function jour_ouvre($date) {
        list($annee, $mois, $jour) = explode('-', $date);
        $j_semaine = date("N", mktime(0, 0, 0, $mois, $jour, $annee));
        if ($j_semaine !=6 && $j_semaine !=7) {
            if (!array_key_exists($date, self::ferie($annee))) {
                // jour ouvré
                return true;
            }
            else {
                // jour non ouvré
                return false;
            }
        }
        else  {
            // jour non ouvré
            return false;
        }
    }

    /**
     * Permet de compter le nombre de jour ouvrés entre 2 dates
     * Un jour est ouvré si ce n'est pas un week end et s'il n'est pas férié
     * @param <Date> $date_debut
     * @param <Date> $date_fin
     * @return <int> $nb_jours_ouvres
     **/
    static public function nb_jours_ouvres($date_debut, $date_fin) {
        list($annee, $mois, $jour) = explode('-', $date_fin);
        $nb_jours_ouvres = 0;
        $timestamp_debut = self::get_timestamp($date_debut);
        $timestamp_fin = self::get_timestamp($date_fin);
        $nb_jours_intervalle = self::nb_jours_intervalle($timestamp_debut, $timestamp_fin);
        $date_temp = $date_debut;
        $tab_date = Array();
        array_push($tab_date, $date_temp);

        //Pour chaque jour entre la date de début et celle de fin
        for ($i = 1; $i <= $nb_jours_intervalle; $i++) {
            //Puis on va incrémenter la date d'un jour : on sépare les éléments
            list($annee, $mois, $jour) = explode('-', $date_temp);

            //Si demain est toujours dans le même mois, on incrémente simplement le jour
            if ($jour + 1 <= self::nombre_jours_mois($mois, $annee)) {
                if ($jour + 1 < '10') $date_temp = $annee.'-'.$mois.'-0'.($jour + 1);
                else $date_temp = $annee.'-'.$mois.'-'.($jour + 1);
            }
            //On ajoute la date au format 'Y-m-d' au tableau
            array_push($tab_date, $date_temp);
        }
        foreach ($tab_date as $date) {
            if (self::jour_ouvre($date)) $nb_jours_ouvres ++;
        }

        return $nb_jours_ouvres;
    }

    static public function get_date($timestamp) {
        return date('Y-m-d', $timestamp);
    }

    static public function get_timestamp($date) {
        list($annee, $mois, $jour) = explode('-', $date);
        return mktime(0, 0, 0, $mois, $jour, $annee);
    }

    static public function nb_jours_intervalle($timestamp_debut, $timestamp_fin) {
        $difference = $timestamp_fin - $timestamp_debut;
        //L'arrondi ci-dessous sert à éviter le problème du changement d'heure
        return round($difference / 86400);
    }

    static public function convert_jour($jour, $format = '%d %B %Y') {
        setlocale(LC_ALL, 'fr_FR');
        return utf8_encode(strftime($format, strtotime($jour)));
    }

    static public function ferie($annee){
        $ferie = array();        

        // une constante
        $jour = 3600*24;

        // les jours fixes
        $ferie[$annee.'-01-01'] = "Jour de l'an";
        $ferie[$annee.'-05-01'] = "Fête du travail";
        $ferie[$annee.'-05-08'] = "Armistice 39-45";
        $ferie[$annee.'-07-14'] = "Fête Nationale";
        $ferie[$annee.'-08-15'] = "Assomption";
        $ferie[$annee.'-11-01'] = "Toussaint";
        $ferie[$annee.'-11-11'] = "Armistice 14-18";
        $ferie[$annee.'-12-25'] = "Noël";

        $paque = easter_date($annee);
        // quelques fetes mobiles
        $ferie[
            $annee.'-'.
            str_pad(date( "n", $paque+1*$jour), 2,'0', STR_PAD_LEFT).'-'.
            str_pad(date( "j", $paque+1*$jour), 2,'0', STR_PAD_LEFT)]     = "Lundi de Pâque";
        $ferie[
            $annee.'-'.
            str_pad(date( "n", $paque+39*$jour), 2,'0', STR_PAD_LEFT).'-'.
            str_pad(date( "j", $paque+39*$jour), 2,'0', STR_PAD_LEFT)]    = "Jeudi de l'Ascension";
        $ferie[
            $annee.'-'.
            str_pad(date( "n", $paque+50*$jour), 2,'0', STR_PAD_LEFT).'-'.
            str_pad(date( "j", $paque+50*$jour), 2,'0', STR_PAD_LEFT)]    = "Lundi de Pentecôte";

        return $ferie;
    }
}
?>

Vous pouvez la télécharger ici : classInfosDates

Graine de Crapule

Ça y est, le site Graine de Crapule : http://www.grainedecrapule.fr est publié et officiellement ouvert. Un espace privilégié pour nos petites crapules.

Trouvez votre bonheur sur la nouvelle boutique en ligne http://www.grainedecrapule.fr. Un espace privilégié pour nos petites crapules. Des articles originaux qui ne manquent pas de style pour votre plaisir ou pour faire des heureux.

Chaussons bébé en cuir souple, chaussettes,bodys, pyjamas,bonnets, bavoirs…

Lire la suite »

Sortie Ubuntu 8.04 Hardy Heron

Aujourd’hui c’était la sortie de Hardy Heron, je pensais attendre ce week-end pour l’installer, mais un fichu spyware sur mon windows XP m’en a dissuadé.

Du coup, je glisse le CD de Ubuntu dans mon lecteur, et demarre la machine dessus, il me propose plusieurs choix, mais je prends directement celui : Installer, pas de dual boot, pas de live CD, pas de regrets, Bye Windows, on s’entendaient bien, mais parfois tu as besoins d’un bon dégraissage.

Un peu de mal à reprendre mes marque, cela ne fait pas longtemps que je joue avec linux, mais j’arrive petit à petit à m’en sortir, en tout cas une chose appréciable, je n’ai pas besoin de jouer avec la recherche de drivers.

Ne pas s’enerver, ne pas s’enerver

Les systèmes de campagnes e-mailing parfois quelle plaie, hier j’ai été obligé de menacer pour que ma désinscription puisse être effective.

Pour la petite histoire, cela fait à peu près une semaine que j’essaie de de désinscrire à une newsletter un peu trop envahissante à mon goût : au moins un mail par jour, d’accord au départ je m’étais inscrit dessus intéressé par un service qu’ils proposaient, mais autant s’inscrire fus facile, autant se désinscrire fus plus difficile.

Lire la suite »