mysql - por - generar rango de fechas sql

Obtener una lista de fechas entre dos fechas

Question

El uso de funciones estándar de MySQL es una forma de escribir una consulta que devolverá una lista de días entre dos fechas.

por ejemplo, dado 2009-01-01 y 2009-01-13 devolvería una tabla de una columna con los valores:

 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

Editar: Parece que no he sido claro. Quiero GENERAR esta lista. Tengo valores almacenados en la base de datos (por fecha y hora) pero quiero que se agreguen en una combinación externa izquierda a una lista de fechas como la anterior (estoy esperando nulo desde el lado derecho de algunos de esta unión durante algunos días y manejaré esto )

Answer 1

Bien, cómo encontrar las fechas entre dos fechas dadas en el servidor SQL se explica en http://ektaraval.blogspot.com/2010/09/writing-recursive-query-to-find-out-all.html

Answer 2

Cree un procedimiento almacenado que tome dos parámetros a_begin y a_end. Cree una tabla temporal dentro de la llamada t, declare una variable d, asigne a_begin a d, y ejecute un bucle WHILE ADDDATE d en t y llamando a la función ADDDATE para incrementar el valor d. Finalmente SELECT * FROM t .

Answer 3

Esta solución está trabajando con MySQL 5.0
Crea una tabla - mytable .
El esquema no es material. Lo que importa es el número de filas en él.
Por lo tanto, puede mantener solo una columna de tipo INT con 10 filas, valores - 1 a 10.

SQL:

set @tempDate=date(''2011-07-01'') - interval 1 day;
select
date(@tempDate := (date(@tempDate) + interval 1 day)) as theDate
from mytable x,mytable y
group by theDate
having theDate <= ''2011-07-31'';

Limitación: el número máximo de fechas devuelto por la consulta anterior será
(rows in mytable)*(rows in mytable) = 10*10 = 100.

Puede aumentar este rango cambiando la parte de formulario en sql:
de mytable x, mytable y, mytable z
Entonces, el rango debe ser 10*10*10 =1000 y así sucesivamente.

Answer 4

Estoy usando la Server version: 5.7.11-log MySQL Community Server (GPL)

Ahora resolveremos esto de una manera simple.

Creé una tabla llamada "fechable"

mysql> describe datetable;
+---------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| colid | int(11) | NO | PRI | NULL | |
| coldate | date | YES | | NULL | |
+---------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

ahora, nosotros veremos los registros insertados dentro.

mysql> select * from datetable;
+-------+------------+
| colid | coldate |
+-------+------------+
| 101 | 2015-01-01 |
| 102 | 2015-05-01 |
| 103 | 2016-01-01 |
+-------+------------+
3 rows in set (0.00 sec)

y aquí nuestra consulta para obtener registros dentro de dos fechas en lugar de esas fechas.

mysql> select * from datetable where coldate > ''2015-01-01'' and coldate < ''2016-01-01'';
+-------+------------+
| colid | coldate |
+-------+------------+
| 102 | 2015-05-01 |
+-------+------------+
1 row in set (0.00 sec)

Espero que esto ayude a muchos.

Answer 5

He estado peleando con esto por bastante tiempo. Dado que este es el primer golpe en Google cuando busqué la solución, permítanme publicar dónde he llegado hasta el momento.

SET @d := ''2011-09-01'';
SELECT @d AS d, cast( @d := DATE_ADD( @d , INTERVAL 1 DAY ) AS DATE ) AS new_d
FROM [yourTable]
WHERE @d <= ''2012-05-01'';

Reemplace [yourTable] con una tabla de su base de datos. El truco es que el número de filas en la tabla que seleccione debe ser> = el número de fechas que desea que se devuelva. Intenté usar el marcador de posición de tabla DUAL, pero solo devolvería una sola fila.

Answer 6

Necesitaba una lista con todos los meses entre 2 fechas para estadísticas. Las 2 fechas son el inicio y el final de una suscripción. Entonces la lista muestra todos los meses y la cantidad de suscripciones por mes.

MYSQL

