sql - sacar - Calcule la diferencia de tiempo(solo horas de trabajo) en minutos entre dos fechas

operaciones con fechas sql

Question

Necesito calcular el número de "minutos activos" para un evento dentro de una base de datos. La hora de inicio es bien conocida.

La complicación es que estos minutos activos solo se deben contar durante un día laborable, de lunes a viernes de 9:00 a.m. a 6:30 p.m., excluidos los fines de semana y la lista (conocida) de días festivos.

La hora de inicio o "actual" puede estar fuera de las horas de trabajo, pero aún solo se cuentan las horas de trabajo.

Este es SQL Server 2005, por lo que se podría usar T-SQL o un ensamblado administrado.

Answer 1

Globalmente, necesitarías:

1. Una forma de capturar el tiempo de finalización del evento (posiblemente mediante notificación, o lo que sea que inició el evento en primer lugar), y una tabla para registrar esta hora de inicio y finalización.
2. Una tabla auxiliar que contiene todos los períodos (inicio y final) para contar. (Y luego necesita un código de soporte para mantener esta tabla actualizada en el futuro)
3. Un procedimiento almacenado que:
   • iterar sobre esta tabla auxiliar y encontrar los períodos ''activos''
   • calcule los minutos dentro de cada período activo.

(Tenga en cuenta que esto supone que el evento puede durar varios días: ¿es realmente probable?)

Un método diferente sería tener un reloj de tiempo dentro del evento, que comprueba cada vez si el evento debe contarse en ese momento, y aumenta (en segundos o minutos) cada vez que descubre que está activo durante el período relevante. Esto todavía requeriría la tabla auxiliar y sería menos auditable (presumiblemente).

Answer 2

Si quieres hacerlo SQL puro, aquí hay un enfoque

CREATE TABLE working_hours (start DATETIME, end DATETIME);

Ahora rellene la tabla de horas de trabajo con períodos contables, ~ 250 filas por año.

Si tiene un evento (@event_start, @event_end) que comenzará horas y finalizará horas, entonces consulta simple

SELECT SUM(end-start) as duration
FROM working_hours
WHERE start >= @event_start AND end <= @event_end

Será suficiente.

Si, por el contrario, el evento comienza y / o finaliza durante las horas de trabajo, la consulta es más complicada

SELECT SUM(duration)
FROM
(
SELECT SUM(end-start) as duration
FROM working_hours
WHERE start >= @event_start AND end <= @event_end
UNION ALL
SELECT end-@event_start
FROM working_hours
WHERE @event_start between start AND end
UNION ALL
SELECT @event_end - start
FROM working_hours
WHERE @event_end between start AND end
) AS u

Notas:

• lo anterior es una consulta no probada, dependiendo de su RDBMS puede que necesite funciones de fecha / hora para agregar y restar fecha y hora (y dependiendo de las funciones utilizadas, la consulta anterior puede funcionar con precisión de tiempo).
• la consulta se puede volver a escribir para no utilizar el UNION ALL.
• la tabla working_hours se puede usar para otras cosas en el sistema y permite la máxima flexibilidad

EDITAR: en MSSQL puede usar DATEDIFF (mi, inicio, final) para obtener la cantidad de minutos para cada resta anterior.

Answer 3

Vine aquí buscando una respuesta a una pregunta muy similar: necesitaba obtener los minutos entre 2 fechas, excluidos los fines de semana y las horas fuera de 08:30 y 18:00. Después de un poco de piratear, creo que lo tengo ordenado. A continuación es cómo lo hice. los pensamientos son bienvenidos, quién sabe, tal vez algún día me suscriba a este sitio :)

create function WorkingMinutesBetweenDates(@dteStart datetime, @dteEnd datetime)
returns int
as
begin
declare @minutes int
set @minutes = 0
while @dteEnd>=@dteStart
begin
if datename(weekday,@dteStart) <>''Saturday'' and datename(weekday,@dteStart)<>''Sunday''
and (datepart(hour,@dteStart) >=8 and datepart(minute,@dteStart)>=30 )
and (datepart(hour,@dteStart) <=17)
begin
set @minutes = @minutes + 1
end
set @dteStart = dateadd(minute,1,@dteStart)
end
return @minutes
end
go

Answer 4

Empecé a trabajar con lo que publicó Sinrazón y fue un gran comienzo. Probé esto es SQL Server y encontré que no todo el tiempo estaba siendo capturado. Creo que el problema fue principalmente cuando el evento comenzó y terminó el mismo día. Esta solución parece estar funcionando lo suficientemente bien para mí

