Mysql:在两个日期和时间之间寻找空的时间块? [英] Mysql : Finding empty time blocks between two dates and times?
本文介绍了Mysql:在两个日期和时间之间寻找空的时间块?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想从数据库表中找出用户的可用性:
I wanted to find out user's availability from database table:
primary id | UserId | startdate | enddate
1 | 42 | 2014-05-18 09:00 | 2014-05-18 10:00
2 | 42 | 2014-05-18 11:00 | 2014-05-18 12:00
3 | 42 | 2014-05-18 14:00 | 2014-05-18 16:00
4 | 42 | 2014-05-18 18:00 | 2014-05-18 19:00
让我们考虑上面插入的数据是用户的忙碌时间,我想从表中找出开始时间和结束时间之间的空闲时间间隔块.
Let's consider above inserted data is user's busy time, I want to find out free time gap blocks from table between start time and end time.
BETWEEN 2014-05-18 11:00 AND 2014-05-18 19:00;
让我在这里添加表格模式以避免混淆:
Let me add here schema of table for avoiding confusion:
Create Table availability (
pid int not null,
userId int not null,
StartDate datetime,
EndDate datetime
);
Insert Into availability values
(1, 42, '2013-10-18 09:00', '2013-10-18 10:00'),
(2, 42, '2013-10-18 11:00', '2013-10-18 12:00'),
(3, 42, '2013-10-18 14:00', '2013-11-18 16:00'),
(4, 42, '2013-10-18 18:00', '2013-11-18 19:00');
要求:我想找出免费间隔记录,例如:
REQUIREMENT: I wanted to find out free gap records like:
'2013-10-27 10:00' to '2013-10-28 11:00' - User is available for 1 hours and
'2013-10-27 12:00' to '2013-10-28 14:00' - User is available for 2 hours and
available start time is '2013-10-27 10:00' and '2013-10-27 12:00' respectively.
推荐答案
给你
SELECT t1.userId,
t1.enddate, MIN(t2.startdate),
MIN(TIMESTAMPDIFF(HOUR, t1.enddate, t2.startdate))
FROM user t1
JOIN user t2 ON t1.UserId=t2.UserId
AND t2.startdate > t1.enddate AND t2.pid > t1.pid
WHERE
t1.endDate >= '2013-10-18 09:00'
AND t2.startDate <= '2013-11-18 19:00'
GROUP BY t1.UserId, t1.endDate
http://sqlfiddle.com/#!2/50d693/1
这篇关于Mysql:在两个日期和时间之间寻找空的时间块?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文