CREATE PROCEDURE `get_amount_subscription_per_month`()
BEGIN
-- Select the ultimate start and enddate from subscribers
select @startdate := min(DATE_FORMAT(a.startdate, "%Y-%m-01")),
@enddate := max(DATE_FORMAT(a.enddate, "%Y-%m-01")) + interval 1 MONTH
from subscription a;
-- Tmp table with all months (dates), you can always format them with DATE_FORMAT)
DROP TABLE IF EXISTS tmp_months;
create temporary table tmp_months (
year_month date,
PRIMARY KEY (year_month)
);
set @tempDate=@startdate; #- interval 1 MONTH;
-- Insert every month in tmp table
WHILE @tempDate <= @enddate DO
insert into tmp_months (year_month) values (@tempDate);
set @tempDate = (date(@tempDate) + interval 1 MONTH);
END WHILE;
-- All months
select year_month from tmp_months;
-- If you want the amount of subscription per month else leave it out
select mnd.year_month, sum(subscription.amount) as subscription_amount
from tmp_months mnd
LEFT JOIN subscription ON mnd.year_month >= DATE_FORMAT(subscription.startdate, "%Y-%m-01") and mnd.year_month <= DATE_FORMAT(subscription.enddate, "%Y-%m-01")
GROUP BY mnd.year_month;
END

Answer 7

Para MSSQL puedes usar esto. Es MUY rápido.

Puede resumir esto en una función con valores de tabla o un proceso almacenado y analizar en las fechas de inicio y finalización como 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

Answer 8

Para el acceso (o cualquier lenguaje SQL)

1. Cree una tabla que tenga 2 campos, llamaremos a esta tabla tempRunDates :
   - fromDate desde fromDate y toDate
   - A continuación, inserte solo 1 registro, que tiene la fecha de inicio y la fecha de finalización.

2. Crear otra tabla: Time_Day_Ref
   --Importar una lista de fechas (hacer lista en excel es fácil) en esta tabla.
   --El nombre del campo en mi caso es Greg_Dt , para fecha gregoriana
   --He hecho mi lista desde enero 1 2009 hasta enero 1 2020.

3. Ejecute la consulta:

   SELECT Time_Day_Ref.GREG_DT
   FROM tempRunDates, Time_Day_Ref
   WHERE Time_Day_Ref.greg_dt>=tempRunDates.fromDate And greg_dt<=tempRunDates.toDate;

¡Fácil!

Answer 9

Por lo general, uno usaría una tabla de números auxiliares que generalmente mantiene para este propósito con alguna variación sobre esto:

SELECT *
FROM (
SELECT DATEADD(d, number - 1, ''2009-01-01'') AS dt
FROM Numbers
WHERE number BETWEEN 1 AND DATEDIFF(d, ''2009-01-01'', ''2009-01-13'') + 1
) AS DateRange
LEFT JOIN YourStuff
ON DateRange.dt = YourStuff.DateColumn

He visto variaciones con funciones con valores de tabla, etc.

También puede mantener una lista permanente de fechas. Tenemos eso en nuestro almacén de datos, así como una lista de las horas del día.

Answer 10

Puede usar las variables de usuario de MySQL como esta:

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 es -1 porque lo agrega la primera vez que lo usa. Además, no puede usar "HAVING date_sequence" porque eso hace que la variable de usuario incremente dos veces para cada fila.

Answer 11

Tomando prestada una idea de this respuesta, puede configurar una tabla con 0 a 9 y usarla para generar su lista de fechas.

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

Esto le permitirá generar una lista de hasta 1000 fechas. Si necesita aumentar de tamaño, puede agregar otra combinación cruzada a la consulta interna.

Answer 12

Tuvimos un problema similar con los informes de BIRT en el sentido de que queríamos informar sobre aquellos días que no tenían datos. Como no había entradas para esas fechas, la solución más fácil para nosotros fue crear una tabla simple que almacenara todas las fechas y la usara para obtener rangos o unir para obtener valores cero para esa fecha.

Tenemos un trabajo que se ejecuta todos los meses para garantizar que la tabla se rellene 5 años en el futuro. La tabla se crea así:

create table all_dates (
dt date primary key
);

Sin duda, hay maneras mágicas y complicadas de hacerlo con diferentes DBMS, pero siempre optamos por la solución más simple. Los requisitos de almacenamiento para la tabla son mínimos y hace que las consultas sean mucho más simples y portátiles. Este tipo de solución casi siempre es mejor desde el punto de vista del rendimiento, ya que no requiere cálculos por fila en los datos.

La otra opción (y hemos usado esto antes) es asegurar que haya una entrada en la tabla para cada fecha. Barrimos la tabla periódicamente y agregamos cero entradas para las fechas y / o tiempos que no existían. Esto puede no ser una opción en su caso, depende de los datos almacenados.

