Seleccionar rango de fechas de a 1 minuto

[NSD]

Hola gente lo que quiero hacer (nose si se podra) es seleccionar entre dos fechas dadas, de tipo datetime, seleccionar todos los minutos que hay entre ellos, por supuesto que por programacion es mucho mas facil, pero necesito que sea por sql porque al resultado lo tengo que meter en un join contra otra tabla.


A ver si me explico mejor con un ejemplo, supongamos que tengo estas dos fechas:

Código MySQL:

SET @fInicio = "2014-07-04 12:00:00";
SET @fFinal  = "2014-07-04 12:10:00";
 

Lo que quiero hacer es un select que me devuelva:
2014-07-04 12:00:00
2014-07-04 12:01:00
2014-07-04 12:02:00
2014-07-04 12:03:00
2014-07-04 12:04:00
2014-07-04 12:05:00
2014-07-04 12:06:00
2014-07-04 12:07:00
2014-07-04 12:08:00
2014-07-04 12:09:00
2014-07-04 12:10:00

La forma mas rustica y artesanal es crear una tabla y poner ahi todos los posibles minutos del dia, pero quiza se pueda hacer solo en la consulta sin crear ninguna tabla

[quimfv]

...que meter en un join contra otra tabla.

Contra la otra tabla

Código MySQL:

...WHERE campo  BETWEEN '2014-07-04 12:00:00' AND '2014-07-04 12:10:00';
 

No te sirve?

[gnzsoloyo]

También podrías usar un

Código MySQL:

...
GROUP BY DATE(fecha), HOUR(fecha), MINUTE(fecha)
 

Ahora, bien, los minutos en los que no haya datos, los deberás crear programáticamente...
Usar una tabla con 1440 registros por cada día a considerar, me parece un despropósito innecesario.

[NSD]

Contra la otra tabla

Código MySQL:
Ver original
...WHERE campo BETWEEN '2014-07-04 12:00:00' AND '2014-07-04 12:10:00';


No te sirve?

Hola @quimfv gracias por responder pero no, no me sirve, necesito todos los minutos entre dos fechas como tabla para luego hacer el producto carteciano contra otra tabla.

@gnzsoloyo Gracias por la respuesta pero temo que tampoco me sirve, no estaba seguro de que con sql se pudiera, lo que habia pensado es crear una tabla con 1440 times y selecionar el concat de la fecha con el time asi solo tengo 1440 registros sino cada vez que varia la fecha tengo que repoblar la tabla y no tiene sentido.


Les explico cual es mi problema a resolver a ver si se les ocurre una forma de resolverlo diferente:

Tengo una tabla llamada agenda, que tiene estos campos:

id | hs_inicio (datetime) | hs_fin (datetime) | descripcion...

cada tarea en la agenda es un registro en esa tabla, lo que yo quiero saber, es entre dos fechas, que rangos de tiempos tengo libres tengo, esta es la consulta que uso ahora para saber que rangos tengo libres en el dia:

Código MySQL:

SET @dia = '2014-07-04';
    SET @minFecha = CONCAT(@dia, ' 00:00:00');
    SET @maxFecha = CONCAT(@dia, ' 23:59:00');
    SET @inicio = @minFecha;
    SET @fin = @maxFecha;
    SET @inicioSig = @minFecha;
    SET @lastId = 0;
    SET @mostrar = FALSE;
    SET @resto = 0;
    SELECT DISTINCT 
        DATE_FORMAT(IF(mostrar, inicio, resto_inicio), '%d/%m/%Y %H:%i:%s') AS inicio,
        DATE_FORMAT(IF(mostrar, fin, resto_fin), '%d/%m/%Y %H:%i:%s') AS fin,
        DATE_FORMAT(IF(mostrar, inicio, resto_inicio), '%Y-%m-%d %H:%i:%s') AS raw_inicio,
        DATE_FORMAT(IF(mostrar, fin, resto_fin), '%Y-%m-%d %H:%i:%s') AS raw_fin,
        TIMESTAMPDIFF(MINUTE, IF(mostrar, inicio, resto_inicio), IF(mostrar, fin, resto_fin)) AS minutos
    FROM 
    (SELECT
        IF(agd2.id = @lastId, (@mostrar:=TRUE), (@mostrar:=FALSE)) AS mostrar,
        IF(@mostrar, 0, (@lastId:=agd2.id)) AS tmp1,
        IF(@mostrar, @inicio, 
            IF
            (agd2.`hs_inicio` > @inicio,
                IF((@fin:=agd2.`hs_inicio`) AND (@inicioSig:=agd2.`hs_fin`), TRUE, FALSE)
            ,
                IF((@inicio:=agd2.`hs_fin`) AND (@fin:=agd2.`hs_fin`) AND (@inicioSig:=agd2.`hs_fin`), TRUE, FALSE)
            )
        
        ) AS inicio,
        @fin AS fin,
        IF(@mostrar, (@inicio:=@inicioSig), @inicioSig) AS resto_inicio,
        IF(@mostrar, (@fin:=@maxFecha), @maxFecha) AS resto_fin,
        IF(@mostrar, -1, (@resto:=@resto+1)) AS resto_nro
    FROM
        `agenda` agd
        JOIN  `agenda` AS agd2 -- Producto cartesiano
    WHERE        
        (
                (DATE_FORMAT(agd.`hs_inicio`, '%Y-%m-%d') = @dia)
            OR  (DATE_FORMAT(agd.`hs_fin`, '%Y-%m-%d') = @dia)
        )
        AND
        (
                (DATE_FORMAT(agd2.`hs_inicio`, '%Y-%m-%d') = @dia)
            OR  (DATE_FORMAT(agd2.`hs_fin`, '%Y-%m-%d') = @dia)
        )
    ORDER BY 
        agd2.`hs_inicio`
    ) 
    AS tTemp
    WHERE 
    (
            tTemp.mostrar = 1
        OR  tTemp.resto_nro = @resto
    )
    AND 
        inicio != fin
    ORDER BY inicio ASC
 

