Liste des jours entre deux dates

Version imprimable

18/01/2012, 13h33
rvzip64

Liste des jours entre deux dates

Salut tout le monde,

Je chercher à faire une requête qui me donnerait la liste des jours entre deux dates.

J'ai (mal?) fais une recherche mais je ne trouve pas de solution.

Merci d'avance pour votre aide
18/01/2012, 14h53
CinePhil

Il faut que tu modélises et implémentes un calendrier.
18/01/2012, 15h20
rvzip64

:massacre:

Euh ....

Y a pas un moyen plus simple ?
18/01/2012, 15h32
CinePhil

Tu n'es pas forcément obligé de construire un calendrier aussi complexe que celui de SQLPro mais si tu veux tous les jours entre deux dates, il te faut a minima une table des dates.
18/01/2012, 15h46
rvzip64

C'est une limitation de Mysql, ou c'est pour toutes les bases de données la même chose ?
18/01/2012, 15h48
CinePhil

Aucun SGBD ne contient la liste des dates. Ce sera pareil pour tous les SGBD.
Les SGBD savent seulement faire des calculs sur les types DATE et DATETIME, souvent avec des fonctions qui leur sont propres.
18/01/2012, 16h26
Rei Ichido

On peut quand même s'en tirer avec des CTE !
18/01/2012, 16h38
rvzip64

Des CTE ? c'est à dire ?
18/01/2012, 17h04
CinePhil

Citation:

Envoyé par Rei Ichido

On peut quand même s'en tirer avec des CTE !

Pas avec MYSQL !
Extrait de cet article :

Citation:

SQL pauvre (MySQL ne respecte même pas la norme de 1992 !) :
...
- pas d'expression de tables (CTE)

Citation:

Envoyé par rvzip64

Des CTE ? c'est à dire ?

Comon Table Expression.
31/08/2015, 10h52
chapt0011
Désolé je déterre le sujet, mais j'ai cherché longtemps et je pense que cette solution peut servir!

Situation:

Vous disposez d'une table A contenant une date de début et une date de fin (Exemple: Sejour)
Vous souhaitez lister les dates entre la date de début et la date de fin de sejour?
C'est faisable en une seule requête en suivant la procédure suivante:
1. 1- Créer une table "enumeration" avec 2 colonnes: idEnumeration, no_jour
2. 2- Lister dans cette table numéro de jours de l'année (1 à 366)
3. 3- La requête: nous allons utiliser les fonctions from_days,to_days et dayofyear de mysql.
  
  Code:
  
  SELECT DISTINCT s.idSejour, s.dEntree,s.dSortie,FROM_DAYS(TO_DAYS(s.dEntree)-DAYOFYEAR(s.dEntree)+e.no_jour) as date_presence FROM sejour s INNER JOIN enumeration e ON e.no_jour BETWEEN DAYOFYEAR(s.dEntree) AND DAYOFYEAR(s.dSortie) WHERE s.idSejour=123456 ORDER BY date_presence ASC
  - Ainsi pour un sejour donné (ici le sejour n° 123456), le moteur fait la jointure par rapport au n° de jour dans l'année avec DAYOFYEAR()
    Et donc on associe aux deux dates de début et de fin tous les numéros de jours compris entre l'une et l'autre grâce à :
    
    Code:
    
    INNER JOIN enumeration e ON e.no_jour BETWEEN DAYOFYEAR(s.dEntree) AND DAYOFYEAR(s.dSortie)
  - Puis dans le select, il nous reste à faire le calcul:
    TO_DAYS() transforme la date d'entrée en un numéro de jour correspondant à une date: par exemple le résultat de TO_DAYS('1915-01-12') vaut x=699450
    Inversement FROM_DAYS(699450) vaut y='1915-01-12'
    on retranche à x le nombre de jour depuis le début de l'année DAYOFYEAR('1915-01-12') vaut z=12
  - il nous reste à ajouté un par un tous les numéros de jours récupérés dans la jointure donc + e.no_jour
    Et finalement on retransforme en date avec FROM_DAYS()
4. Donc pour l'exemple admettons que le séjour démarre le 1915-01-12 et termine le 1915-01-15
  La jointure va retourner les numéros de jours associés au dates à savoir e.no_jour = 12,13,14 et 15
  x=699450 z1=12 e.no_jour=12 : x-Z1+e.no_jour=x et donc FROM_DAYS(x)=1915-01-12
  x=699450 z1=12 e.no_jour=13 : x-Z1+e.no_jour=x+1 et donc FROM_DAYS(x+1)=1915-01-13
  x=699450 z1=12 e.no_jour=14 : x-Z1+e.no_jour=x+2 et donc FROM_DAYS(x+2)=1915-01-14
  x=699450 z1=12 e.no_jour=15 : x-Z1+e.no_jour=x+3 et donc FROM_DAYS(x+3)=1915-01-15
