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.

Please edit the question and show the format of the data in the database. Sample data and desired results really help, at least with questions involving SQL. — Gordon Linoff, Sep 06 '14 at 13:04
I would probably approach this question by building a table of "all possible dates" (having some limiting heuristic would be helpful) and then making outer-joins between that table and each range, filtering those records for which there was no matching join and counting the result. — eggyal, Sep 06 '14 at 13:12
It sounds like you're trying to compute the overall duration of a date range that's defined by a possibly overlapping collection of start/end date pairs. Is that correct? — O. Jones, Sep 06 '14 at 14:39
Post what the input (range of dates) looks like. Does it come from an array, or table, etc. — Salman A, Sep 06 '14 at 14:51
Salman it is the result of a mysql query, so it comes back as 2014-01-01 as the format. — Terence Bruwer, Sep 06 '14 at 15:33
ollie that is correct and as you stated it is the gaps in between that are the problem — Terence Bruwer, Sep 06 '14 at 15:34

score 2 · Answer 1 · edited May 23 '17 at 12:05

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.

look at this [link](http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap) i am basically trying to get this right but with multiple values — Terence Bruwer, Sep 06 '14 at 13:52
You could use this logic: `(StartDate1 <= EndDate2) and (EndDate1 >= StartDate2)` or something suggested here: http://stackoverflow.com/questions/117962/simplest-efficient-way-to-find-rows-with-time-interval-overlaps-in-sql Although that link suggests that you're comparing one table to another, and returning overlaps, you could easily adapt it. — Ian M, Sep 06 '14 at 14:00

score 0 · Answer 2 · answered Sep 06 '14 at 14:48

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.

Calculate actual number of days from series of date ranges

2 Answers2