MySQL中的日期字段，查找所有不重叠的行并仅返回差异

发布于 2024-08-03 04:00:08 字数 2048 浏览 10 评论 0原文

这是我在这里提出的第一个问题，但我有一点小小的不同：

所以我有两个人的日程安排在数据库中。时间表仅记录两个用户的开始时间、结束时间以及各种事件/约会的描述。

A 想要与 B 交换约会。我想要一个 MySQL 查询，它将返回 PersonB 和 PersonA 可以交换的所有时间。

最初，查询的参数是丢弃与 PersonA 重叠的 PersonB 的任何约会，并且 PersonB 的约会必须与 PersonA 想要交换的约会的长度完全相同。我得到了一些关于时间算术/几何的很好的建议，帮助我得到了我需要的结果。

现在我想更改一对一参数，以便约会的长度不必相等。因此，如果 PersonA 想要调换周一早上的约会（上午 10:00 - 11:30 AM），则查询将：

排除 PersonA 约会期间的 PersonB 的任何约会
包括 PersonA 约会之外的 PersonB 的任何约会
包括PersonB 的约会中当 PersonA 有空时的部分，但仅显示空闲部分。

因此，如果 PersonA 想要交换上述约会（同样是周一上午 10:00 - 11:30），并且 PersonA 在周二下午 1:00 到 3:00 有约会，而 PersonB 在周二 12 点有约会： 00 PM 到 4:00 PM，查询将返回：

Possible_Swaps
==============
userID  | Start             | End             | Description
PersonB | Tuesday, 12:00 PM | Tuesday 1:00 PM | Cooking
PersonB | Tuesday,  4:00 PM | Tuesday 5:00 PM | Cooking

除了任何其他可能性之外。对数据库的期望是否过高？如果是这样，关于如何至少获得那些重叠但时间悬在两侧以便 PHP 脚本可以处理它们的班次有什么建议吗？

根据塞利的要求，这里有更多背景信息：

我一直在说约会，但我认为我真正的意思是“工作”，如“工作轮班”。 A 和 B 在同一办公室工作。在 vcalendar 中，轮班通常被称为“活动”，但有时也被称为“约会”，我选择后者，因为听起来不太像两个人要去展览会。

因此，PersonA 在周一上午 10:00 至 11:30 轮班洗碗。 PersonB 于周二中午 12:00 至下午 5:00 做饭。 PersonA 真的很想在周一离开小镇之前见到他的兄弟。他宁愿周一早上都休息，但他也愿意轮班休息一个小时。

因此，在我的旧模型中（在我的第一个问题中提出），我正在寻找任何转变没有重叠并且班次在时间上相等。但这有两个问题：

如果我需要有人来代替我周二的 2 小时轮班，而我周四工作 4 小时，乔周四工作 8 小时，我可以交换他的两个小时，他就可以离开早一点，我可以晚一点。
如果我有两个小时的轮班时间，但我很乐意用一个小时来准时到达机场，我想知道本周晚些时候某某是否会比我早一小时到达这样我就可以代替他的那部分轮班时间。

长话短说（太晚了），我想要明显被称为 relative PersonA 的轮班对 PersonB 的补充（基本上任何时候 PersonB 正在工作而 PersonA 不工作，无论班次是否在其他点重叠。）

理想情况下，我会得到一组结果，其中包括PersonB 正在工作而 PersonA 没有工作的部分（上面提到的两个 1 小时轮班），以及整个班次（带有一个特殊标签来表明它作为一个整体不可用），以便 PersonA 会看到他正在覆盖轮班的一部分，不要感到困惑，并认为 PersonB 只是碰巧工作两个一小时的轮班。

这一切听起来有点复杂。基本上我希望 PersonB 的班次为蓝色，PersonA 的班次为黄色，并且我希望数据库返回所有非绿色的部分。

原文

So this was one of my very first questions here, but I have a slight variation:

So I have two people whose schedules are in a database. The schedules simply record the start time, end time, and description of the various events/appointments for both users.

PersonA wants to trade appointments with PersonB. I want a MySQL query that will return all of them times that PersonB and PersonA can swap.

Originally the parameters of the query were to throw out any appointments of PersonB where there was overlap with PersonA and PersonB's appointment had to be the exact same length as the appointment PersonA wants to swap. I got some great advice on time arithmetic/geometry that helped me get the results I needed.

Now I want to change the 1-to-1 parameter so that the appointments don't have to be equal in length. So if PersonA wants to swap his Monday morning appointment (10:00 AM - 11:30 AM), the query will:

