MongoDB查找日期范围(如果与其他日期重叠) [英] MongoDB find date range if overlap with other dates
问题描述
我有许多文档,其中的架构如下所示,每个文档都包含(开始日期,结束日期),如以下架构所示.在保存新文档之前,是否有一种简单的方法可以知道新文档的开始日期,结束日期是否与先前保存的文档开始日期,结束日期重叠?谢谢
I have many documents with the schema shown below each containing (start date, enddate) as shown in the schema below. Is there a simple way to know before saving a new document if the new document startdate, enddate will overlap with previously saved documents startdate, enddate? Thanks
{
"title" : "",
"owner" : "",
"notes" : "",
"startdate" : "",
"enddate" : ""
}
以下是当前保存的唯一文档:
Below is the only document currently saved:
Document.(anonymous function) {_id: "FADnPAxRu4Ps5hkLz",
title: "Round A",
customerid: "QDGvBQhS6vYgZtnRr",
startdate: "11/21/2014 7:25 AM"…}
_id: "FADnPAxRu4Ps5hkLz"customerid: "QDGvBQhS6vYgZtnRr"
enddate: "11/30/2014 6:09 AM"
startdate: "11/21/2014 7:25 AM"
title: "Round A"__proto__: Document.(anonymous function)
当我尝试在上述文档上执行以下任何查询时,即使此处有明显的重叠,它也不返回任何内容.
When I try executing any of the following query on the above document it return nothing, even though there is an obvious overlap here.
db.Projects.find({'startdate': {$lt: '11/25/2014 6:26 PM'}, 'enddate': {$gt: '11/19/2014 6:26 PM'}}, {sort:{time: -1}});
db.Projects.find({'startdate': {$lt: '11/30/2014 6:26 PM'}, 'enddate': {$gt: '11/21/2014 6:26 PM'}}, {sort:{time: -1}});
推荐答案
下图中的这4种情况可以说明时间重叠,其中S/E是新文档的开始日期/结束日期,而S'/E'是新文档的开始日期/结束日期.是任何现有文档的开始日期/结束日期:
The time overlap can be illustrated with these 4 cases in the figure below, where S/E is startdate/enddate of the new document and S'/E' is startdate/enddate of any existing document:
S' E'
|-------------------|
S E
|****************|
S' E'
|-----------|
S' E'
|-----------|
S' E'
|-----------|
在4种情况下,我们有S'<E
和E'>S
.查找所有时间重叠的文档的查询可以是:
In 4 cases we have S'<E
and E'>S
. The query to find all documents with overlapped time can be:
db.collection.find({"startdate": {"$lt": E}, "enddate": {"$gt": S}})
您的开始日期和结束日期为字符串格式,并且没有按词法排序,因此不能使用"$ gt"和"$ lt"进行比较.您应该将它们转换为日期类型:
Your startdate and enddate are in string format, and not lexically ordered, hence can't use "$gt" and "$lt" for comparison. You should convert them to Date type:
db.collection.find().forEach(
function (e) {
// convert date if it is a string
if (typeof e.startdate === 'string') {
e.startdate = new Date(e.startdate);
}
if (typeof e.enddate === 'string') {
e.enddate = new Date(e.enddate);
}
// save the updated document
db.collection.save(e);
}
)
最终查询将是:
db.collection.find({"startdate": {"$lt": new Date("E")}, "enddate": {"$gt": new Date("S")}})
这篇关于MongoDB查找日期范围(如果与其他日期重叠)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!