CREATE TABLE [dbo].[working_hours](
[wh_id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[wh_starttime] [datetime] NULL,
[wh_endtime] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[wh_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

IR

CREATE FUNCTION [dbo].[udFWorkingMinutes]
(
@startdate DATETIME
,@enddate DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @WorkingHours INT
SET @WorkingHours =
(SELECT
CASE WHEN COALESCE(SUM(duration),0) < 0 THEN 0 ELSE SUM(Duration)
END AS Minutes
FROM
(
--All whole days
SELECT ISNULL(DATEDIFF(mi,wh_starttime,wh_endtime),0) AS Duration
FROM working_hours
WHERE wh_starttime >= @startdate AND wh_endtime <= @enddate
UNION ALL
--All partial days where event start after office hours and finish after office hours
SELECT ISNULL(DATEDIFF(mi,@startdate,wh_endtime),0) AS Duration
FROM working_hours
WHERE @startdate > wh_starttime AND @enddate >= wh_endtime
AND (CAST(wh_starttime AS DATE) = CAST(@startdate AS DATE))
AND @startdate < wh_endtime
UNION ALL
--All partial days where event starts before office hours and ends before day end
SELECT ISNULL(DATEDIFF(mi,wh_starttime,@enddate),0) AS Duration
FROM working_hours
WHERE @enddate < wh_endtime
AND @enddate >= wh_starttime
AND @startdate <= wh_starttime
AND (CAST(wh_endtime AS DATE) = CAST(@enddate AS DATE))
UNION ALL
--Get partial day where intraday event
SELECT ISNULL(DATEDIFF(mi,@startdate,@enddate),0) AS Duration
FROM working_hours
WHERE @startdate > wh_starttime AND @enddate < wh_endtime
AND (CAST(@startdate AS DATE)= CAST(wh_starttime AS DATE))
AND (CAST(@enddate AS DATE)= CAST(wh_endtime AS DATE))
) AS u)
RETURN @WorkingHours
END
GO

Todo lo que queda por hacer es poblar la tabla de horas de trabajo con algo así como

;WITH cte AS (
SELECT CASE WHEN DATEPART(Day,''2014-01-01 9:00:00 AM'') = 1 THEN ''2014-01-01 9:00:00 AM''
ELSE DATEADD(Day,DATEDIFF(Day,0,''2014-01-01 9:00:00 AM'')+1,0) END AS myStartDate,
CASE WHEN DATEPART(Day,''2014-01-01 5:00:00 PM'') = 1 THEN ''2014-01-01 5:00:00 PM''
ELSE DATEADD(Day,DATEDIFF(Day,0,''2014-01-01 5:00:00 PM'')+1,0) END AS myEndDate
UNION ALL
SELECT DATEADD(Day,1,myStartDate), DATEADD(Day,1,myEndDate)
FROM cte
WHERE DATEADD(Day,1,myStartDate) <= ''2015-01-01''
)
INSERT INTO working_hours
SELECT myStartDate, myEndDate
FROM cte
OPTION (MAXRECURSION 0)
delete from working_hours where datename(dw,wh_starttime) IN (''Saturday'', ''Sunday'')
--delete public holidays
delete from working_hours where CAST(wh_starttime AS DATE) = ''2014-01-01''

Mi primera publicación! Se misericordioso.

Answer 5

Usando el excelente punto de partida de la sinrazón, aquí hay una implementación de TSQL para SQL Server 2012.

Este primer SQL rellena una tabla con nuestros días y horas de trabajo excluyendo fines de semana y días festivos:

declare @dteStart date
declare @dteEnd date
declare @dtStart smalldatetime
declare @dtEnd smalldatetime
Select @dteStart = ''2016-01-01''
Select @dteEnd = ''2016-12-31''
CREATE TABLE working_hours (starttime SMALLDATETIME, endtime SMALLDATETIME);
while @dteStart <= @dteEnd
BEGIN
IF datename(WEEKDAY, @dteStart) <> ''Saturday''
AND DATENAME(WEEKDAY, @dteStart) <> ''Sunday''
AND @dteStart not in (''2016-01-01'' --New Years
,''2016-01-18'' --MLK Jr
,''2016-02-15'' --President''s Day
,''2016-05-30'' --Memorial Day
,''2016-07-04'' --Fourth of July
,''2016-09-05'' --Labor Day
,''2016-11-11'' --Veteran''s Day
,''2016-11-24'' --Thanksgiving
,''2016-11-25'' --Day after Thanksgiving
,''2016-12-26'' --Christmas
)
BEGIN
select @dtStart = SMALLDATETIMEFROMPARTS(year(@dteStart),month(@dteStart),day(@dteStart),8,0) --8:00am
select @dtEnd = SMALLDATETIMEFROMPARTS(year(@dteStart),month(@dteStart),day(@dteStart),17,0) --5:00pm
insert into working_hours values (@dtStart,@dtEnd)
END
Select @dteStart = DATEADD(day,1,@dteStart)
END

Ahora aquí está la lógica que funcionó para devolver los minutos como INT:

declare @event_start datetime2
declare @event_end datetime2
select @event_start = ''2016-01-04 8:00''
select @event_end = ''2016-01-06 16:59''
SELECT SUM(duration) as minutes
FROM
(
SELECT DATEDIFF(mi,@event_start,@event_end) as duration
FROM working_hours
WHERE @event_start >= starttime
AND @event_start <= endtime
AND @event_end <= endtime
UNION ALL
SELECT DATEDIFF(mi,@event_start,endtime)
FROM working_hours
WHERE @event_start >= starttime
AND @event_start <= endtime
AND @event_end > endtime
UNION ALL
SELECT DATEDIFF(mi,starttime,@event_end)
FROM working_hours
WHERE @event_end >= starttime
AND @event_end <= endtime
AND @event_start < starttime
UNION ALL
SELECT SUM(DATEDIFF(mi,starttime,endtime))
FROM working_hours
WHERE starttime > @event_start
AND endtime < @event_end
) AS u

Esto devuelve correctamente 1 minuto antes de tres días de trabajo de 9 horas