Exclude any of PersonB's appointments that are during one of PersonA's appointments
Include any of PersonB's appointments that are outside of PersonA's appointments
Include the parts of PersonB's appointments that are while PersonA is free, but only show the free portion.

So if PersonA wants to swap the above appointment (again, Monday 10:00 AM - 11:30 AM), and PersonA has an appointment on Tuesday 1:00 PM to 3:00 PM and PersonB has an appointment on Tuesday from 12:00 PM to 4:00 PM, the query would return:

Possible_Swaps
==============
userID  | Start             | End             | Description
PersonB | Tuesday, 12:00 PM | Tuesday 1:00 PM | Cooking
PersonB | Tuesday,  4:00 PM | Tuesday 5:00 PM | Cooking

In addition to any other possibilities. Is this too much to expect from the database? If so, any suggestions on how to at least get those shifts that are overlapping but have the times hanging over either side so that a PHP script could deal with them?

per searlea's request, here's a bit more context:

I kept saying appointments but I think I really meant "jobs" as in "work shifts". PersonA and PersonB work in the same office. In vcalendar, work shifts are usually referred to as "Events" but on occasion "Appointments" and I went with the latter as it sounds less like the two Persons are going to a fair.

So PersonA has a dish-washing shift on Monday from 10:00 to 11:30 AM. PersonB is cooking on Tuesday from 12:00PM to 5:00PM. PersonA really wants to see his brother before they leave town on Monday. He'd rather get all of Monday morning off, but he'd settle for getting an hour of shift off .

So in my old model (brought up in my very first question here), I was looking for any shifts where there was no overlap and where the shifts were equal in time. But that has two problems:

If I need someone to cover my 2 hour shift on Tuesday and I work for 4 hours on Thursday, and Joe works for 8 hours on Thursday, I could swap two of his hours and he could leave a bit early and I can stay a bit later.
If I have a two hour shift, but I'd gladly trade an hour of it just to make it to the airport on time, I want to know if such and such comes in an hour earlier than me later in the week so I can take that part of his shift.

Long story short (too late), I want what is apparently known as the relative complement of PersonA's shifts to PersonB (basically any times that PersonB is working and PersonA is not, regardless of whether the shifts overlap at some other point.)

Ideally, I would get a set of results that included the bits that PersonB was working and PersonA was not (the two 1 hour shifts mentioned above), as well as the entire shift (with a special tag to indicate it is not available as a whole) so that PersonA would see that he was covering part of a shift and not get confused and think that PersonB just happened to be working two one hour shifts.

This is all starting to sound a bit complicated. Basically I want PersonB's shifts to be be blue, PersonA's shifts to be yellow, and I want the database to return all of the parts that are not green.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

小巷里的女流氓 2024-08-10 04:00:08

SELECT * 
  FROM schedule AS s1
WHERE
  s1.user = 'Ondra'
AND
NOT EXISTS ( 
  SELECT * FROM schedule AS s2 
  WHERE
    s2.user = 'Zizka'
    AND (
      s2.start BETWEEN s1.start AND s1.end 
      OR
      s2.end BETWEEN s1.start AND s1.end 
      OR 
      s1.start > s2.start AND s1.end < s2.end 
    )
)

这会选择 Ondra 的事件，这些事件可以填补 Zizka 日记中的空白。

编辑：本来它是一个相交，但如果你想要相对补集，这就足够了。

SELECT * 
  FROM schedule AS s1
WHERE
  s1.user = 'Ondra'
AND
NOT EXISTS ( 
  SELECT * FROM schedule AS s2 
  WHERE
    s2.user = 'Zizka'
    AND (
      s2.start BETWEEN s1.start AND s1.end 
      OR
      s2.end BETWEEN s1.start AND s1.end 
      OR 
      s1.start > s2.start AND s1.end < s2.end 
    )
)

This selects Ondra's events which can fit into a gap in Zizka's diary.

Edited: Originally it was an intersect, but if you want the relative complement, this is enough.

回复收藏 0 原文

⊕婉儿 2024-08-10 04:00:08

让 $shift_id 为您的用户想要交换的班次的 ID。