L'avantage de cette solution est qu'elle ne nécessite pas de générer tous les dates existantes dans une table qu'il faudrait remettre sans cesse à jour, les numéros de jours dans l'année sont fixes et toujours vrais (sauf si l'on change de calendrier :mouarf::aie: pas taper!)
De plus, les fonctions MySQL sont compatibles avec les années bissextiles, inutile de modéliser un calendrier qui existe déjà.
Le seul inconvénient est plutôt du côté de la durée d'éxécution notamment si au lieu de lister les dates pour un seul séjour, on veux les dates de tous les séjours compris entre deux autres dates.
Ceci étant ça reste raisonnable (me concernant, quelques secondes tout au plus pour 20000 séjour). Tout dépend donc de l'utilisation qu'on veut en faire.
Bon code à tous.
31/08/2015, 22h30
CinePhil

Intéressant mais, comme je n'ai pas testé, je pose la question : est-ce que ça fonctionne si la période est à cheval sur deux années civiles ?
01/09/2015, 14h38
chapt0011

Citation:

Envoyé par CinePhil

Intéressant mais, comme je n'ai pas testé, je pose la question : est-ce que ça fonctionne si la période est à cheval sur deux années civiles ?

Effectivement il y a un hic pour ce cas là, je me suis un peu précipité...
Toutefois il y a une astuce qui fonctionne.
D'abord, il est nécessaire de modifier un peu notre table de numéros de jours, en effet le type doit être VARCHAR et non plus INTEGER
il faut y mettre tous les numéros de jours de 001 à 366 et non plus de 1 à 366. Ou alors on peut aussi simplement ajouter une colonne no_jour_txt et adapter un peu notre requête. Mais dans tous les cas, dans la jointure, nous n'aurons plus qu'à concaténer avec l'année comme ceci:

Code:

INNER JOIN enumeration e ON (CONCAT(YEAR(s.dEntree),e.no_jour) BETWEEN CONCAT(YEAR(s.dEntree),DAYOFYEAR(s.dEntree)) AND CONCAT(YEAR(s.dSortie),DAYOFYEAR(s.dSortie))) OR (CONCAT(YEAR(s.dSortie),e.no_jour) BETWEEN CONCAT(YEAR(s.dEntree),DAYOFYEAR(s.dEntree)) AND CONCAT(YEAR(s.dSortie),DAYOFYEAR(s.dSortie)))

C'est un peu sale mais ça doit marcher. Je teste tout ça et reviens vers vous!

1 pièce(s) jointe(s)

Voila le final:

La requête:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 SELECT DISTINCT idSejour,s.dEntree,s.dSortie, CASE WHEN CONCAT(YEAR(s.dEntree),right(CONCAT('000',e.cle_externe),3)) BETWEEN CONCAT(YEAR(s.dEntree),right(CONCAT('000',DAYOFYEAR(s.dEntree)),3)) AND CONCAT(YEAR(s.dSortie),right(CONCAT('000',DAYOFYEAR(s.dSortie)),3)) THEN FROM_DAYS(TO_DAYS(s.dEntree)-DAYOFYEAR(s.dEntree)+e.cle_externe) ELSE DATE_FORMAT(FROM_DAYS(TO_DAYS(s.dEntree)-DAYOFYEAR(s.dEntree)+e.cle_externe),CONCAT(YEAR(s.dSortie),'-%m-%d')) END as date_presence, e.cle_externe FROM sejour s INNER JOIN enumeration e ON (CONCAT(YEAR(s.dEntree),right(CONCAT('000',e.cle_externe),3)) BETWEEN CONCAT(YEAR(s.dEntree),right(CONCAT('000',DAYOFYEAR(s.dEntree)),3)) AND CONCAT(YEAR(s.dSortie),right(CONCAT('000',DAYOFYEAR(s.dSortie)),3)) AND e.type='dayofyear') OR (CONCAT(YEAR(s.dSortie),right(CONCAT('000',e.cle_externe),3)) BETWEEN CONCAT(YEAR(s.dEntree),right(CONCAT('000',DAYOFYEAR(s.dEntree)),3)) AND CONCAT(YEAR(s.dSortie),right(CONCAT('000',DAYOFYEAR(s.dSortie)),3)) AND e.type='dayofyear') WHERE idSejour=14029996 ORDER BY idSejour,date_presence ASC
Il n'est pas utile de modifier la table des numéros de jours puisqu'on peut le faire dynamiquement comme c'est fait dans la requête ci-dessus!
Je vous poste également un résultat sous forme d'image pour un séjour qui chevauche deux années!
A noter un doublon pour le 01/01/2015, celui ci se retire très facilement en enlevant l'affichage de la cle_externe dans la requête ;-)
Par ailleurs, sachez que j'utilise ma table enumeration pour de nombreuses chose d'ou le champ "type" pour lequel je demande égal à "dayofyear"
c'est une manière pour moi de filtrer ;-)
Bon code à vous.
Pièce jointe 186868

PS: Ce code ne fonctionne pas uniquement pour les séjours chevauchant 2 années, un séjour non chevauchant fonctionnera aussi...
Toutefois, je n'ai aucun cas et n'en n'aurais jamais pour lesquels le séjour dure plus d'un an donc inutile de me demander si ça fonctionne, libre à vous de tester, d'améliorer :D