All geek questions in one place

Calculate time difference (hours of operation only) in minutes between two dates

I need to calculate the number of "active minutes" for an event in the database. Start time is well known.

The complication is that these active minutes should be taken into account only during the working day - from Monday to Friday from 9:00 to 18:30, excluding weekends and the (known) list of holidays

Start-up time or “current” may be outside of working hours, but only hours of operation are taken into account.

This is SQL Server 2005, so you can use T-SQL or a managed assembly.

+3
source share
5 answers

SQL

CREATE TABLE working_hours (start DATETIME, end DATETIME);

, ~ 250 .

(@event_start, @event_end), ,

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

.

, , / ,

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

:

  • , ( ).
  • , UNION ALL.
  • work_hours .

EDIT: MSSQL DATEDIFF (mi, start, end), .

+5

, TSQL SQL Server 2012.

SQL , :

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 Day
                          ,'2016-05-30' --Memorial Day
                          ,'2016-07-04' --Fourth of July
                          ,'2016-09-05' --Labor Day
                          ,'2016-11-11' --Veteran 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

, , 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

1 9

+2

, - , 08:30 18:00. , , . , . - , , :)

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
+1

, , . SQL Server , . , , . , ,

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]


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

, , -

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

! .

+1

:

  • (, , ), .
  • , ( ) . ( , )
  • , :
    • ""
    • .

( , , : ?)

Another method is to have a ticking time in the event, which checks each time whether the event should be counted at that time, and increases (in seconds or minutes) each time it finds itself active during the corresponding period. This will still require a supporting table and will be less audited (presumably).

0
source

Source: https://habr.com/ru/post/1755671/

More articles:


All Articles