select swappable.shift_id, swappable.user_id, swappable.description,
    FROM_UNIXTIME(swappable.shiftstart) as start,
    FROM_UNIXTIME(swappable.shiftend) as end,
    (swappable.shiftend - swappable.shiftstart) -
        sum(coalesce(least(conflict.shiftend, swappable.shiftend) -
            greatest(conflict.shiftstart, swappable.shiftstart), 0))
        as swaptime,
    group_concat(conflict.shift_id) as conflicts,
    group_concat(concat(FROM_UNIXTIME(conflict.shiftstart), ' - ',
        FROM_UNIXTIME(conflict.shiftend))) as conflict_times
from shifts as problem
join shifts as swappable on swappable.user_id != problem.user_id
left join shifts as conflict on conflict.user_id = problem.user_id
    and conflict.shiftstart < swappable.shiftend
    and conflict.shiftend > swappable.shiftstart
where problem.shift_id = 1
group by swappable.shift_id
having swaptime > 0;

测试

CREATE TABLE `shifts` (
  `shift_id` int(10) unsigned NOT NULL auto_increment,
  `user_id` varchar(20) NOT NULL,
  `shiftstart` int unsigned NOT NULL,
  `shiftend` int unsigned NOT NULL,
  `description` varchar(32) default NULL,
  PRIMARY KEY  (`shift_id`)
);

insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (1,'april', UNIX_TIMESTAMP('2009-04-04 10:00:00'),UNIX_TIMESTAMP('2009-04-04 12:00:00'),'Needs to be swapped');
insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (2,'bill',  UNIX_TIMESTAMP('2009-04-04 10:30:00'),UNIX_TIMESTAMP('2009-04-04 11:30:00'),'Inside today');
insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (3,'casey', UNIX_TIMESTAMP('2009-04-04 12:00:00'),UNIX_TIMESTAMP('2009-04-04 14:00:00'),'Immediately after today');
insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (4,'casey', UNIX_TIMESTAMP('2009-04-04 08:00:00'),UNIX_TIMESTAMP('2009-04-04 10:00:00'),'Immediately before today');
insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (5,'david', UNIX_TIMESTAMP('2009-04-04 11:00:00'),UNIX_TIMESTAMP('2009-04-04 15:00:00'),'Partly after today');

insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (6,'april', UNIX_TIMESTAMP('2009-04-05 10:00:00'),UNIX_TIMESTAMP('2009-04-05 12:00:00'),'Tommorow');
insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (7,'bill',  UNIX_TIMESTAMP('2009-04-05 09:00:00'),UNIX_TIMESTAMP('2009-04-05 11:00:00'),'Partly before tomorrow');
insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (8,'casey', UNIX_TIMESTAMP('2009-04-05 10:00:00'),UNIX_TIMESTAMP('2009-04-05 12:00:00'),'Equals tomorrow');
insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (9,'david', UNIX_TIMESTAMP('2009-04-05 10:30:00'),UNIX_TIMESTAMP('2009-04-05 11:30:00'),'Inside tomorrow');

insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (10,'april',UNIX_TIMESTAMP('2009-04-11 10:00:00'),UNIX_TIMESTAMP('2009-04-11 12:00:00'),'Next week');
insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (11,'april',UNIX_TIMESTAMP('2009-04-11 12:00:00'),UNIX_TIMESTAMP('2009-04-11 14:00:00'),'Second shift');
insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (12,'bill', UNIX_TIMESTAMP('2009-04-11 11:00:00'),UNIX_TIMESTAMP('2009-04-11 13:00:00'),'Overlaps two');
insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (13,'casey',UNIX_TIMESTAMP('2009-04-11 17:00:00'),UNIX_TIMESTAMP('2009-04-11 19:00:00'),'No conflict');

insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (14,'april',UNIX_TIMESTAMP('2009-05-04 10:00:00'),UNIX_TIMESTAMP('2009-05-04 12:00:00'),'Next month');
insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (15,'april',UNIX_TIMESTAMP('2009-05-04 13:00:00'),UNIX_TIMESTAMP('2009-05-04 15:00:00'),'After break');
insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (16,'bill', UNIX_TIMESTAMP('2009-05-04 11:00:00'),UNIX_TIMESTAMP('2009-05-04 14:00:00'),'Middle okay');

insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (17,'april',UNIX_TIMESTAMP('2010-04-04 10:00:00'),UNIX_TIMESTAMP('2010-04-04 11:00:00'),'Next year');
insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (18,'april',UNIX_TIMESTAMP('2010-04-04 11:30:00'),UNIX_TIMESTAMP('2010-04-04 12:00:00'),'After break');
insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (19,'april',UNIX_TIMESTAMP('2010-04-04 12:30:00'),UNIX_TIMESTAMP('2010-04-04 13:30:00'),'Third part');
insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (20,'bill', UNIX_TIMESTAMP('2010-04-04 10:30:00'),UNIX_TIMESTAMP('2010-04-04 13:00:00'),'Two parts okay');

