Calculate actual number of days from series of date ranges

Question

I need to calculate the actual number of days between a number of date ranges.

eg:

• 2014-01-01 to 2014-01-04 is 4 days
• 2014-01-05 to 2014-01-06 is 2 days

while

• 2014-02-01 to 2014-02-03 3 days with
• 2014-02-03 to 2014-02-05 3 days

is a total of 5 days

an added complication is that during a month there will be some gaps between date ranges and or overlapping date ranges to be taken into consideration

any ideas guys. ability to do the calc using mysql would be great.

Maybe i should have said count the number of days instead of calculate. I can get the number of days between two date ranges using either mysql or javascript as mentioned below, I think my wheels are coming off with the overlapping date ranges where one range starts before another has finished.

Answer 1

As suggested HERE: You can use Date objects in Javascript:

var prevTime = new Date(2011,1,1,0,0);  // Feb 1, 2011
var thisTime = new Date();              // now
var diff = thisTime.getTime() - prevTime.getTime();   // now - Feb 1
alert(diff / (1000*60*60*24));     // positive number of days

EDIT: I missed you tagged JavaScript, but asked for MySQL

As suggested HERE: If you are using DATE or DATETIME formatting for your column, you can use:

SELECT DATEDIFF(STR_TO_DATE('2014-01-01', '%Y-%m-%d'),STR_TO_DATE('2014-01-04', '%Y-%m-%d')) AS DAYS

Hope that helps

EDIT2 Here's a nice way to do it in one statement with some logic:

SELECT (CASE 
  WHEN Start_date1 <= End_date2 THEN
    1+DATEDIFF(End_date2, Start_date1)
  WHEN Start_date2 <= End_date1 THEN
    1+DATEDIFF(End_date1, Start_date2)
  ELSE 0
  END) AS DAYS
FROM TABLE

The logic is: Date1 starts before Date2 ends, Start_date1 >= End_date2

OR

Date2 starts before Date1 ends, Start_date2 >= End_date1

If neither is true, they don't overlap.