CODEWITHSUNDEEP
mysqlsql
user1043036
user1043036

Reputation: 83

Return Unique Dates in Range of Days

I am trying to query a table that is setup with id, startDateTime, endDateTime. Let's say row 1 looks like this:

id      startDateTime      endDateTime      
100     2/9/2012 20:55     3/21/2012 10:43

I need to query the above such that I get a distinct count of all the days in the above range. My expected result would be in the above 42 as there are 42 unique calendar days from 2/9 through 3/21. Datediff because it looks at the time piece gives me 41 days. I have tried various iterations of datediff and timediff trying to get this to work but can't find anything that works in all scenarios. Any suggestions as to how this can be done in SQL?

I started with a query as shown below:

SELECT ConditionStatus.ID, 
  SUM((DATEDIFF(ConditionStatus.endDate,ConditionStatus.startDate))) AS Duration 
WHERE ID = 100

My query returns a Duration of 41 which is technically accurate but I need to condition such that every date in the range of dates gets a count of 1

I am trying to mimic some logic we use on our datawarehouse where we persist a count of 1 for each date for which there was activity.

Thanks, Bob

Upvotes: 0

Views: 599

Answers (2)

Sebastian
Sebastian

Reputation: 967

See this answer - How do I select the number of distinct days in a date range?

Take a look before you use @Ike's answer. If you add +1, you will get one too many in instances where the time value is 00:00:00.

select datediff(days, '2/9/2012 20:55', '3/21/2012 10:43') returns 41

select datediff(days, '2/9/2012 20:55', '3/21/2012 10:43') + 1 returns 42

This is where that breaks -

select datediff(days, '2/9/2012 20:55', '3/21/2012 00:00:00') + 1 returns 42

That's the wrong answer. It should not include the last day.

Here's the workaround -

datediff(days, '2/9/2012 20:55', dateadd(seconds, -1, '3/21/2012 00:00:00') + 1

Upvotes: 0

Ike Walker
Ike Walker

Reputation: 65537

The basic answer is that you can use DATEDIFF() and add 1 because you want to include the current day.

For example:

mysql> select datediff(current_date(),current_date()) + 1;
+---------------------------------------------+
| datediff(current_date(),current_date()) + 1 |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+
1 row in set (0.00 sec)

Expanding on your original example, you can convert the strings to datetimes, then discard the time component, then calculate the inclusive date count with a query like this:

mysql> SELECT ABS(DATEDIFF(DATE(STR_TO_DATE('2/9/2012 20:55','%m/%d/%Y %H:%i')),
    ->          DATE(STR_TO_DATE('3/21/2012 10:43','%m/%d/%Y %H:%i')))) + 1 as days_in_range;
+---------------+
| days_in_range |
+---------------+
|            42 |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT ABS(DATEDIFF(DATE(STR_TO_DATE('3/21/2012 10:43','%m/%d/%Y %H:%i')),
    ->          DATE(STR_TO_DATE('2/9/2012 20:55','%m/%d/%Y %H:%i')))) + 1 as days_in_range;
+---------------+
| days_in_range |
+---------------+
|            42 |
+---------------+
1 row in set (0.00 sec)

Upvotes: 3

Related Questions