I have a table which stores a list of activities with an interval of time delimited by 2 dates.
Sample:
+------+---------------------+---------------------+-------------+
| name | start | end | time (calc) |
+------+---------------------+---------------------+-------------+
| me | 2017-04-03 11:00:00 | 2017-04-03 11:30:00 | 30 |
| me | 2017-04-03 23:45:00 | 2017-04-04 00:15:00 | 30 |
| me | 2017-04-04 10:00:00 | 2017-04-04 11:00:00 | 60 |
| me | 2017-04-04 10:30:00 | 2017-04-04 11:30:00 | 60 |
| me | 2017-04-05 23:00:00 | 2017-04-05 23:30:00 | 30 |
| me | 2017-04-05 23:15:00 | 2017-04-07 00:45:00 | 1530 |
+------+---------------------+---------------------+-------------+
I would like to know how many minutes are occupied each day per user (and then per week), so I need to transform current table into where intervals which share a partial space time are merged in a single one, and intervals which are in several days are split, like the next one:
+------+---------------------+---------------------+-------------+
| name | start | end | time (calc) |
+------+---------------------+---------------------+-------------+
| me | 2017-04-03 11:00:00 | 2017-04-03 11:30:00 | 30 |
| me | 2017-04-03 23:45:00 | 2017-04-03 23:59:59 | 15 |
| me | 2017-04-04 00:00:00 | 2017-04-04 00:15:00 | 15 |
| me | 2017-04-04 10:00:00 | 2017-04-04 11:30:00 | 90 |
| me | 2017-04-05 23:00:00 | 2017-04-05 23:59:59 | 60 |
| me | 2017-04-06 00:00:00 | 2017-04-06 23:59:59 | 1440 |
| me | 2017-04-07 00:00:00 | 2017-04-07 00:45:00 | 45 |
+------+---------------------+---------------------+-------------+
To then query it easily in order to obtain minutes per day:
+------+------------+------+
| name | day | time |
+------+------------+------+
| me | 2017-04-03 | 45 |
| me | 2017-04-04 | 105 |
| me | 2017-04-05 | 60 |
| me | 2017-04-06 | 1440 |
| me | 2017-04-07 | 45 |
+------+------------+------+
I were looking for information an I found how to merge multiple date intervals here (mysql – sum interval dates), however I am not able to split an interval in several days.
It is possible to do it in a single query? How could I do it?
Edit:
SQL (structure and data):
CREATE TABLE activities (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
name VARCHAR(45),
start DATETIME,
end DATETIME,
time INT GENERATED ALWAYS AS (TIMESTAMPDIFF(MINUTE, start, end)) VIRTUAL
);
INSERT INTO activities (name, start, end) VALUES
('me','2017-04-03 11:00','2017-04-03 11:30'),
('me','2017-04-03 23:45','2017-04-04 00:15'),
('me','2017-04-04 10:00','2017-04-04 11:00'),
('me','2017-04-04 10:30','2017-04-04 11:30'),
('me','2017-04-05 23:00','2017-04-05 23:30'),
('me','2017-04-05 23:15','2017-04-07 00:45');
SQL to merge multiple intervals (Soruce: mysql – sum interval dates):
SELECT name, min(start) AS start, end, TIMESTAMPDIFF(MINUTE, MIN(start), end) AS time
FROM (
SELECT x.name, x.start, min(y.end) AS end
FROM activities AS x
JOIN activities AS y
ON x.name = y.name
AND x.start <= y.end
AND NOT EXISTS (
SELECT 1
FROM activities AS z
WHERE y.name = z.name
AND y.end >= z.start
AND y.end < z.end
)
WHERE NOT EXISTS (
SELECT 1
FROM activities AS u
WHERE x.name = u.name
AND x.start > u.start
AND x.start <= u.start
)
GROUP BY x.name, x.start
) AS v GROUP BY name, end;
Best Answer
First due you need to generate a series of dates, I'd suggest to use a
calendar
table.How do they do it
Just to get overlapped activities I've used the query that you provided on your question.
First I calculate minutes from start date till midnight:
Then, if start <> end, I calculate minutes from midnight till end date:
This returns a table like this:
That's nice, but there is another problem here:
Of course:
1545 <> 60 + 45
We need to generate a series of dates between start and end date, and add 1440 minutes to each day.
We can get it using the calendar table:
Ok, we got all ingredients, it's time to cook the recipe:
Final result:
Almost forget it, the recipe: http://rextester.com/EIJOI20983