Coding Help : subtract business hours from a timerange?

[Kenji4861]

Say I have a function like

removeBusinessHours($timestampend, $timestampbegin)

which gives me the difference between the two times excluding business hours and let's assume business hours to be Weekdays 8am-8pm PST.

One ugly solution I can think of is to run through a loop for every second between $timestampbegin and $timestampend and do a check if it's in a business time and count++ if so, but this is a terrible solution.

Any easier way of doing this?

I'm doing this in PHP, but any pseudo-code would help me. Thanks!

 

[Markbnj]

Since 1/2 of each weekday is business hours, you might start by getting the number of weekdays in the range. But since 5/14 of each calendar week is business hours you might also start with getting the number of full calendar weeks in the range, and then work on the remainder.

 

[Ken g6]

To begin with, consider if both timestamps are on the same day. I see three cases for $timestampbegin: before business hours, during business hours, or after business hours. The same three cases exist for $timestampend. Put them together and you have nine different cases if both timestamps are on the same day. Edit: Three of those cases have the end time before the beginning time, so make that six cases.

Next, make a 3x3 table, and write down what you need to do in each (valid) case. For instance, if both timestamps are during business hours, return 0.

Now, consider that if $timestampbegin is on one day, and $timestampend is on another, you can break the problem up into:

removeBusinessHours($timestampend, $timestampbegin) =
removeBusinessHours(endofday($timestampbegin), $timestampbegin) +
removeBusinessHours(beginningofday($timestampend), endofday($timestampbegin)) +
removeBusinessHours($timestampend, beginningofday($timestampend))

where beginningofday() returns 12:00am on that given day, and endofday() returns 12:00am on the next day.

So now the two end cases are "on the same day" cases, and you just have to fill in the middle. To do that, do what Markbnj said.

 

[Evadman]

Here's a version I wrote for SQL Server that returns the number of work seconds between 2 dates. It accepts the start and end datetimes for weekdays, sat and sun separately. You would wan the exact oposite for your code, hopefully this is useful.

/*************************************************************************
	Function Name:  WorkTimeOpen
	Type:			Scaler
	Designer:		Evadman
	Last Updated:	Tue May 10 16:17:12 CDT 2005 @928 /Internet Time/
	Updated by:		Evadman
	Information:	This function returns the number of work time seconds between 2 times.
	Format:			Integer
	Length:			4
	************************************************************************
	Version:		1.00
	Date:			Tue May 10 16:17:10 CDT 2005 @928 /Internet Time/
***************************************************************************/
CREATE Function WorkTimeOpen
	(
	@BegDate DateTime, 
	@EndDate datetime,
	@WeekdayHourOpen int = NULL, 
	@WeekdayHourClosed int = NULL, 
	@SatHourOpen int = NULL, 
	@SatHourClosed int = NULL, 
	@SunHourOpen int = NULL, 
	@SunHourClosed int = NULL
	) 
	Returns int
