none
Rellenar o Replicar intervalos de hora RRS feed

  • Pregunta

  • Hola a todos,

    Quisiera pedirle ayuda con lo siguiente (ojala se entienda)

    Tengo la siguiente tabla:

    Start_Timestamp Duration End_Timestamp
    10-05-2019 10:13:53 10065 10-05-2019 13:01:38


    y como resultado debo generar lo siguiente:

    Start_Timestamp Duration End_Timestamp
    10-05-2019 10:13:53 967 10-05-2019 10:30:00
    10-05-2019 10:30:00 1800 10-05-2019 11:00:00
    10-05-2019 11:00:00 1800 10-05-2019 11:30:00
    10-05-2019 11:30:00 1800 10-05-2019 12:00:00
    10-05-2019 12:00:00 1800 10-05-2019 12:30:00
    10-05-2019 12:30:00 1800 10-05-2019 13:00:00
    10-05-2019 13:00:00 98 10-05-2019 13:01:38

    hay alguna forma en SQL de hacer esto para varios registros a la vez?

    De antemano agradezco la ayuda


    martes, 11 de junio de 2019 14:28

Respuestas

  • Hola Claudio Ferreira Reyes:

    Timestamp no es para tiempo, para esto disponemos de otro tipo de campos

    TimeStamp

    https://docs.microsoft.com/es-es/previous-versions/sql/sql-server-2005/ms182776(v=sql.90)

    Te he creado una solución que puede ser bastante cercana a lo que solicitas utilizando datetime.

    Lo primero te creas una función que te devuelve el rango de tiempo en los intervalos de 1800 segundos

    CREATE function dbo.fnRangeDTime_Offset_DateTime
    (@startDateTime DATETIME
    , @endDateTime   DATETIME
    
    )
    RETURNS TABLE
    	RETURN(
    	WITH cte(hora)
    		AS (SELECT t.v
    		    FROM  
    			    (VALUES(N'00:00:00')) AS t(v)),
    		c
    		AS (SELECT number
    		    FROM   
    			    master..spt_values
    		    WHERE  type = 'p  '
    				 AND number > 0
    				 and number < 49),
    		c1
    		AS (SELECT  --row_number() over (order by c.number) as num,
    		    CAST(DATEADD(second, (c.number * 1800), (CONVERT(DATETIME, (concat(N'2000-01-01T', cte.hora)), 126))) AS TIME) AS rows
    		    FROM 
    			    c
    				    CROSS JOIN cte),
    		tiempo
    		AS (SELECT CAST(c1.rows AS TIME) horaInicial
    			    , 1800 AS durationReg
    			    , CAST(DATEADD(second, 1800, CAST(c1.rows AS DATETIME)) AS TIME) AS horaFinal
    		    FROM   
    			    c1
    		    WHERE      CAST(c1.rows AS DATETIME) > DATEADD(SECOND,-1800,CAST(CAST(@startDateTime AS TIME) AS DATETIME))
    				 AND CAST(c1.rows AS DATETIME) < CAST(CAST(@endDateTime AS TIME) AS DATETIME))
    		SELECT t.horaInicial
    			, t.durationReg
    			, t.horaFinal
    		FROM   
    			tiempo t);

    Una vez esta creada la función. Preparación del escenario

    CREATE TABLE tiempos
    (start_timestamp DATETIME
    , duration        INT
    , end_Timestamp   DATETIME
    );
    GO
    INSERT INTO tiempos ( 
      start_timestamp
    , duration
    , end_Timestamp
    )
    values
    ('20190510 10:13:53',10065,'20190510 13:01:38'),
    ('20190510 15:18:53',10065,'20190510 21:17:41');

    Consumo del mismo

    WITH CTE
    	AS (SELECT ROW_NUMBER() OVER(PARTITION BY T.START_tIMESTAMP 
    			 ORDER BY T.START_TIMESTAMP
    				   , V.HORAINICIAL) AS FILA
    		    , T.start_timestamp
    		    , T.duration
    		    , T.end_Timestamp
    		    , v.horaInicial
    		    , v.durationReg
    		    , v.horaFinal
    	    FROM   
    		    TIEMPOS T
    			    CROSS APPLY 
    /* utilizamos la función para que nos devuelva los rangos */
    dbo.fnRangeDTime_Offset_DateTime(T.start_timestamp, T.end_Timestamp) AS v)
    	SELECT 
    /* escogemos de lo que tiene el cte, bien sea la hora inicial o la hora del rango */
    CASE WHEN C.FILA = 1
    					THEN CAST(start_timestamp AS TIME)
    				 ELSE horaInicial
    			 END AS HORAINI
    		    , DATEDIFF(SECOND,CASE WHEN C.FILA = 1   THEN CAST(C.start_timestamp AS TIME) ELSE C.horaInicial END,
    		     CASE WHEN C.horaFinal < CAST(C.END_TIMESTAMP AS TIME)
    					THEN C.horaFinal
    				 ELSE CAST(C.END_TIMESTAMP AS TIME) END) AS DESP
    		    , CASE WHEN C.horaFinal < CAST(C.END_TIMESTAMP AS TIME)
    					THEN C.horaFinal
    				 ELSE CAST(C.END_TIMESTAMP AS TIME)
    			 END AS HORAFIN
    		 
    	    FROM   
    		    CTE C

    Salida

    Cte

    https://javifer2.blogspot.com/2019/01/with-cte-tablas-de-expresion-comun-1_1.html

    Espero te ayude

    miércoles, 12 de junio de 2019 5:00

