rango - time mysql ejemplo

¿Cómo llenar una tabla con un rango de fechas?

Question

Necesito una tabla de MySQL para contener TODAS LAS FECHAS entre 2011-01-01 y 2011-12-31. Creé una tabla con los nombres de una columna "_date", escriba DATE.

¿Con qué consulta puedo completar la tabla con todas las fechas deseadas (en lugar de tener que ingresarlas a mano)?

Answer 1

Encontré esta variante de pegar y usar funcionando:

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'');

Answer 2

Esto se puede lograr en PHP utilizando un simple for loop. Hay un par de maneras de hacerlo. Una forma sería colocar la fecha original en una variable y hacer que el ciclo se ejecute en ella todos los días añadiendo +1 día en cada bucle, por ejemplo, comenzará el 01/01/2011 y luego el ciclo agregue 0 la primera vez, 1 día el siguiente, seguido por 2 días, etc., y así sucesivamente hasta la variable $ i. Luego puede imprimir los días o agregarlos a su base de datos. En este caso $ i representaría el contador con 0 como el punto de partida, <= 365 es el número de ciclos que desea pasar que es igual o menor que el número de días y $ i ++ agrega +1 a la variable $ i en cada ciclo

date (''Ymd'' convierte la fecha en aaaa-mm-dd. Usar una Y mayúscula le da un año completo de 4 dígitos, mientras que usar una minúscula y le dará los últimos 2 dígitos del año. Desea mantenerlo en este Para agregarlo en un campo de fecha en mySQL.

strtotime ($ originalDate analiza la fecha en una marca de tiempo Unix y el. "+". $ i. "día") básicamente agrega el valor de $ i en días a la fecha.

Finalmente está la consulta mysqli. $ db representa la variable de conexión de la base de datos, esto tendrá que cambiarse a cualquier variable que haya establecido para la conexión. Esto es seguido por la consulta real. Simplemente cambie la tabla de palabras para el nombre de su tabla y la fecha anterior a VALUES para fechar el nombre de la fila y ya está listo para comenzar.

Lo siguiente es un ejemplo:

<?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'')");
}

Otra forma de lograr esto utilizando la función for sería incluir las fechas strtotime directamente en el para acciones como una oposición a las variables de contador, que es un fragmento de código aún más corto. Reemplace $ i = 0 (el punto del contador inicial) con el punto del día inicial, siga con el menor o igual que el día final (el número de bucles) y finalmente con su +1 más a la primera declaración colocada en un variable lista para usar

Finalmente, convierta la fecha en formato Ymd lista para ser colocada en la base de datos y ejecutar la consulta.

Nuevamente, como en el primer ejemplo, esto se puede imprimir o colocar directamente en su base de datos.

Lo siguiente es un ejemplo:

<?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'')");
}

Probablemente haya hecho que suene más confuso de lo que es, pero espero que al menos te dé una idea de cómo funciona.

Answer 3

Gracias a IvanD. Tengo una mejor solución que te permite crear una tabla de calendario específica. Por ejemplo, si intento crear una tabla de 2014-04, se ve así:

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'')

Answer 4

Inspirado por el gran número de miembros de IvanD, me uno a esto:

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'')

Answer 5

No quería que mi consulta SQL requiriera dependencias externas (necesitando tener una tabla de calendario, un procedimiento para rellenar una tabla temporal con fechas, etc.) La idea original para esta consulta provino de http://jeffgarretson.wordpress.com/2012/05/04/generating-a-range-of-dates-in-mysql/ que había optimizado ligeramente para mayor claridad y facilidad de uso.

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

Es simple optimizar y escalar esta tabla para otros usos. Puede deshacerse fácilmente de las tablas de decenas y centenas si solo necesita una semana de datos.

Si necesita un conjunto de números más grande, es fácil agregar una tabla de miles. Solo necesita copiar y pegar la tabla con cientos y agregar un cero a 9 números.

Answer 6

Prueba esto:

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'');

Aquí está el SQL Fiddle para jugar con él: http://sqlfiddle.com/#!2/65d13/1

EDITAR (para verificar si la fecha ya existe) como lo pidió 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;//

Aquí está el SQL Fiddle para jugar con él: http://sqlfiddle.com/#!2/66f86/1

Answer 7

Recientemente tuve la necesidad de crear una tabla calendar_date como la siguiente:

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`));

Luego lo llené con todas las fechas posibles entre January 1, 2001 y December 31, 2100 (ambos inclusive) usando la siguiente consulta:

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;

En mi base de datos MySQL local, la consulta INSERT tomó solo unos segundos. Espero que esto ayude a alguien.

Answer 8

Si tiene una tabla con un conjunto contiguo suficientemente grande de identificadores, podría usar:

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

Nota: pero tenga en cuenta que esto podría ocasionarle problemas durante los años bisiestos (tener 366 días)

Answer 9

si se encuentra en una situación como la mía donde los procedimientos están prohibidos , y su usuario sql no tiene permisos para insertar, por lo tanto, inserte no permitido , pero desea generar una lista de fechas en un período específico , por ejemplo, el año actual para hacer alguna agregación, use esto

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''

Answer 10

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'');