如何在MySQL中检查日期范围内的任何日期是否介于表中的日期之间 [英] How to check in MySQL if any date in a range of dates falls between dates held in a table

问题描述

我目前有一张表格，其中包含员工休假的一系列日期:

I currently have a table containing a series of dates that staff are taking holidays:

DROP TABLE IF EXISTS `holidays`;

CREATE TABLE `holidays` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `staffid` int(11) DEFAULT NULL,
  `startdate` datetime DEFAULT NULL,
  `enddate` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `holidays` (`id`, `staffid`, `startdate`, `enddate`)
VALUES
    (1,1,'2016-01-25 12:45:00','2016-01-29 12:30:00'),
    (2,1,'2016-01-18 09:00:00','2016-01-18 12:45:00'),
    (3,1,'2016-02-29 09:00:00','2016-03-04 16:30:00'),
    (4,1,'2016-01-19 12:30:00','2016-01-19 15:00:00');

我想做的是检查此工作周中的任何日期(mon-fri)是否介于表中的开始日期和结束日期之间.这似乎需要循环浏览本工作周中的每个日期，但是我不知道在MySQL中是否可行.我宁愿不使用PHP循环发送多个查询，因为那样可能最终会很慢.

What I want to do is check whether any date in this working week (mon-fri) falls between any of the start and enddates in the table. This seems to be to require looping through each of the dates in this working week, but I do not know if this is possible in MySQL. I'd rather not loop through using PHP to send off multiple queries as that would probably end up being slow.

有什么建议吗?

推荐答案

马丁·施耐德的上述评论有所帮助我得到答案.它不是MySQL特有的，而是解决问题的逻辑方法，因此，我将在下面使用的查询包括在内，以帮助将来遇到此问题的任何人:

Martin Schneider's comment above helped me arrive at the answer. It wasn't MySQL specific but rather the logical approach to solving the problem, so I am including the query I am using below to help anyone who comes across this in the future:

SELECT * FROM holiday 
WHERE staffid = {staff id} 
AND '{date on monday}' <= DATE(enddate) 
AND '{date on friday}' >= DATE(startdate)

根据需要替换大括号内的文本.

Replace the text inside the curly brackets as required.

这篇关于如何在MySQL中检查日期范围内的任何日期是否介于表中的日期之间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！