结果：

'shift_id', 'user_id', 'description',              'start',               'end',                 'swaptime', 'conflicts', 'conflict_times'
 '3',       'casey',   'Immediately after today',  '2009-04-04 12:00:00', '2009-04-04 14:00:00', '7200',       NULL,       NULL
 '4',       'casey',   'Immediately before today', '2009-04-04 08:00:00', '2009-04-04 10:00:00', '7200',       NULL,       NULL
 '5',       'david',   'Partly after today',       '2009-04-04 11:00:00', '2009-04-04 15:00:00', '10800',     '1',        '2009-04-04 10:00:00 - 2009-04-04 12:00:00'
 '7',       'bill',    'Partly before tomorrow',   '2009-04-05 09:00:00', '2009-04-05 11:00:00', '3600',      '6',        '2009-04-05 10:00:00 - 2009-04-05 12:00:00'
'13',       'casey',   'No conflict',              '2009-04-11 17:00:00', '2009-04-11 19:00:00', '7200',       NULL,       NULL
'16',       'bill',    'Middle okay',              '2009-05-04 11:00:00', '2009-05-04 14:00:00', '3600',      '15,14',    '2009-05-04 13:00:00 - 2009-05-04 15:00:00,2009-05-04 10:00:00 - 2009-05-04 12:00:00'
'20',       'bill',    'Two parts okay',           '2010-04-04 10:30:00', '2010-04-04 13:00:00', '3600',      '19,18,17', '2010-04-04 12:30:00 - 2010-04-04 13:30:00,2010-04-04 11:30:00 - 2010-04-04 12:00:00,2010-04-04 10:00:00 - 2010-04-04 11:00:00'

这显示了可以交换任何部分的所有班次，包括如何交换
许多总时间（以秒为单位）是可以交换的。最后一栏，conflict_times，
显示交换用户已安排工作的时间。
应用程序应该很容易从中提取可用时间；
在 MySQL 中这是可能的，但非常棘手。

Let $shift_id be the id of the shift that your user wants to swap.

select swappable.shift_id, swappable.user_id, swappable.description,
    FROM_UNIXTIME(swappable.shiftstart) as start,
    FROM_UNIXTIME(swappable.shiftend) as end,
    (swappable.shiftend - swappable.shiftstart) -
        sum(coalesce(least(conflict.shiftend, swappable.shiftend) -
            greatest(conflict.shiftstart, swappable.shiftstart), 0))
        as swaptime,
    group_concat(conflict.shift_id) as conflicts,
    group_concat(concat(FROM_UNIXTIME(conflict.shiftstart), ' - ',
        FROM_UNIXTIME(conflict.shiftend))) as conflict_times
from shifts as problem
join shifts as swappable on swappable.user_id != problem.user_id
left join shifts as conflict on conflict.user_id = problem.user_id
    and conflict.shiftstart < swappable.shiftend
    and conflict.shiftend > swappable.shiftstart
where problem.shift_id = 1
group by swappable.shift_id
having swaptime > 0;

Tested with:

CREATE TABLE `shifts` (
  `shift_id` int(10) unsigned NOT NULL auto_increment,
  `user_id` varchar(20) NOT NULL,
  `shiftstart` int unsigned NOT NULL,
  `shiftend` int unsigned NOT NULL,
  `description` varchar(32) default NULL,
  PRIMARY KEY  (`shift_id`)
);

insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (1,'april', UNIX_TIMESTAMP('2009-04-04 10:00:00'),UNIX_TIMESTAMP('2009-04-04 12:00:00'),'Needs to be swapped');
insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (2,'bill',  UNIX_TIMESTAMP('2009-04-04 10:30:00'),UNIX_TIMESTAMP('2009-04-04 11:30:00'),'Inside today');
insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (3,'casey', UNIX_TIMESTAMP('2009-04-04 12:00:00'),UNIX_TIMESTAMP('2009-04-04 14:00:00'),'Immediately after today');
insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (4,'casey', UNIX_TIMESTAMP('2009-04-04 08:00:00'),UNIX_TIMESTAMP('2009-04-04 10:00:00'),'Immediately before today');
insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (5,'david', UNIX_TIMESTAMP('2009-04-04 11:00:00'),UNIX_TIMESTAMP('2009-04-04 15:00:00'),'Partly after today');

insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (6,'april', UNIX_TIMESTAMP('2009-04-05 10:00:00'),UNIX_TIMESTAMP('2009-04-05 12:00:00'),'Tommorow');
insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (7,'bill',  UNIX_TIMESTAMP('2009-04-05 09:00:00'),UNIX_TIMESTAMP('2009-04-05 11:00:00'),'Partly before tomorrow');
insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (8,'casey', UNIX_TIMESTAMP('2009-04-05 10:00:00'),UNIX_TIMESTAMP('2009-04-05 12:00:00'),'Equals tomorrow');
insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (9,'david', UNIX_TIMESTAMP('2009-04-05 10:30:00'),UNIX_TIMESTAMP('2009-04-05 11:30:00'),'Inside tomorrow');

insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (10,'april',UNIX_TIMESTAMP('2009-04-11 10:00:00'),UNIX_TIMESTAMP('2009-04-11 12:00:00'),'Next week');
insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (11,'april',UNIX_TIMESTAMP('2009-04-11 12:00:00'),UNIX_TIMESTAMP('2009-04-11 14:00:00'),'Second shift');
insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (12,'bill', UNIX_TIMESTAMP('2009-04-11 11:00:00'),UNIX_TIMESTAMP('2009-04-11 13:00:00'),'Overlaps two');
insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (13,'casey',UNIX_TIMESTAMP('2009-04-11 17:00:00'),UNIX_TIMESTAMP('2009-04-11 19:00:00'),'No conflict');

insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (14,'april',UNIX_TIMESTAMP('2009-05-04 10:00:00'),UNIX_TIMESTAMP('2009-05-04 12:00:00'),'Next month');
insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (15,'april',UNIX_TIMESTAMP('2009-05-04 13:00:00'),UNIX_TIMESTAMP('2009-05-04 15:00:00'),'After break');
insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (16,'bill', UNIX_TIMESTAMP('2009-05-04 11:00:00'),UNIX_TIMESTAMP('2009-05-04 14:00:00'),'Middle okay');

insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (17,'april',UNIX_TIMESTAMP('2010-04-04 10:00:00'),UNIX_TIMESTAMP('2010-04-04 11:00:00'),'Next year');
insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (18,'april',UNIX_TIMESTAMP('2010-04-04 11:30:00'),UNIX_TIMESTAMP('2010-04-04 12:00:00'),'After break');
insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (19,'april',UNIX_TIMESTAMP('2010-04-04 12:30:00'),UNIX_TIMESTAMP('2010-04-04 13:30:00'),'Third part');
insert  into `shifts`(`shift_id`,`user_id`,`shiftstart`,`shiftend`,`description`) values (20,'bill', UNIX_TIMESTAMP('2010-04-04 10:30:00'),UNIX_TIMESTAMP('2010-04-04 13:00:00'),'Two parts okay');

Results:

'shift_id', 'user_id', 'description',              'start',               'end',                 'swaptime', 'conflicts', 'conflict_times'
 '3',       'casey',   'Immediately after today',  '2009-04-04 12:00:00', '2009-04-04 14:00:00', '7200',       NULL,       NULL
 '4',       'casey',   'Immediately before today', '2009-04-04 08:00:00', '2009-04-04 10:00:00', '7200',       NULL,       NULL
 '5',       'david',   'Partly after today',       '2009-04-04 11:00:00', '2009-04-04 15:00:00', '10800',     '1',        '2009-04-04 10:00:00 - 2009-04-04 12:00:00'
 '7',       'bill',    'Partly before tomorrow',   '2009-04-05 09:00:00', '2009-04-05 11:00:00', '3600',      '6',        '2009-04-05 10:00:00 - 2009-04-05 12:00:00'
'13',       'casey',   'No conflict',              '2009-04-11 17:00:00', '2009-04-11 19:00:00', '7200',       NULL,       NULL
'16',       'bill',    'Middle okay',              '2009-05-04 11:00:00', '2009-05-04 14:00:00', '3600',      '15,14',    '2009-05-04 13:00:00 - 2009-05-04 15:00:00,2009-05-04 10:00:00 - 2009-05-04 12:00:00'
'20',       'bill',    'Two parts okay',           '2010-04-04 10:30:00', '2010-04-04 13:00:00', '3600',      '19,18,17', '2010-04-04 12:30:00 - 2010-04-04 13:30:00,2010-04-04 11:30:00 - 2010-04-04 12:00:00,2010-04-04 10:00:00 - 2010-04-04 11:00:00'

