Comment remplir un tableau avec une plage de dates?

j'ai besoin d'une table MySQL pour contenir toutes les DATES entre 2011-01-01 et 2011-12-31. J'ai créé une table avec les noms d'une colonne "_date", tapez la DATE.

avec quelle requête puis-je remplir la table avec toutes les dates désirées (au lieu d'avoir à les entrer à la main)?

27
demandé sur Leniel Maccaferri 2012-04-13 01:20:09

10 réponses

essayez ceci: 

DROP PROCEDURE IF EXISTS filldates;
DELIMITER |
CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)
BEGIN
  WHILE dateStart <= dateEnd DO
    INSERT INTO tablename (_date) VALUES (dateStart);
    SET dateStart = date_add(dateStart, INTERVAL 1 DAY);
  END WHILE;
END;
|
DELIMITER ;
CALL filldates('2011-01-01','2011-12-31');

voici le violon SQL pour jouer avec: http://sqlfiddle.com/#!2 / 65d13 / 1

MODIFIER (pour vérifier si la date existe déjà), comme demandé par Andrew Fox . 

CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)

BEGIN

DECLARE adate date;

    WHILE dateStart <= dateEnd DO

        SET adate = (SELECT mydate FROM MyDates WHERE mydate = dateStart);

        IF adate IS NULL THEN BEGIN

            INSERT INTO MyDates (mydate) VALUES (dateStart);

        END; END IF;

        SET dateStart = date_add(dateStart, INTERVAL 1 DAY);

    END WHILE;

END;//

voici le violon SQL pour jouer avec: http://sqlfiddle.com/#!2 / 66f86/1

41
répondu Leniel Maccaferri 2017-05-23 12:18:17

