générer des jours à partir d'une plage de dates

Question

Je voudrais exécuter une requête comme

select ... as days where `date` is between '2010-01-20' and '2010-01-24'

Et retourner des données comme :

days
----------
2010-01-20
2010-01-21
2010-01-22
2010-01-23
2010-01-24

Answer 1

Cette solution utilise pas de boucles, de procédures ou de tables temporaires . La sous-requête génère des dates pour les 10 000 derniers jours, et peut être étendue pour remonter ou avancer aussi loin que vous le souhaitez.

select a.Date 
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
) a
where a.Date between '2010-01-20' and '2010-01-24' 

Sortie :

Date
----------
2010-01-24
2010-01-23
2010-01-22
2010-01-21
2010-01-20

Notes sur la performance

L'essai aquí la performance est étonnamment bonne : la requête ci-dessus prend 0.0009 sec.

Si nous étendons la sous-requête pour générer environ 100 000 numéros (et donc environ 274 années de dates), elle s'exécute en 0,0458 seconde.

Soit dit en passant, il s'agit d'une technique très portable qui fonctionne avec la plupart des bases de données moyennant quelques ajustements mineurs.

Exemple de SQL Fiddle retournant 1 000 jours

Answer 2

Voici une autre variante utilisant des vues :

CREATE VIEW digits AS
  SELECT 0 AS digit UNION ALL
  SELECT 1 UNION ALL
  SELECT 2 UNION ALL
  SELECT 3 UNION ALL
  SELECT 4 UNION ALL
  SELECT 5 UNION ALL
  SELECT 6 UNION ALL
  SELECT 7 UNION ALL
  SELECT 8 UNION ALL
  SELECT 9;

CREATE VIEW numbers AS
  SELECT
    ones.digit + tens.digit * 10 + hundreds.digit * 100 + thousands.digit * 1000 AS number
  FROM
    digits as ones,
    digits as tens,
    digits as hundreds,
    digits as thousands;

CREATE VIEW dates AS
  SELECT
    SUBDATE(CURRENT_DATE(), number) AS date
  FROM
    numbers;

Et ensuite, vous pouvez simplement faire (vous voyez comme c'est élégant ?) :

SELECT
  date
FROM
  dates
WHERE
  date BETWEEN '2010-01-20' AND '2010-01-24'
ORDER BY
  date

Mise à jour

Il convient de noter que vous ne pourrez générer que les dates passées à partir de la date actuelle . Si vous voulez générer n'importe quel type de plage de dates (passé, futur, et entre les deux), vous devrez utiliser cette vue à la place :

CREATE VIEW dates AS
  SELECT
    SUBDATE(CURRENT_DATE(), number) AS date
  FROM
    numbers
  UNION ALL
  SELECT
    ADDDATE(CURRENT_DATE(), number + 1) AS date
  FROM
    numbers;

Answer 3

À l'aide d'une expression de table commune (CTE) récursive, vous pouvez générer une liste de dates, puis effectuer une sélection dans cette liste. Il est évident que vous ne voudriez pas créer trois millions de dates, mais ceci n'est qu'une illustration des possibilités. Vous pourriez simplement limiter la plage de dates dans l'expression de table commune et omettre la clause where de l'instruction de sélection utilisant l'expression de table commune.

with [dates] as (
    select convert(datetime, '1753-01-01') as [date] --start
    union all
    select dateadd(day, 1, [date])
    from [dates]
    where [date] < '9999-12-31' --end
)
select [date]
from [dates]
where [date] between '2013-01-01' and '2013-12-31'
option (maxrecursion 0)

Sur Microsoft SQL Server 2005, la génération de la liste CTE de toutes les dates possibles a pris 1:08. La génération de cent années a pris moins d'une seconde.

Answer 4

La solution traditionnelle pour réaliser cette opération sans boucle/curseur consiste à créer un fichier de type NUMBERS qui comporte une seule colonne "Integer" dont les valeurs commencent à 1.

CREATE TABLE  `example`.`numbers` (
  `id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Vous devez alimenter la table avec suffisamment d'enregistrements pour couvrir vos besoins :

INSERT INTO NUMBERS (id) VALUES (NULL);

Une fois que vous avez le NUMBERS vous pouvez utiliser :

SELECT x.start_date + INTERVAL n.id-1 DAY
  FROM NUMBERS n
  JOIN (SELECT STR_TO_DATE('2010-01-20', '%Y-%m-%d') AS start_date 
          FROM DUAL) x
 WHERE x.start_date + INTERVAL n.id-1 DAY <= '2010-01-24'

La solution la plus simple serait :

SELECT STR_TO_DATE('2010-01-20', '%Y-%m-%d')
 FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-21', '%Y-%m-%d')
 FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-22', '%Y-%m-%d')
 FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-23', '%Y-%m-%d')
 FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-24', '%Y-%m-%d')
 FROM DUAL

A quoi servirait-il ?

---

Pour générer des listes de dates ou de nombres afin de les joindre à gauche (LEFT JOIN). Vous pouvez le faire pour voir où il y a des lacunes dans les données, car vous effectuez un LEFT JOIN sur une liste de données séquentielles - les valeurs nulles rendent évidentes les lacunes.

Answer 5

Pour Access 2010 - plusieurs étapes nécessaires ; j'ai suivi le même schéma que celui posté ci-dessus, mais j'ai pensé que je pourrais aider quelqu'un en Access. Cela a très bien fonctionné pour moi, je n'ai pas eu besoin de conserver un tableau de dates ensemencé.

Créez une table appelée DUAL (similaire au fonctionnement de la table DUAL d'Oracle).

• ID (Numéro d'identification automatique)
• DummyColumn (Texte)
• Ajouter une ligne de valeurs (1, "DummyRow")

Créez une requête nommée "ZeroThru9Q" ; entrez manuellement la syntaxe suivante :

SELECT 0 AS a
FROM dual
UNION ALL
SELECT 1
FROM dual
UNION ALL
SELECT 2
FROM dual
UNION ALL
SELECT 3
FROM dual
UNION ALL
SELECT 4
FROM dual
UNION ALL
SELECT 5
FROM dual
UNION ALL
SELECT 6
FROM dual
UNION ALL
SELECT 7
FROM dual
UNION ALL
SELECT 8
FROM dual
UNION ALL
SELECT 9
FROM dual;

Créez une requête nommée "TodayMinus1KQ" (pour les dates antérieures à aujourd'hui) ; entrez manuellement la syntaxe suivante :

SELECT date() - (a.a + (10 * b.a) + (100 * c.a)) AS MyDate
FROM
  (SELECT *
   FROM ZeroThru9Q) AS a,

  (SELECT *
   FROM ZeroThru9Q) AS b,

  (SELECT *
   FROM ZeroThru9Q) AS c

Créez une requête nommée "TodayPlus1KQ" (pour les dates postérieures à aujourd'hui) ; entrez manuellement la syntaxe suivante :

SELECT date() + (a.a + (10 * b.a) + (100 * c.a)) AS MyDate
FROM
  (SELECT *
   FROM ZeroThru9Q) AS a,

  (SELECT *
   FROM ZeroThru9Q) AS b,

  (SELECT *
   FROM ZeroThru9Q) AS c;

Créez une requête d'union nommée "TodayPlusMinus1KQ" (pour les dates de +/- 1000 jours) :

SELECT MyDate
FROM TodayMinus1KQ
UNION
SELECT MyDate
FROM TodayPlus1KQ;

Maintenant vous pouvez utiliser la requête :

SELECT MyDate
FROM TodayPlusMinus1KQ
WHERE MyDate BETWEEN #05/01/2014# and #05/30/2014#