CODEWITHSUNDEEP
javascriptjquerymysql
Terence Bruwer
Terence Bruwer

Reputation: 239

Calculate actual number of days from series of date ranges

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.

Upvotes: 5

Views: 346

Answers (2)

Ian M
Ian M

Reputation: 731

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.

Upvotes: 2

O. Jones
O. Jones

Reputation: 108706

This little snippet of SQL request code may get you started. It uses DATEDIFF():

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

What's going on here?

First, you've said that the range 21-Aug-14 to 22-Aug-14 is 2 days, but DATEDIFF computes it as 1. So we need to add 1 to it.

Second, the GROUP BY here will aggregate multiple date ranges, if any, for the same event. I have no idea if you're using events; you didn't say. The point here is to show how to aggregate these dates.

Third, if your individual date ranges are non-overlapping, this query won't work correctly. For example, suppose you have these two date ranges

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

This query will come up with the aggregate range 21-Aug-14 to 28-Aug-14, which is eight days. But you may want to omit the gap 23-Aug to 26-Aug, and only report 4 days. This query won't do that.

Upvotes: 0

Related Questions