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