Si realmente cree que es una molestia mantener la tabla all_dates llena, un procedimiento almacenado es el camino a seguir, que devolverá un conjunto de datos que contiene esas fechas. Es casi seguro que será más lento, ya que debe calcular el rango cada vez que se lo llama en lugar de simplemente extraer datos precalculados de una tabla.

Pero, para ser honesto, podría completar la tabla durante 1000 años sin ningún problema grave de almacenamiento de datos: 365,000 fechas de 16 bytes (por ejemplo) más un índice que duplica la fecha más un 20% de sobrecarga por seguridad, aproximadamente lo calculo en aproximadamente 14M [365,000 * 16 * 2 * 1.2 = 14,016,000 bytes]), una tabla minúscula en el esquema de cosas.

Answer 13

Usamos esto en nuestro Sistema HRMS lo encontrará útil

SELECT CAST(DAYNAME(daydate) as CHAR) as dayname,daydate
FROM
(select CAST((date_add(''20110101'', interval H.i*100 + T.i*10 + U.i day) )as DATE) as daydate
from erp_integers as H
cross
join erp_integers as T
cross
join erp_integers as U
where date_add(''20110101'', interval H.i*100 + T.i*10 + U.i day ) <= ''20110228''
order
by daydate ASC
)Days

Answer 14

Utilizaría este procedimiento almacenado para generar los intervalos que necesita en la tabla temporal llamada time_intervals , luego JOIN y agregue su tabla de datos con la tabla temp time_intervals .

El procedimiento puede generar intervalos de todos los diferentes tipos que ve especificados en él:

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;

Escenario de datos de ejemplo similar en la parte inferior de esta publicación , donde construí una función similar para SQL Server.

Answer 15

Yo usaría algo similar a esto:

DECLARE @DATEFROM AS DATETIME
DECLARE @DATETO AS DATETIME
DECLARE @HOLDER TABLE(DATE DATETIME)
SET @DATEFROM = ''2010-08-10''
SET @DATETO = ''2010-09-11''
INSERT INTO
@HOLDER
(DATE)
VALUES
(@DATEFROM)
WHILE @DATEFROM < @DATETO
BEGIN
SELECT @DATEFROM = DATEADD(D, 1, @DATEFROM)
INSERT
INTO
@HOLDER
(DATE)
VALUES
(@DATEFROM)
END
SELECT
DATE
FROM
@HOLDER

Luego, la tabla de variables @HOLDER contiene todas las fechas incrementadas por día entre esas dos fechas, listas para unirse al contenido de su corazón.

Answer 16

CREATE FUNCTION [dbo].[_DATES]
(
@startDate DATETIME,
@endDate DATETIME
)
RETURNS
@DATES TABLE(
DATE1 DATETIME
)
AS
BEGIN
WHILE @startDate <= @endDate
BEGIN
INSERT INTO @DATES (DATE1)
SELECT @startDate
SELECT @startDate = DATEADD(d,1,@startDate)
END
RETURN
END

Answer 17

DELIMITER $$
CREATE PROCEDURE popula_calendario_controle()
BEGIN
DECLARE a INT Default 0;
DECLARE first_day_of_year DATE;
set first_day_of_year = CONCAT(DATE_FORMAT(curdate(),''%Y''),''-01-01'');
one_by_one: LOOP
IF dayofweek(adddate(first_day_of_year,a)) <> 1 THEN
INSERT INTO calendario.controle VALUES(null,150,adddate(first_day_of_year,a),adddate(first_day_of_year,a),1);
END IF;
SET a=a+1;
IF a=365 THEN
LEAVE one_by_one;
END IF;
END LOOP one_by_one;
END $$

este procedimiento insertará todas las fechas desde el comienzo del año hasta ahora, simplemente sustituya los días de "inicio" y "fin", ¡y ya está listo para comenzar!

Answer 18

select * from table_name where col_Date between ''2011/02/25'' AND DATEADD(s,-1,DATEADD(d,1,''2011/02/27''))

Aquí, primero agregue un día a la fecha final actual, será el 2011-02-28 00:00:00, luego restará un segundo para hacer la fecha final 2011-02-27 23:59:59. Al hacer esto, puede obtener todas las fechas entre los intervalos dados.

salida:
25/02/2011
26/02/2011 26/02/2011
27/02/2011