This shows all shifts for which any portion(s) can be swapped, including how
much total time (in seconds) is swappable. The final column, conflict_times,
shows the times for which the swapping user is already scheduled to work.
It should be easy for the application to extract the available times from that;
it's possible, but very tricky, in MySQL.

回复收藏 0 原文

给妤﹃绝世温柔 2024-08-10 04:00:08

任务

返回两个不同用户的所有间隔（重叠部分除外）。

表和测试数据

CREATE TABLE IF NOT EXISTS `shifts` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(1) NOT NULL,
  `start` datetime NOT NULL,
  `end` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

INSERT INTO `shifts` (`id`, `name`, `start`, `end`) VALUES
(1, 'a', '2000-01-01 01:00:00', '2000-01-01 03:00:00'),
(2, 'a', '2000-01-01 06:00:00', '2000-01-01 07:30:00'),
(3, 'b', '2000-01-01 02:00:00', '2000-01-01 04:00:00'),
(4, 'b', '2000-01-01 05:00:00', '2000-01-01 07:00:00'),
(5, 'a', '2000-01-01 08:00:00', '2000-01-01 11:00:00'),
(6, 'b', '2000-01-01 09:00:00', '2000-01-01 10:00:00'),
(7, 'a', '2000-01-01 12:00:00', '2000-01-01 13:00:00'),
(8, 'b', '2000-01-01 14:00:00', '2000-01-01 14:30:00'),
(9, 'a', '2000-01-01 16:00:00', '2000-01-01 18:00:00'),
(10, 'a', '2000-01-01 19:00:00', '2000-01-01 21:00:00'),
(11, 'b', '2000-01-01 17:00:00', '2000-01-01 20:00:00');

测试结果

        id  name    start           end
        1   a   2000-01-01 01:00:00 2000-01-01 02:00:00
        3   b   2000-01-01 03:00:00 2000-01-01 04:00:00
        4   b   2000-01-01 05:00:00 2000-01-01 06:00:00
        2   a   2000-01-01 07:00:00 2000-01-01 07:30:00
        5   a   2000-01-01 10:00:00 2000-01-01 11:00:00
        7   a   2000-01-01 12:00:00 2000-01-01 13:00:00
        8   b   2000-01-01 14:00:00 2000-01-01 14:30:00
        9   a   2000-01-01 16:00:00 2000-01-01 17:00:00
        11  b   2000-01-01 18:00:00 2000-01-01 19:00:00
        10  a   2000-01-01 20:00:00 2000-01-01 21:00:00

解决方案

我使用 MySQL 的“用户定义变量”功能通过以下查询来实现目标：

SET @inA=0, @inB=0, @lastAstart = 0, @lastBstart = 0, @lastAend = 0, @lastBend = 0;
SELECT id,name,start,end FROM (
    SELECT 
        id,name,
        IF(name='a',
          IF(UNIX_TIMESTAMP(start) > @lastBend, start, FROM_UNIXTIME(@lastBend)),
          IF(UNIX_TIMESTAMP(start) > @lastAend, start, FROM_UNIXTIME(@lastAend))
        ) as start,
        IF(name='a',
          IF(@inB,FROM_UNIXTIME(@lastBstart),end),
          IF(@inA,FROM_UNIXTIME(@lastAstart),end)
        )  as end,
        IF(name='a',
          IF(@inB AND (@lastBstart < @lastAstart), 1, 0),
          IF(@inA AND (@lastAstart < @lastBstart), 1, 0)
        ) as fullyEnclosed,
          isStart,
          IF(name='a',@inA:=isStart,0), 
          IF(name='b',@inB:=isStart,0), 
          IF(name='a',IF(isStart,@lastAstart:=t,@lastAend:=t),0), 
          IF(name='b',IF(isStart,@lastBstart:=t,@lastBend:=t),0)
    FROM (
            (SELECT *, UNIX_TIMESTAMP(start) as t, 1 as isStart FROM `shifts` WHERE name IN ('a', 'b'))
        UNION ALL 
            (SELECT *, UNIX_TIMESTAMP(end) as t, 0 as isStart FROM `shifts` WHERE name IN ('a', 'b'))
        ORDER BY t
    ) as sae
) AS final WHERE NOT isStart AND NOT fullyEnclosed;