AS
Begin		
	DECLARE @TimeStart DateTime
    DECLARE @TimeEnd DateTime
    DECLARE @TempDate DateTime
    DECLARE @TmpSeconds int
	SET @TmpSeconds = 0
	IF @WeekdayHourOpen = NULL
		SET @WeekdayHourOpen = 0 
	IF @WeekdayHourClosed = NULL
		SET @WeekdayHourClosed = 0
	IF @SatHourOpen = NULL
		SET @SatHourOpen = 0
	IF @SatHourClosed = NULL
		SET @SatHourClosed = 0
	IF @SunHourOpen = NULL
		SET @SunHourOpen = 0
	IF @SunHourClosed = NULL
		SET @SunHourClosed = 0
    /* return backwards days if beg and end date are backwards, basicly always return a pos number */ 
    If @EndDate < @BegDate
        BEGIN
		SET @TimeEnd = @BegDate
        SET @TimeStart = @EndDate
		END
    Else
        BEGIN
		SET @TimeEnd = @EndDate
        SET @TimeStart = @BegDate
    END
	/*round the times to the ending/beginning time (start times) */
    If DATEPART(dw, @TimeStart) = 1
		BEGIN
        If DATEPART(hh, @TimeStart) < @SunHourOpen 
			BEGIN
			SET @TimeStart = CAST(convert(varchar,@TimeStart,101) AS DateTime) -- start time before open
			SET @TimeStart = DateAdd(hh, @SunHourOpen, @TimeStart)
		END
		If DATEPART(hh, @TimeStart) >= @SunHourClosed 
			BEGIN
			SET @TimeStart = CAST(convert(varchar,@TimeStart,101) AS DATETIME) --start time before open
			SET @TimeStart = DateAdd(hh, @SunHourClosed, @TimeStart)
		END
	END
	If DATEPART(dw, @TimeStart) = 7
		BEGIN
        If DATEPART(hh, @TimeStart) < @SatHourOpen
			BEGIN
			SET @TimeStart = CAST(convert(varchar,@TimeStart,101) AS DATETIME) -- start time before open
			SET @TimeStart = DateAdd(hh, @SatHourOpen, @TimeStart)
		END	
		If DATEPART(hh, @TimeStart) >= @SatHourClosed 
			BEGIN
			SET @TimeStart = CAST(convert(varchar,@TimeStart,101) AS DATETIME) -- start time before open
			SET @TimeStart = DateAdd(hh, @SatHourClosed, @TimeStart)
		End		
	END
	IF DATEPART(dw, @TimeStart) > 1 AND DATEPART(dw, @TimeStart) < 7
        BEGIN
		If DATEPART(hh, @TimeStart) < @WeekdayHourOpen
			BEGIN
			SET @TimeStart = CAST(convert(varchar,@TimeStart,101) AS DATETIME) --start time before open
			SET @TimeStart = DateAdd(hh, @WeekdayHourOpen, @TimeStart)
		END
		If DATEPART(hh, @TimeStart) >= @WeekdayHourClosed
			BEGIN
			SET @TimeStart = CAST(convert(varchar,@TimeStart,101) AS DATETIME) -- start time before open
			SET @TimeStart = DateAdd(hh, @WeekdayHourClosed, @TimeStart)
		END
	END	
		
    /* round the times to the ending/beginning time (end times) */
    If DATEPART(dw, @TimeEnd) = 1  -- sunday
        BEGIN
		If DATEPART(hh, @TimeEnd) >= @SunHourClosed
			BEGIN
			SET @TimeEnd = CAST(convert(varchar,@TimeEnd,101) AS DATETIME) -- start time before open
			SET @TimeEnd = DateAdd(hh, @SunHourClosed, @TimeEnd)
		END
		If DATEPART(hh, @TimeEnd) < @SunHourOpen 
			BEGIN
			SET @TimeEnd = CAST(convert(varchar,@TimeEnd,101) AS DATETIME) -- start time before open
			SET @TimeEnd = DateAdd(hh, @SunHourOpen, @TimeEnd)
		END
	END
    If DATEPART(dw, @TimeEnd) = 7  -- saturday
        BEGIN
		If DATEPART(hh, @TimeEnd) >= @SatHourClosed
			BEGIN
			SET @TimeEnd = CAST(convert(varchar,@TimeEnd,101) AS DATETIME) -- start time before open
			SET @TimeEnd = DateAdd(hh, @SatHourClosed, @TimeEnd)
		END
		If DATEPART(hh, @TimeEnd) < @SatHourOpen 
			BEGIN	
			SET @TimeEnd = CAST(convert(varchar,@TimeEnd,101) AS DATETIME) -- start time before open
			SET @TimeEnd = DateAdd(hh, @SatHourOpen, @TimeEnd)
		END		
	END		
	IF DATEPART(dw, @TimeStart) > 1 AND DATEPART(dw, @TimeStart) < 7
        BEGIN
		If DATEPART(hh, @TimeEnd) >= @WeekdayHourClosed
			BEGIN
			SET @TimeEnd = CAST(convert(varchar,@TimeEnd,101) AS DATETIME) -- start time before open
			SET @TimeEnd = DateAdd(hh, @WeekdayHourClosed, @TimeEnd)
		END
		If DATEPART(hh, @TimeEnd) < @WeekdayHourOpen 
			BEGIN
			SET @TimeEnd = CAST(convert(varchar,@TimeEnd,101) AS DATETIME) -- start time before open
			SET @TimeEnd = DateAdd(hh, @WeekdayHourOpen, @TimeEnd)
		END
	END
		
		
		
	/* if the datevalues are on different days, add the number of workseconds for each day */
    While DateDiff(dd, @TimeStart, @TimeEnd) > 0
		BEGIN
		/* add the number of work seconds in the current day of week to the total. */
        If DATEPART(dw, @TimeStart) = 1
			BEGIN
			SET @TempDate = cast(convert(varchar,@TimeStart,101) as DATETIME)
			SET @TempDate = DateAdd(hh, @SunHourClosed, @TempDate)
            SET @TmpSeconds = @TmpSeconds + DateDiff(ss, @TimeStart, @TempDate)
		END
        If DATEPART(dw, @TimeStart) = 7
        	BEGIN    
			SET @TempDate = cast(convert(varchar,@TimeStart,101) as DATETIME)
			SET @TempDate = DateAdd(hh, @SatHourClosed, @TempDate)			
			SET @TmpSeconds = @TmpSeconds + DateDiff(ss, @TimeStart, @TempDate)
        END
		If DATEPART(dw, @TimeStart) > 1 AND DATEPART(dw, @TimeStart) < 7
			BEGIN
			SET @TempDate = cast(convert(varchar,@TimeStart,101) as DATETIME)
			SET @TempDate = DateAdd(hh, @WeekdayHourClosed, @TempDate)				
         	SET @TmpSeconds = @TmpSeconds + DateDiff(ss, @TimeStart, @TempDate)    
		END
		/* add a day */
		SET @TimeStart = DateAdd(D, 1, @TimeStart)
		
        /* change the start time of the current day to the beginning of the workday. */
        If DATEPART(dw, @TimeStart) = 1
			BEGIN
			SET @TimeStart = CAST(convert(varchar,@TimeStart,101) AS DATETIME) --start time at open
			SET @TimeStart = DateAdd(hh, @SunHourOpen, @TimeStart)
		END
		If DATEPART(dw, @TimeStart) = 7
        	BEGIN
			SET @TimeStart = CAST(convert(varchar,@TimeStart,101) AS DATETIME) --start time at open
			SET @TimeStart = DateAdd(hh, @SatHourOpen, @TimeStart)
		END	
		If DATEPART(dw, @TimeStart) > 1 AND DATEPART(dw, @TimeStart) < 7
           BEGIN
		   SET @TimeStart = CAST(convert(varchar,@TimeStart,101) AS DATETIME) --start time at open
		   SET @TimeStart = DateAdd(hh, @WeekdayHourOpen, @TimeStart)
		END
	END
	
    /* if the date value of the 2 dates is the same, use datediff to figure out the difference */
    If DateDiff(dd, @TimeStart, @TimeEnd) = 0
        SET @TmpSeconds = @TmpSeconds + DateDiff(S, @TimeStart, @TimeEnd)	
	
	RETURN @TmpSeconds
END