Todas las respuestas

  • Utiliza esta consulta:

    creas primero tu tabla con los dos campos de fecha inicial y fecha final, luego vas a nueva consulta:

    ALTER TABLE tbl_mitabla
    ADD dif AS DATEDIFF([second],start_time,end_time)
    la diferencia es es segundo, si lo quieres en horas solo coloca [hour]

    Espero que te sirva. Y si es así márcala como respuesta!

    martes, 11 de junio de 2019 15:56
  • Hola, gracias por la ayuda...

    como comente solo tengo la parte inicial (estos son los datos con los que cuento)

    Start_Timestamp Duration End_Timestamp
    10-05-2019 10:13:53 10065 10-05-2019 13:01:38

    y la idea es llegar a generar o crear mediante una consulta la separación de los intervalos, es decir, debo generar cada linea por separación de 30 minutos (1800 segundos) con su respectivo inicio y fin, disculpa si no me explique bien y también agradezco mucho la ayuda brindada.

    martes, 11 de junio de 2019 16:58
  • Ya creo que te entendí. He realizado una consulta de introducir valores con esas intervalos que dices mediante la siguiente sentencia, pero para eso debes agregarle a la tabla un campo id auto numérico incrementable de 1 en 1:

    DECLARE @nroLine As int          --Número de líneas a introducir
    DECLARE @lastTimeEnd as datetime --Último registro de tiempo final
    DECLARE @lastTimeBeg as datetime --Último registro de tiempo inicial
    DECLARE @duration as int         --Duración
    DECLARE @count as int            --Contador hasta llegar al límite
    DECLARE @endTime as datetime     --Calculo del tiempo final
    
    
    SET @nroLine=10    --Le agregué como ejemplo 10 líneas más a la tabla
    SET @duration=1800 --Le agregue 1800s como ejemplo
    SET @lastTimeEnd=(SELECT End_Timestamp FROM tbl_time WHERE id=(SELECT MAX(id) FROM tbl_time))
    SET @lastTimeBeg=(SELECT Start_Timestamp FROM tbl_time WHERE id=(SELECT MAX(id) FROM tbl_time))
    SET @endTime=DATEADD([SECOND],@duration,@lastTimeBeg)
    
    SET @count=1
    WHILE @count<@nroLine
    	BEGIN		
    		INSERT INTO tbl_time (Start_Timestamp,Duration,End_Timestamp) VALUES(@lastTimeEnd,@duration,@endTime)
    		
    		SET @lastTimeEnd=(SELECT End_Timestamp FROM tbl_time WHERE id=(SELECT MAX(id) FROM tbl_time))
    		SET @endTime=DATEADD([SECOND],@duration,@lastTimeEnd)
    		SET @count=@count+1
    	END

    Tomando como datos iniciales de tu tabla me genero o sigueinte:

    • Propuesto como respuesta cerm88 miércoles, 12 de junio de 2019 22:54
    miércoles, 12 de junio de 2019 3:59
  • Hola Claudio Ferreira Reyes:

    Timestamp no es para tiempo, para esto disponemos de otro tipo de campos

    TimeStamp

    https://docs.microsoft.com/es-es/previous-versions/sql/sql-server-2005/ms182776(v=sql.90)

    Te he creado una solución que puede ser bastante cercana a lo que solicitas utilizando datetime.

    Lo primero te creas una función que te devuelve el rango de tiempo en los intervalos de 1800 segundos

    CREATE function dbo.fnRangeDTime_Offset_DateTime
    (@startDateTime DATETIME
    , @endDateTime   DATETIME
    
    )
    RETURNS TABLE
    	RETURN(
    	WITH cte(hora)
    		AS (SELECT t.v
    		    FROM  
    			    (VALUES(N'00:00:00')) AS t(v)),
    		c
    		AS (SELECT number
    		    FROM   
    			    master..spt_values
    		    WHERE  type = 'p  '
    				 AND number > 0
    				 and number < 49),
    		c1
    		AS (SELECT  --row_number() over (order by c.number) as num,
    		    CAST(DATEADD(second, (c.number * 1800), (CONVERT(DATETIME, (concat(N'2000-01-01T', cte.hora)), 126))) AS TIME) AS rows
    		    FROM 
    			    c
    				    CROSS JOIN cte),
    		tiempo
    		AS (SELECT CAST(c1.rows AS TIME) horaInicial
    			    , 1800 AS durationReg
    			    , CAST(DATEADD(second, 1800, CAST(c1.rows AS DATETIME)) AS TIME) AS horaFinal
    		    FROM   
    			    c1
    		    WHERE      CAST(c1.rows AS DATETIME) > DATEADD(SECOND,-1800,CAST(CAST(@startDateTime AS TIME) AS DATETIME))
    				 AND CAST(c1.rows AS DATETIME) < CAST(CAST(@endDateTime AS TIME) AS DATETIME))
    		SELECT t.horaInicial
    			, t.durationReg
    			, t.horaFinal
    		FROM   
    			tiempo t);

    Una vez esta creada la función. Preparación del escenario

    CREATE TABLE tiempos
    (start_timestamp DATETIME
    , duration        INT
    , end_Timestamp   DATETIME
    );
    GO
    INSERT INTO tiempos ( 
      start_timestamp
    , duration
    , end_Timestamp
    )
    values
    ('20190510 10:13:53',10065,'20190510 13:01:38'),
    ('20190510 15:18:53',10065,'20190510 21:17:41');

    Consumo del mismo

    WITH CTE
    	AS (SELECT ROW_NUMBER() OVER(PARTITION BY T.START_tIMESTAMP 
    			 ORDER BY T.START_TIMESTAMP
    				   , V.HORAINICIAL) AS FILA
    		    , T.start_timestamp
    		    , T.duration
    		    , T.end_Timestamp
    		    , v.horaInicial
    		    , v.durationReg
    		    , v.horaFinal
    	    FROM   
    		    TIEMPOS T
    			    CROSS APPLY 
    /* utilizamos la función para que nos devuelva los rangos */
    dbo.fnRangeDTime_Offset_DateTime(T.start_timestamp, T.end_Timestamp) AS v)
    	SELECT 
    /* escogemos de lo que tiene el cte, bien sea la hora inicial o la hora del rango */
    CASE WHEN C.FILA = 1
    					THEN CAST(start_timestamp AS TIME)
    				 ELSE horaInicial
    			 END AS HORAINI
    		    , DATEDIFF(SECOND,CASE WHEN C.FILA = 1   THEN CAST(C.start_timestamp AS TIME) ELSE C.horaInicial END,
    		     CASE WHEN C.horaFinal < CAST(C.END_TIMESTAMP AS TIME)
    					THEN C.horaFinal
    				 ELSE CAST(C.END_TIMESTAMP AS TIME) END) AS DESP
    		    , CASE WHEN C.horaFinal < CAST(C.END_TIMESTAMP AS TIME)
    					THEN C.horaFinal
    				 ELSE CAST(C.END_TIMESTAMP AS TIME)
    			 END AS HORAFIN
    		 
    	    FROM   
    		    CTE C

    Salida

    Cte

    https://javifer2.blogspot.com/2019/01/with-cte-tablas-de-expresion-comun-1_1.html

    Espero te ayude

    miércoles, 12 de junio de 2019 5:00
  • Hola Claudio Ferreira Reyes:

    Timestamp no es para tiempo, para esto disponemos de otro tipo de campos

    TimeStamp

    Te he creado una solución que puede ser bastante cercana a lo que solicitas utilizando datetime.

    Lo primero te creas una función que te devuelve el rango de tiempo en los intervalos de 1800 segundos

    CREATE function dbo.fnRangeDTime_Offset_DateTime
    (@startDateTime DATETIME
    , @endDateTime   DATETIME
    
    )
    RETURNS TABLE
    	RETURN(
    	WITH cte(hora)
    		AS (SELECT t.v
    		    FROM  
    			    (VALUES(N'00:00:00')) AS t(v)),
    		c
    		AS (SELECT number
    		    FROM   
    			    master..spt_values
    		    WHERE  type = 'p  '
    				 AND number > 0
    				 and number < 49),
    		c1
    		AS (SELECT  --row_number() over (order by c.number) as num,
    		    CAST(DATEADD(second, (c.number * 1800), (CONVERT(DATETIME, (concat(N'2000-01-01T', cte.hora)), 126))) AS TIME) AS rows
    		    FROM 
    			    c
    				    CROSS JOIN cte),
    		tiempo
    		AS (SELECT CAST(c1.rows AS TIME) horaInicial
    			    , 1800 AS durationReg
    			    , CAST(DATEADD(second, 1800, CAST(c1.rows AS DATETIME)) AS TIME) AS horaFinal
    		    FROM   
    			    c1
    		    WHERE      CAST(c1.rows AS DATETIME) > DATEADD(SECOND,-1800,CAST(CAST(@startDateTime AS TIME) AS DATETIME))
    				 AND CAST(c1.rows AS DATETIME) < CAST(CAST(@endDateTime AS TIME) AS DATETIME))
    		SELECT t.horaInicial
    			, t.durationReg
    			, t.horaFinal
    		FROM   
    			tiempo t);

    Una vez esta creada la función. Preparación del escenario

    CREATE TABLE tiempos
    (start_timestamp DATETIME
    , duration        INT
    , end_Timestamp   DATETIME
    );
    GO
    INSERT INTO tiempos ( 
      start_timestamp
    , duration
    , end_Timestamp
    )
    values
    ('20190510 10:13:53',10065,'20190510 13:01:38'),
    ('20190510 15:18:53',10065,'20190510 21:17:41');

    Consumo del mismo

    WITH CTE
    	AS (SELECT ROW_NUMBER() OVER(PARTITION BY T.START_tIMESTAMP 
    			 ORDER BY T.START_TIMESTAMP
    				   , V.HORAINICIAL) AS FILA
    		    , T.start_timestamp
    		    , T.duration
    		    , T.end_Timestamp
    		    , v.horaInicial
    		    , v.durationReg
    		    , v.horaFinal
    	    FROM   
    		    TIEMPOS T
    			    CROSS APPLY 
    /* utilizamos la función para que nos devuelva los rangos */
    dbo.fnRangeDTime_Offset_DateTime(T.start_timestamp, T.end_Timestamp) AS v)
    	SELECT 
    /* escogemos de lo que tiene el cte, bien sea la hora inicial o la hora del rango */
    CASE WHEN C.FILA = 1
    					THEN CAST(start_timestamp AS TIME)
    				 ELSE horaInicial
    			 END AS HORAINI
    		    , DATEDIFF(SECOND,CASE WHEN C.FILA = 1   THEN CAST(C.start_timestamp AS TIME) ELSE C.horaInicial END,
    		     CASE WHEN C.horaFinal < CAST(C.END_TIMESTAMP AS TIME)
    					THEN C.horaFinal
    				 ELSE CAST(C.END_TIMESTAMP AS TIME) END) AS DESP
    		    , CASE WHEN C.horaFinal < CAST(C.END_TIMESTAMP AS TIME)
    					THEN C.horaFinal
    				 ELSE CAST(C.END_TIMESTAMP AS TIME)
    			 END AS HORAFIN
    		 
    	    FROM   
    		    CTE C

    Salida

    Cte

    Espero te ayude


    Muchas muchas gracias Javi, me ha servido mucho.
    miércoles, 12 de junio de 2019 19:05
  • De nada
    miércoles, 12 de junio de 2019 19:14