基本思想是将表按时间排序两次，以便每个记录出现两次。一次为开始时间，一次为结束时间。然后，我使用用户定义的变量来跟踪状态，同时遍历记录，并仅返回“结束时间”记录，并根据重叠间隔调整开始时间和结束时间。

假设

唯一的假设是人 x 的区间不与同一人的另一个区间重叠。

行为

少数情况及其结果：

<  (   >   )
<  >   (   )

( < )  ( > )
( ) <  > ( )

<  (   )   >    // for this and similar cases only last part of interval is returned
       <   >

(   <  )   (   )  (  )  (   >   )  // like so
(   )                <  >   (   )

警告

我必须使用 unix 时间戳，因为我的 mysql 服务器无法在用户定义变量中保存的 DATETIME 与其他内容之间进行比较。

优点与优点缺点

它在没有任何连接的情况下单次完成它的工作，所以它应该花费 O(N) 时间。
它无法检索由人 B 的封闭区间截取的人 A 的区间的所有部分。
它使用 MySQL 特定功能。

Task

Return all the intervals of two different users except parts where they overlap.

Table and test data

CREATE TABLE IF NOT EXISTS `shifts` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(1) NOT NULL,
  `start` datetime NOT NULL,
  `end` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

INSERT INTO `shifts` (`id`, `name`, `start`, `end`) VALUES
(1, 'a', '2000-01-01 01:00:00', '2000-01-01 03:00:00'),
(2, 'a', '2000-01-01 06:00:00', '2000-01-01 07:30:00'),
(3, 'b', '2000-01-01 02:00:00', '2000-01-01 04:00:00'),
(4, 'b', '2000-01-01 05:00:00', '2000-01-01 07:00:00'),
(5, 'a', '2000-01-01 08:00:00', '2000-01-01 11:00:00'),
(6, 'b', '2000-01-01 09:00:00', '2000-01-01 10:00:00'),
(7, 'a', '2000-01-01 12:00:00', '2000-01-01 13:00:00'),
(8, 'b', '2000-01-01 14:00:00', '2000-01-01 14:30:00'),
(9, 'a', '2000-01-01 16:00:00', '2000-01-01 18:00:00'),
(10, 'a', '2000-01-01 19:00:00', '2000-01-01 21:00:00'),
(11, 'b', '2000-01-01 17:00:00', '2000-01-01 20:00:00');

Test results

        id  name    start           end
        1   a   2000-01-01 01:00:00 2000-01-01 02:00:00
        3   b   2000-01-01 03:00:00 2000-01-01 04:00:00
        4   b   2000-01-01 05:00:00 2000-01-01 06:00:00
        2   a   2000-01-01 07:00:00 2000-01-01 07:30:00
        5   a   2000-01-01 10:00:00 2000-01-01 11:00:00
        7   a   2000-01-01 12:00:00 2000-01-01 13:00:00
        8   b   2000-01-01 14:00:00 2000-01-01 14:30:00
        9   a   2000-01-01 16:00:00 2000-01-01 17:00:00
        11  b   2000-01-01 18:00:00 2000-01-01 19:00:00
        10  a   2000-01-01 20:00:00 2000-01-01 21:00:00

Solution

I used feature of MySQL called User-Defined Variables to achieve the goal with the following query:

SET @inA=0, @inB=0, @lastAstart = 0, @lastBstart = 0, @lastAend = 0, @lastBend = 0;
SELECT id,name,start,end FROM (
    SELECT 
        id,name,
        IF(name='a',
          IF(UNIX_TIMESTAMP(start) > @lastBend, start, FROM_UNIXTIME(@lastBend)),
          IF(UNIX_TIMESTAMP(start) > @lastAend, start, FROM_UNIXTIME(@lastAend))
        ) as start,
        IF(name='a',
          IF(@inB,FROM_UNIXTIME(@lastBstart),end),
          IF(@inA,FROM_UNIXTIME(@lastAstart),end)
        )  as end,
        IF(name='a',
          IF(@inB AND (@lastBstart < @lastAstart), 1, 0),
          IF(@inA AND (@lastAstart < @lastBstart), 1, 0)
        ) as fullyEnclosed,
          isStart,
          IF(name='a',@inA:=isStart,0), 
          IF(name='b',@inB:=isStart,0), 
          IF(name='a',IF(isStart,@lastAstart:=t,@lastAend:=t),0), 
          IF(name='b',IF(isStart,@lastBstart:=t,@lastBend:=t),0)
    FROM (
            (SELECT *, UNIX_TIMESTAMP(start) as t, 1 as isStart FROM `shifts` WHERE name IN ('a', 'b'))
        UNION ALL 
            (SELECT *, UNIX_TIMESTAMP(end) as t, 0 as isStart FROM `shifts` WHERE name IN ('a', 'b'))
        ORDER BY t
    ) as sae
) AS final WHERE NOT isStart AND NOT fullyEnclosed;

