Calculate the actual number of days from a series of date ranges

Question

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

eg:

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

and

• 2014-02-01 - 2014-02-03 3 days from
• 2014-02-03 - 2014-02-05 3 days

is a total of 5 days

an added complication is that during the month there will be some gaps between date ranges and or overlapping date ranges that need to be considered

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

, . , mysql, javascript, , , , , .

Answer 1

: Date 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: JavaScript, MySQL

: DATE DATETIME , :

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

,

EDIT2 :

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

: 1 2, Start_date1 >= End_date2

2 1, Start_date2 >= End_date1

, .

Answer 2

SQL . DATEDIFF():

SELECT 1+DATEDIFF(MAX(end_date), MIN(start_date)) AS duration_in_days, event_id
  FROM event_table
 GROUP BY event_id

?

, 21--14-22--14 2 , DATEDIFF 1. 1 .

-, GROUP BY , , . , ; . , .

-, , . , ,

21-Aug-14 to 22-Aug-14 
27-Aug-14 to 28-Aug-14

21 -14-28 -14, . 23 26 4 . .