Je ne voulais pas que ma requête SQL nécessite des dépendances externes (nécessité d'avoir une table de calendrier, procédure pour remplir une table temporaire avec des dates, etc.) L'idée originale de cette requête est venue de http://jeffgarretson.wordpress.com/2012/05/04/generating-a-range-of-dates-in-mysql / que j'avais légèrement optimisé pour la clarté et la facilité d'utilisation. 

SELECT (CURDATE() - INTERVAL c.number DAY) AS date
FROM (SELECT singles + tens + hundreds number FROM 
( SELECT 0 singles
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
) singles JOIN 
(SELECT 0 tens
UNION ALL SELECT  10 UNION ALL SELECT  20 UNION ALL SELECT  30
UNION ALL SELECT  40 UNION ALL SELECT  50 UNION ALL SELECT  60
UNION ALL SELECT  70 UNION ALL SELECT  80 UNION ALL SELECT  90
) tens  JOIN 
(SELECT 0 hundreds
UNION ALL SELECT  100 UNION ALL SELECT  200 UNION ALL SELECT  300
UNION ALL SELECT  400 UNION ALL SELECT  500 UNION ALL SELECT  600
UNION ALL SELECT  700 UNION ALL SELECT  800 UNION ALL SELECT  900
) hundreds
ORDER BY number DESC) c  
WHERE c.number BETWEEN 0 and 364

il est simple d'optimiser et de mettre à l'échelle ce tableau pour d'autres utilisations. Vous pouvez débarrassez-vous facilement des tables des dizaines et des centaines Si vous n'avez besoin que d'une semaine de données.

Si vous avez besoin d'un plus grand ensemble de nombres, il est facile d'ajouter des milliers de table. Vous devez seulement copier et coller la table avec des centaines et Ajouter un zéro à 9 nombres.

14
répondu IvanD 2014-07-09 04:57:37

j'ai trouvé cette variante de pâte-et-aller travailler: 

DROP PROCEDURE IF EXISTS FillCalendar;
DROP TABLE IF EXISTS calendar;
CREATE TABLE IF NOT EXISTS calendar(calendar_date DATE NOT NULL PRIMARY KEY);

DELIMITER $$
    CREATE PROCEDURE FillCalendar(start_date DATE, end_date DATE)
    BEGIN
    DECLARE crt_date DATE;
    SET crt_date = start_date;
    WHILE crt_date <= end_date DO
        INSERT IGNORE INTO calendar VALUES(crt_date);
        SET crt_date = ADDDATE(crt_date, INTERVAL 1 DAY);
    END WHILE;
    END$$
DELIMITER ;

CALL FillCalendar('2013-01-01', '2013-01-03');
CALL FillCalendar('2013-01-01', '2013-01-07');
8
répondu Zon 2013-10-25 11:00:25

si vous avez une table avec un ensemble contigu assez grand d'ids vous pourriez utiliser - 

INSERT INTO tablename (_date)
SELECT '2011-01-01' + INTERVAL (id - 1) DAY
FROM some_table_with_lots_of_ids
WHERE id BETWEEN 1 AND 365

note: mais soyez conscient que cela pourrait vous attirer des ennuis pendant les années bissextiles (ayant 366 jours)

2
répondu nnichols 2012-09-12 02:24:08

si vous êtes dans une situation comme moi où procédures sont interdites , et votre utilisateur sql n'a pas les permissions pour insert, donc insert non autorisé , mais vous voulez générer une liste de dates dans une période spécifique , dites année courante pour faire une certaine agrégation, utilisez cette 

select * from 
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where gen_date between '2017-01-01' and '2017-12-31'
2
répondu computingfreak 2017-09-01 01:39:44

merci à IvanD. J'ai une meilleure solution qui vous permet de créer une table de calendrier spécifiée. Par exemple, si j'essaie de créer une table de 2014-04, il ressemble à ceci: 

SELECT (CURDATE() - INTERVAL c.number DAY) AS DATE
FROM 
(
    SELECT singles + tens + hundreds number FROM 
    ( 
        SELECT 0 singles
        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
    ) singles JOIN 
    (
        SELECT 0 tens
        UNION ALL SELECT  10 UNION ALL SELECT  20 UNION ALL SELECT  30
        UNION ALL SELECT  40 UNION ALL SELECT  50 UNION ALL SELECT  60
        UNION ALL SELECT  70 UNION ALL SELECT  80 UNION ALL SELECT  90
    ) tens  JOIN 
    (
        SELECT 0 hundreds
        UNION ALL SELECT  100 UNION ALL SELECT  200 UNION ALL SELECT  300
        UNION ALL SELECT  400 UNION ALL SELECT  500 UNION ALL SELECT  600
        UNION ALL SELECT  700 UNION ALL SELECT  800 UNION ALL SELECT  900
    ) hundreds
    ORDER BY number DESC
) c  
WHERE c.number BETWEEN 
DAYOFYEAR(NOW()) - DAYOFYEAR('2014-04-01')-  DAY(LAST_DAY('2014-04-01')) +1
AND 
DAYOFYEAR(NOW()) - DAYOFYEAR('2014-04-01')
1
répondu Zhili Yin 2015-08-18 10:38:06

cela peut être réalisé en PHP en utilisant un simple pour boucle. Il ya un couple de façons de le faire. Une façon serait de placer la date originale dans une variable et faire tourner la boucle pour chaque jour en ajoutant +1 jour sur chaque boucle, par exemple, vous commencerez le 01/01/2011 et ensuite la boucle ajoutera 0 la première fois, 1 jour le lendemain, suivi de 2 jours ainsi de suite et ainsi de suite à la variable $i. Vous pouvez ensuite imprimer le jours ou les ajouter à votre base de données. Dans ce cas $je représentez le compteur avec 0 étant le point de départ, < = 365 est le nombre de boucles que vous voulez passer qui est égal ou inférieur au nombre de jours et $i++ ajoute +1 à la variable $i sur chaque boucle.

date ('Y-m-d' convertit la date en AAAA-mm-JJ. L'utilisation d'Un y majuscule vous donne une année complète à 4 chiffres alors que l'utilisation d'un y minuscule vous donnera les 2 derniers chiffres de l'année. Vous voulez le garder dans cet ordre pour l'ajouter dans un champ de date dans mySQL.

strtotime ($originalDate divise la date en un horodatage Unix et le ."+ " .$I. "jour") ajoute essentiellement la valeur de $I en jours à la date.

enfin il y a la requête mysqli. $db représente la variable de connexion à la base de données, celle-ci devra être changée pour n'importe quelle variable que vous avez définie pour la connexion. Ceci est suivi par la requête réelle. Il suffit d'échanger la table de mot pour votre nom de table et la date avant les valeurs pour vous date nom de ligne et vous êtes prêt à aller.

exemple: 

<?php
for($i=0;$i<=365;$i++){ 
$originalDate = "01/01/2011";
$date = date('Y-m-d',strtotime($originalDate . "+".$i." day"));
mysqli_query($db, "INSERT INTO table (date)VALUES('$date')");
}

une autre façon d'y parvenir en utilisant la fonction for serait d'inclure les dates strtotime directement dans les actions for comme opposition aux variables de compteur, ce qui est un morceau de code encore plus court. Remplacer $i=0 (le point de départ du compteur) par le point de départ du jour, suivre cela avec le moins ou égal au point de fin de jour (le nombre de boucles) puis finalement avec votre plus +1 à la première déclaration placée dans une variable prête à l'emploi.

enfin, convertissez la date en format Y-m-d prêt à être placé dans la base de données et lancez la requête.

encore une fois, comme dans le premier exemple, cela peut être imprimé ou placé directement dans votre base de données.

exemple: 

<?php
for ($startdate = strtotime("2011-01-01"); $startdate <= strtotime("2011-12-31"); $startdate = strtotime("+1 day", $startdate)) {
$date= date("Y-m-d", $startdate);
mysqli_query($db, "INSERT INTO tracking (date)VALUES('$date')");
}

Je l'ai probablement fait paraître plus confus qu'il ne l'est, mais j'espère qu'il vous donnera au moins une idée sur la façon dont elle fonctionne.

1
répondu independent.guru 2016-08-08 12:06:39

j'ai récemment eu besoin de créer un tableau calendar_date comme ci-dessous: 

CREATE TABLE `calendar_date` (
    `date`    DATE NOT NULL      -- A calendar date.
    , `day`   SMALLINT NOT NULL  -- The day of the year for the date, 1-366.
    , `month` TINYINT NOT NULL   -- The month number, 1-12.
    , `year`  SMALLINT NOT NULL  -- The year.
    , PRIMARY KEY (`id`));

Je l'ai ensuite rempli avec toutes les dates possibles entre January 1, 2001 et December 31, 2100 (tous deux inclus) en utilisant la requête ci-dessous: 

INSERT INTO `calendar_date` (`date`
    , `day`
    , `month`
    , `year`)
SELECT
    DATE
    , INCREMENT + 1
    , MONTH(DATE)
    , YEAR(DATE)
FROM
    -- Generate all possible dates for every year from 2001 to 2100.
    (SELECT
        DATE_ADD(CONCAT(YEAR, '-01-01'), INTERVAL INCREMENT DAY) DATE
        , INCREMENT
    FROM
        (SELECT
            (UNITS + TENS + HUNDREDS) INCREMENT
        FROM
            (SELECT 0 UNITS UNION
            SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
            SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION
            SELECT 7 UNION SELECT 8 UNION SELECT 9) UNITS
        CROSS JOIN
            (SELECT 0 TENS UNION
            SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION
            SELECT 40 UNION SELECT 50 UNION SELECT 60 UNION
            SELECT 70 UNION SELECT 80 UNION SELECT 90) TENS
        CROSS JOIN
            (SELECT 0 HUNDREDS UNION
            SELECT 100 UNION SELECT 200 UNION SELECT 300 UNION
            SELECT 400 UNION SELECT 500 UNION SELECT 600 UNION
            SELECT 700 UNION SELECT 800 UNION SELECT 900) HUNDREDS
        ) INCREMENT
        -- For every year from 2001 to 2100, find the number of days in the year.
        , (SELECT
            YEAR
            , DAYOFYEAR(CONCAT(YEAR, '-12-31')) - DAYOFYEAR(CONCAT(YEAR, '-01-01')) + 1 DAYS
        FROM
            -- Generate years from 2001 to 2100.
            (SELECT
                (2000 + UNITS + TENS) YEAR
            FROM
                (SELECT 0 UNITS UNION
                SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
                SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION
                SELECT 7 UNION SELECT 8 UNION SELECT 9) UNITS
            CROSS JOIN
                (SELECT 0 TENS UNION
                SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION
                SELECT 40 UNION SELECT 50 UNION SELECT 60 UNION
                SELECT 70 UNION SELECT 80 UNION SELECT 90) TENS
            ) YEAR
        WHERE
            YEAR BETWEEN 2001 AND 2100
        ) YEAR
      WHERE
          INCREMENT BETWEEN 0 AND DAYS - 1
      ORDER BY
          YEAR
          , INCREMENT) DATE;

sur ma base de données MySQL locale, la requête INSERT a pris quelques secondes. Espérons que cela aide quelqu'un.

1
répondu manish 2018-01-31 09:45:37

inspiré par le grand nombre d'IvanD rejoindre je viens à ce: 

SELECT DATE_ADD('2015-10-21', INTERVAL c.number DAY)    AS DATE
FROM 
(
    SELECT singles + tens + hundreds+thousands number FROM 
    ( 
        SELECT 0 singles
        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
    ) singles JOIN 
    (
        SELECT 0 tens
        UNION ALL SELECT  10 UNION ALL SELECT  20 UNION ALL SELECT  30
        UNION ALL SELECT  40 UNION ALL SELECT  50 UNION ALL SELECT  60
        UNION ALL SELECT  70 UNION ALL SELECT  80 UNION ALL SELECT  90
    ) tens  JOIN 
    (
        SELECT 0 hundreds
        UNION ALL SELECT  100 UNION ALL SELECT  200 UNION ALL SELECT  300
        UNION ALL SELECT  400 UNION ALL SELECT  500 UNION ALL SELECT  600
        UNION ALL SELECT  700 UNION ALL SELECT  800 UNION ALL SELECT  900
    ) hundreds
     JOIN 
    (
        SELECT 0 thousands
        UNION ALL SELECT  1000 UNION ALL SELECT  2000 UNION ALL SELECT  3000
        UNION ALL SELECT  4000 UNION ALL SELECT  5000 UNION ALL SELECT  6000
        UNION ALL SELECT  7000 UNION ALL SELECT  8000 UNION ALL SELECT  9000
    ) thousands
    ORDER BY number DESC
) c  
WHERE c.number BETWEEN 
0 
AND
DATEDIFF('2016-10-08', '2015-10-21')
0
répondu ilgoom 2016-07-19 19:31:37 
INSERT INTO my_dates (\`_date\`) SELECT DATE_ADD('2011-01-01', INTERVAL @_tmp:=@_tmp+1 day) \`_date\`
FROM (SELECT @_tmp:=-1 d UNION SELECT 1 UNION SELECT 2 
      UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
      UNION SELECT 7 UNION SELECT 8  UNION SELECT 9) a /\*10^1\*/
JOIN (SELECT 0 UNION SELECT 1 UNION SELECT 2 
      UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
      UNION SELECT 7  UNION SELECT 8  UNION SELECT 9) b /\*10^2\*/
JOIN (SELECT 0 UNION SELECT 1 UNION SELECT 2
      UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
      UNION SELECT 7  UNION SELECT 8  UNION SELECT 9) c /\*10^3\*/
WHERE @_tmp+1 BETWEEN 0 AND DATEDIFF('2011-12-31', '2011-01-01');
0
répondu Dmytro L 2018-06-20 20:43:01