Basic idea is to list the table twice sorted by time so that every record appear twice. Once for the start time and then for the end time. Then I'm using user-defined variables to keep track of the state while traversing records and return only 'end time' records with start time and end time adjusted for overlapping intervals.

Assumptions

Only assumption is that no interval of person x does overlap with another interval of the same person.

Behavior

Few cases, and their results:

<  (   >   )
<  >   (   )

( < )  ( > )
( ) <  > ( )

<  (   )   >    // for this and similar cases only last part of interval is returned
       <   >

(   <  )   (   )  (  )  (   >   )  // like so
(   )                <  >   (   )

Caveats

I must have used unix timestamp since it my mysql server could not make comparison between DATETIME kept in user-defined variable and something else.

Pros & Cons

It does it's job in single pass without any joins so it should take O(N) time.
It cannot retrieve all the parts of interval of person A cut out by enclosed intervals of person B.
It uses MySQL specific functionality.

回复收藏 0 原文

感情废物 2024-08-10 04:00:08

作为参考，我最近使用了一段代码片段。它可用于检查重叠的日期范围。它是用 Ruby on Rails 编写的，但其思想（SQL 语句）可以轻松翻译为其他语言）

  class Absence
    named_scope :overlaps, lambda { |start, ende| { 
      :conditions =>
          ["   absences.start_date BETWEEN :start AND :end " +
           "OR absences.end_date   BETWEEN :start AND :end " +
           "OR :start BETWEEN absences.start_date AND absences.end_date " +
           "OR :end BETWEEN absences.start_date AND absences.end_date ",
              {:start => start, :end => ende } ]
      }}
  end

与命名作用域一样，该作用域可以与任何其他作用域结合重用。

user = User.find(...)
today = Date.today
confirmed_absences = user.absences.confirmed.overlaps(today.beginning_of_month, today.end_of_month).count

For the reference a code snipped which I used recently. It can be used to check for overlapping date ranges. It is written in Ruby on Rails, but the idea (the SQL Statement) can easily be translated to other languages)

  class Absence
    named_scope :overlaps, lambda { |start, ende| { 
      :conditions =>
          ["   absences.start_date BETWEEN :start AND :end " +
           "OR absences.end_date   BETWEEN :start AND :end " +
           "OR :start BETWEEN absences.start_date AND absences.end_date " +
           "OR :end BETWEEN absences.start_date AND absences.end_date ",
              {:start => start, :end => ende } ]
      }}
  end

As usual with named scopes this scope can be reused in combination with any other scopes.

user = User.find(...)
today = Date.today
confirmed_absences = user.absences.confirmed.overlaps(today.beginning_of_month, today.end_of_month).count

回复收藏 0 原文

~没有更多了~

关于作者

抠脚大汉

暂无简介

0 文章

0 评论

25 人气

关注发私信

友情链接

文江博客

MySQL中的日期字段，查找所有不重叠的行并仅返回差异

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（4）

任务

表和测试数据

测试结果

解决方案

假设

行为

警告

优点与优点缺点

Task

Table and test data

Test results

Solution

Assumptions

Behavior

Caveats

Pros & Cons

关于作者

相关话题

热门标签

推荐作者

Gabu-gabumon

qq_CgiN62

荔枝明

赏烟花じ飞满天

独守阴晴ぅ圆缺

¤→小豸慧

友情链接

MySQL中的日期字段，查找所有不重叠的行并仅返回差异

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（4）

任务

表和测试数据

测试结果

解决方案

假设

行为

警告

优点与优点缺点

Task

Table and test data

Test results

Solution

Assumptions

Behavior

Caveats

Pros & Cons

关于作者

相关话题

热门标签

推荐作者

Gabu-gabumon

qq_CgiN62

荔枝明

赏烟花じ飞满天

独守阴晴ぅ圆缺

¤→小豸慧

友情链接

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。