CODEWITHSUNDEEP
mysqlsqldatetimegroup-by
konewka
konewka

Reputation: 650

Count amount of days within timestamps in MySQL table

Suppose I have a table with the following records: SQL Fiddle

| category |               begin |                 end |
|----------|---------------------|---------------------|
|        a | 2017-09-26 08:00:00 | 2017-09-27 10:00:00 |
|        b | 2017-10-02 13:00:00 | 2017-10-03 07:00:00 |
|        a | 2017-10-06 02:00:00 | 2017-10-06 09:00:00 |
|        a | 2017-10-06 11:00:00 | 2017-10-06 14:00:00 |
|        a | 2017-10-06 19:00:00 | 2017-10-08 10:00:00 |

I want to be able to count the total amount of distinct days per category (in between begin and end). So: category a contains the periods 26 to 27 September and 6 to 8 October (where 6 October shows up in multiple records), and category b only has the period from 2 to 3 October. I would therefore want to get the following result:

| category | days |
|----------|------|
|        a |    5 |
|        b |    2 |

from some GROUP BY category statement. Is this possible in (My)SQL?

Upvotes: 1

Views: 90

Answers (3)

Stobor
Stobor

Reputation: 45132

One possible solution involves constructing a "reference table" containing all possible dates for your situation. (For example, you could populate this 20 years into the future, and 20 into the past.) For this sqlfiddle, I've just filled in the necessary days. I created ref_dates with a single date column day, and used the following sql:

select category,
count(distinct day)
from times
inner join ref_dates 
    on cast(begin as date) <= day 
    and day <= cast(end as date)
group by category;

Upvotes: 1

Adam92
Adam92

Reputation: 436

I would use timestampdiff and make use of a simple sum. This would calculate the sum of differences in minutes for you to then convert as desired.

select category,
sum(timestampdiff(minute, begin,end)) as 'diff'
from times
group by category

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

If they all overlap (as in your example data), you can use:

select category, datediff(min(begin), max(end))
from t
group by category;

If there are gaps, the problem is much harder.

Upvotes: 0

Related Questions