Obtenir une liste de dates entre deux dates

Question

En utilisant les fonctions standard de mysql, y a-t-il un moyen d'écrire une requête qui renvoie une liste de jours entre deux dates ?

Par exemple, si on lui donne 2009-01-01 et 2009-01-13, il retournera un tableau à une colonne avec les valeurs :

 2009-01-01 
 2009-01-02 
 2009-01-03
 2009-01-04 
 2009-01-05
 2009-01-06
 2009-01-07
 2009-01-08 
 2009-01-09
 2009-01-10
 2009-01-11
 2009-01-12
 2009-01-13

Edit : Il semble que je n'ai pas été clair. Je veux GENERER cette liste. J'ai des valeurs stockées dans la base de données (par date) mais je veux qu'elles soient agrégées par une jointure externe gauche à une liste de dates comme ci-dessus (je m'attends à ce que le côté droit de cette jointure soit nul pour certains jours et je vais gérer cela).

Answer 1

J'utiliserais cette procédure stockée pour générer les intervalles dont vous avez besoin dans la table temporaire nommée intervalles de temps puis JOIN et agréger votre table de données avec le temp intervalles de temps table.

La procédure peut générer des intervalles de tous les types différents que vous voyez spécifiés dans la procédure :

call make_intervals('2009-01-01 00:00:00','2009-01-10 00:00:00',1,'DAY')
.
select * from time_intervals  
.
interval_start      interval_end        
------------------- ------------------- 
2009-01-01 00:00:00 2009-01-01 23:59:59 
2009-01-02 00:00:00 2009-01-02 23:59:59 
2009-01-03 00:00:00 2009-01-03 23:59:59 
2009-01-04 00:00:00 2009-01-04 23:59:59 
2009-01-05 00:00:00 2009-01-05 23:59:59 
2009-01-06 00:00:00 2009-01-06 23:59:59 
2009-01-07 00:00:00 2009-01-07 23:59:59 
2009-01-08 00:00:00 2009-01-08 23:59:59 
2009-01-09 00:00:00 2009-01-09 23:59:59 
.
call make_intervals('2009-01-01 00:00:00','2009-01-01 02:00:00',10,'MINUTE')
. 
select * from time_intervals
.  
interval_start      interval_end        
------------------- ------------------- 
2009-01-01 00:00:00 2009-01-01 00:09:59 
2009-01-01 00:10:00 2009-01-01 00:19:59 
2009-01-01 00:20:00 2009-01-01 00:29:59 
2009-01-01 00:30:00 2009-01-01 00:39:59 
2009-01-01 00:40:00 2009-01-01 00:49:59 
2009-01-01 00:50:00 2009-01-01 00:59:59 
2009-01-01 01:00:00 2009-01-01 01:09:59 
2009-01-01 01:10:00 2009-01-01 01:19:59 
2009-01-01 01:20:00 2009-01-01 01:29:59 
2009-01-01 01:30:00 2009-01-01 01:39:59 
2009-01-01 01:40:00 2009-01-01 01:49:59 
2009-01-01 01:50:00 2009-01-01 01:59:59 
.
I specified an interval_start and interval_end so you can aggregate the 
data timestamps with a "between interval_start and interval_end" type of JOIN.
.
Code for the proc:
.
-- drop procedure make_intervals
.
CREATE PROCEDURE make_intervals(startdate timestamp, enddate timestamp, intval integer, unitval varchar(10))
BEGIN
-- *************************************************************************
-- Procedure: make_intervals()
--    Author: Ron Savage
--      Date: 02/03/2009
--
-- Description:
-- This procedure creates a temporary table named time_intervals with the
-- interval_start and interval_end fields specifed from the startdate and
-- enddate arguments, at intervals of intval (unitval) size.
-- *************************************************************************
   declare thisDate timestamp;
   declare nextDate timestamp;
   set thisDate = startdate;

   -- *************************************************************************
   -- Drop / create the temp table
   -- *************************************************************************
   drop temporary table if exists time_intervals;
   create temporary table if not exists time_intervals
      (
      interval_start timestamp,
      interval_end timestamp
      );

   -- *************************************************************************
   -- Loop through the startdate adding each intval interval until enddate
   -- *************************************************************************
   repeat
      select
         case unitval
            when 'MICROSECOND' then timestampadd(MICROSECOND, intval, thisDate)
            when 'SECOND'      then timestampadd(SECOND, intval, thisDate)
            when 'MINUTE'      then timestampadd(MINUTE, intval, thisDate)
            when 'HOUR'        then timestampadd(HOUR, intval, thisDate)
            when 'DAY'         then timestampadd(DAY, intval, thisDate)
            when 'WEEK'        then timestampadd(WEEK, intval, thisDate)
            when 'MONTH'       then timestampadd(MONTH, intval, thisDate)
            when 'QUARTER'     then timestampadd(QUARTER, intval, thisDate)
            when 'YEAR'        then timestampadd(YEAR, intval, thisDate)
         end into nextDate;

      insert into time_intervals select thisDate, timestampadd(MICROSECOND, -1, nextDate);
      set thisDate = nextDate;
   until thisDate >= enddate
   end repeat;

 END;

