1

How can I calculate the time difference between two date, considering:

  • Only Monday to Friday
  • Time between 9am to 5:30pm;
  • Exclude Holidays.

Example:

  • d1 = 2012-10-05 17:00:00
  • d2 = 2012-14-09 12:00:00

Calculation Steps:

  • 2012-10-05 = 00:30:00
  • 2012-10-06 = 00:00:00
  • 2012-10-07 = 00:00:00
  • 2012-10-08 = 07:30:00
  • 2012-10-09 = 04:00:00

ddiff(d2,d1) = 12:00:00

I know how to do it using only mon-fri, as described here. And I am talking about MySQL.

Community
  • 1
  • 1
Erick Engelhardt
  • 704
  • 2
  • 10
  • 30
  • This question is very similar. Maybe you can use it as inspiration. http://stackoverflow.com/questions/593898/sql-datediff-advanced-usage – Tomalak Oct 05 '12 at 20:57
  • this will require a row-level operation (cursor) and you will need a holiday table. – amphibient Oct 05 '12 at 20:57
  • @foampile . . . This does not require a cursor. A calendar table is the preferred way of identifying holidays, better than a table of just holidays. – Gordon Linoff Oct 05 '12 at 21:31
  • yes, you can do it with a calendar table by getting the count of rows that are in between the two dates and where IS_WORK_DAY=1 times 8 plus the business hours on the first and last date ... BUT that involves a calendar table with every day as opposed to just a holiday table ... personally, i would go for a simple cursor for simplicity and code cleanliness but it's a matter of preference – amphibient Oct 05 '12 at 21:44
  • personally, i would do this computation in the application tier and not in SQL. Perl comes to mind – amphibient Oct 05 '12 at 21:46

1 Answers1

2

I've come up with a solution that's relatively straightforward for calculating the time difference for the full interim dates. However it's a bit messy to use mysql for calculating the time difference for the start & end dates. I have included them in my solution, but with a number of assumptions.

In any case, here's the sql

SET @startdate:='2012-12-24 17:00:00';
SET @enddate:='2013-01-02 12:00:00';

SELECT
TIME_TO_SEC(TIMEDIFF(CONCAT(DATE(@startdate),' 17:30:00'), @startdate))/3600 as startday_time,
TIME_TO_SEC(TIMEDIFF(@enddate, CONCAT(DATE(@enddate),' 9:00:00')))/3600 as endday_time,
SUM(daily_hours) as otherdays_time from
(
    SELECT 7.5 as daily_hours, id, DATE_ADD(DATE(@startdate),INTERVAL id-1 DAY) as idate from numbers
) dates
LEFT JOIN holidays on DATE(dates.idate) = DATE(holidays.date)
WHERE
idate BETWEEN @startdate AND @enddate
AND holidays.date IS NULL
AND DAYOFWEEK(idate) <> 7 AND DAYOFWEEK(idate) <> 1;

sqlfiddle here: http://sqlfiddle.com/#!2/ff3f3/1/2

To get the valid interim dates, we'll need two tables - a holidays table listing all the holiday dates and a numbers table that contains a series of integers which is very useful for joining against to get a sequential series of dates (with no gaps).

Note: In the sqlfiddle, I've populated the numbers table only up to 12 to cover the dates used in my example - it will probably need to be populated to a higher number depending on the range of dates you'll be working with.

For the start day time & end day time, I've made the following assumptions:

  • that start date & end date are both valid dates that should be counted towards the total time
  • that the time on the start date is between lunch and 17.30
  • that the time on the end date is between lunch and 17.30

if these assumptions are wrong, you're getting into serious conditional territory (with lots of ifs) and might be best doing this in the php (or whatever).

note: I've left the times (which are in hours) un-added for illustration purposes.

caitriona
  • 8,569
  • 4
  • 32
  • 36