Pero tiene varios problemas, por ejemplo, si una tarea empieza a la misma hora exacta que termina otra genera registros basura.

Si una tarea esta superpuesta con otra, genera cualquier cosa.

Solo funciona bien si las tareas estan separadas entre si y no superpuestas, lo cual no siempre es el caso.

Actualmente lo parcheo con programacion (se que es off-topic pero lo posteo para que vean que es lo que le corrijo para que ande bien)

Editado: Código de programación no permitido en Foros de Bases de Dtos. Leer las normas del foro, por favor.

Se me ocurrio que si en otra tabla tengo todos los minutos del dia, luego podria hacer un join contra las tareas existentes para que solo me queden los minutos libres, y luego, volver a hacer un join contra si misma para buscar los rangos de minutos correlativos, pero nose si hay otra solucion

[helibertoarias]

Hola NSD, una forma rápida es utilizar una tally table o tabla de cuentas o números. Lo que haces es un join de tu tabla con la tally table con la condición el join. Esta tabla solo tiene una columna de valores incrementales y permite trabajarse muy bien. En

Editado: Autopromo no permitida en FDW

podras ver un ejemplo, es este caso se utiliza una fecha inicio y una fecha fin para generar el respectivo rango de días contenidos, lo puedes ajustar para en vez de incrementar días lo haga con minutos.

Espero te sirva, saludos.

[gnzsoloyo]

@helibertoarias: Si tienes soluciones a proponer, postea las soluciones, es decir, el código o procedimiento propuesto para el problema.
No postees enlaces a páginas propias, porque eso transgrede las Políticas de uso de Foros del Web, y serán eliminadas sin aviso.

[helibertoarias]

Disculpas @gnzsoloyo, se me paso. Aquí comparto el código. Basicamente genero los dias contenidos en un rango de fechas sin uso de while, el principio es el mismo y puede aplicarse para horas y o minutos. Espero les sirva.

Código SQL:

CREATE DATABASE Demo
GO
 
USE Demo 
GO
 
SET NOCOUNT ON 
 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.TallyTable') AND TYPE IN (N'U'))
    DROP TABLE dbo.TallyTable   
GO
 
CREATE TABLE dbo.TallyTable(ID INT NOT NULL, PRIMARY KEY(ID))
 
GO
 
DECLARE @I INT = 0 /* Puede iniciar en 0 ó 1 según se desee*/
DECLARE @IMax INT = 365*10 /*Para manejar operaciones de días con 10 años aproximadamente*/
 
WHILE @I<=@IMax
    BEGIN
        INSERT INTO dbo.TallyTable VALUES (@I)
        SET @I=@I+1
    END
    
USE Demo 
GO
 
 
 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.Hospedaje') AND TYPE IN (N'U'))
    DROP TABLE dbo.Hospedaje    
GO    
 
CREATE TABLE Hospedaje    (
        HospedajeId INT NOT NULL IDENTITY(1,1),
        Cliente VARCHAR(100) NOT NULL,
        FechaIngreso DATE NOT NULL,
        FechaEgreso DATE NOT NULL
     )
     
INSERT INTO dbo.Hospedaje( 
          Cliente ,
          FechaIngreso ,
          FechaEgreso
        )
    SELECT 'PEKERMAN', ' 20140504', '20140507'
    
USE Demo 
GO
     
SELECT *, DATEADD(DAY, tt.id, h.FechaIngreso) Dia
    FROM dbo.Hospedaje H 
        INNER JOIN dbo.TallyTable TT
            ON DATEADD(DAY, tt.id, h.FechaIngreso)<= h.FechaEgreso
    ORDER BY h.Cliente, tt.ID ASC
 

Los datos se verian algo así

[gnzsoloyo]

Interesante, ahora bien ten en cuenta que ese script es para SQL Server, y no para MySQL...
Si lo intentas correr en MySQL no funcionará porque no reconocerá muchas cosas, desde la declaración de variables, algunos tipos de dato, los terminadores de sentencias, funciones propias de cada DBMS...
Habría que hacer una migración completa... y probablemente no funcione como script, sino que haya que implementarlo por medio de un stored procedure (las estructuras de interación como WHILE, por ejemplo, no funcionan en scripts en MySQL, solo en SP y SF).

[NSD]

Hola @helibertoarias gracias por la respuesta, si bien la solucion es para sql server, la migracion que menciona @gnzsoloyo es posible, pero el problema es el metodo que usas, estas creando una tabla auxiliar y la estas poblando con miles de registros, ten encuenta que yo necesito minutos no dias, asi que necesitaría 1440 registros por cada uno de los tuyos lo cual terminara en una tabla gigante que no sera una solucion definitiva ya que pasados 10 años o el tiempo especificado dejara de andar.

Seguire pensando otra alternativa ya la publicare aqui si la consigo