Un exemple similaire de scénario de données au bas de ce poste où j'ai construit une fonction similaire pour SQL Server.

Answer 2

Pour MSSQL, vous pouvez utiliser ceci. C'est TRES rapide.

Vous pouvez intégrer cette opération dans une fonction à valeur de tableau ou une procédure stockée et analyser les dates de début et de fin en tant que variables.

DECLARE @startDate DATETIME
DECLARE @endDate DATETIME

SET @startDate = '2011-01-01'
SET @endDate = '2011-01-31';

WITH dates(Date) AS 
(
    SELECT @startdate as Date
    UNION ALL
    SELECT DATEADD(d,1,[Date])
    FROM dates 
    WHERE DATE < @enddate
)

SELECT Date
FROM dates
OPTION (MAXRECURSION 0)
GO

Edit 2021/01 (Dr. V) : J'ai aimé cette solution et l'ai fait fonctionner pour mySQL V8. Voici le code, en l'intégrant dans une procédure :

DELIMITER //

CREATE PROCEDURE dates_between (IN from_date DATETIME,
                               IN to_date DATETIME) BEGIN
    WITH RECURSIVE dates(Date) AS
    (
        SELECT from_date as Date
        UNION ALL
        SELECT DATE_ADD(Date, INTERVAL 1 day) FROM dates WHERE Date < to_date
    )
    SELECT DATE(Date) FROM dates;
END//

DELIMITER ;

Answer 3

Nous avions un problème similaire avec les rapports BIRT, car nous voulions faire un rapport sur les jours où il n'y avait pas de données. Comme il n'y avait pas d'entrées pour ces dates, la solution la plus simple pour nous était de créer une simple table qui stockait toutes les dates et de l'utiliser pour obtenir des plages ou des jointures pour obtenir des valeurs nulles pour cette date.

Nous avons un travail qui s'exécute tous les mois pour s'assurer que la table est alimentée 5 ans dans le futur. La table est créée ainsi :

create table all_dates (
    dt date primary key
);

Il existe sans doute des façons magiques et délicates de procéder avec différents SGBD, mais nous optons toujours pour la solution la plus simple. Les besoins de stockage de la table sont minimes et cela rend les requêtes beaucoup plus simples et portables. Ce type de solution est presque toujours meilleur du point de vue des performances, car il ne nécessite pas de calculs par ligne sur les données.

L'autre option (et nous l'avons déjà utilisée) est de s'assurer qu'il y a une entrée dans le tableau pour chaque date. Nous avons balayé la table périodiquement et ajouté des entrées nulles pour les dates et/ou les heures qui n'existaient pas. Cela peut ne pas être une option dans votre cas, cela dépend des données stockées.

Si vous realmente pensent que c'est une corvée de garder les all_dates Si la table est remplie, il faut utiliser une procédure stockée qui renverra un ensemble de données contenant ces dates. Cette procédure sera certainement plus lente, car vous devez calculer l'intervalle à chaque fois que vous l'appelez, au lieu de simplement extraire des données précalculées d'une table.

Mais, pour être honnête, vous pourriez remplir la table pendant 1000 ans sans aucun problème sérieux de stockage de données - 365 000 dates de 16 octets (par exemple) plus un index dupliquant la date plus 20% de frais généraux pour la sécurité, j'estimerais approximativement à environ 14M [365 000 * 16 * 2 * 1,2 = 14 016 000 octets]), une table minuscule dans le schéma des choses.

Answer 4

Vous pouvez utiliser la fonction variables de l'utilisateur comme ça :

SET @num = -1;
SELECT DATE_ADD( '2009-01-01', interval @num := @num+1 day) AS date_sequence, 
your_table.* FROM your_table
WHERE your_table.other_column IS NOT NULL
HAVING DATE_ADD('2009-01-01', interval @num day) <= '2009-01-13'

@num est -1 parce que vous l'ajoutez la première fois que vous l'utilisez. De plus, vous ne pouvez pas utiliser "HAVING date_sequence" car cela incrémente la variable utilisateur deux fois pour chaque ligne.

Answer 5

Emprunter une idée à este vous pouvez créer un tableau de 0 à 9 et l'utiliser pour générer votre liste de dates.

CREATE TABLE num (i int);
INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

select adddate('2009-01-01', numlist.id) as `date` from
(SELECT n1.i + n10.i*10 + n100.i*100 AS id
   FROM num n1 cross join num as n10 cross join num as n100) as numlist
where adddate('2009-01-01', numlist.id) <= '2009-01-13';

Cela vous permettra de générer une liste de 1000 dates au maximum. Si vous avez besoin d'aller plus loin, vous pouvez ajouter une autre jointure croisée à la requête interne.