Obtener una lista de fechas entre dos fechas
Usando funciones estándar de MySQL, ¿hay alguna manera de escribir una consulta que devuelva 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 se muestra arriba (espero que haya valores nulos en el lado derecho de algunas de estas combinaciones durante algunos días y manejaré esto ).
Usaría este procedimiento almacenado para generar los intervalos que necesita en la tabla temporal llamada time_intervals , luego UNIRME y agregar su tabla de datos con la tabla temporal time_intervals .
El procedimiento puede generar intervalos de todos los diferentes tipos que ves 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 al final de esta publicación , donde construí una función similar para SQL Server.
Para MSSQL puedes usar esto. Es MUY rápido.
Puede resumir esto en una función con valores de tabla o en un proceso almacenado y analizar 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
Edición 2021/01 (Dr. V): Me gustó esta solución y la hice funcionar para mySQL V8. Aquí está el código, envolviéndolo en un procedimiento:
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 ;
Puede utilizar las variables de usuario de MySQL de esta manera:
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 le agregas la primera vez que lo usas. Además, no puede usar "TENER secuencia_fecha" porque eso hace que la variable de usuario se incremente dos veces para cada fila.
Tuvimos un problema similar con los informes BIRT en el sentido de que queríamos informar aquellos días que no tenían datos. Como no había entradas para esas fechas, la solución más sencilla para nosotros fue crear una tabla simple que almacenara todas las fechas y usarla para obtener rangos o unirlas para obtener valores cero para esa fecha.
Tenemos un trabajo que se ejecuta todos los meses para garantizar que la tabla esté completa dentro de 5 años. La tabla se crea así:
create table all_dates (
dt date primary key
);
Sin duda, existen formas mágicas y complicadas de hacer esto con diferentes DBMS, pero siempre optamos por la solución más simple. Los requisitos de almacenamiento de 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 de los datos.
La otra opción (y la hemos usado antes) es garantizar que haya una entrada en la tabla para cada fecha. Barrimos la tabla periódicamente y no agregamos entradas para fechas y/u horas que no existían. Puede que esta no sea una opción en tu caso, depende de los datos almacenados.
Si realmente cree que es complicado mantener la all_datestabla llena, el camino a seguir es un procedimiento almacenado que devolverá un conjunto de datos que contiene esas fechas. Es casi seguro que esto será más lento ya que debe calcular el rango cada vez que se 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 gastos generales por seguridad, estimaría aproximadamente en aproximadamente 14M [365.000 * 16 * 2 * 1,2 = 14.016.000 bytes]), una tabla minúscula en el esquema de las cosas.
