mongodb:查询两个日期字段之间的时间段 [英] mongodb: query for the time period between two date fields

问题描述

如果我的mongoDB中保存了以下架构的文档:

If I have documents in the following schema saved in my mongoDB:

{
  createdDate: Date,
  lastUpdate: Date
}

可以查询从创建到最后一次更新之间的时间段为例如大于一天?

is it possible to query for documents where the period of time between creation and the last update is e.g. greater than one day?

推荐答案

最佳选择是使用 $redact 聚合管道阶段:

Best option is to use the $redact aggregation pipeline stage:

db.collection.aggregate([
    { "$redact": {
        "$cond": {
            "if": {
                "$gt": [
                    { "$subtract": [ "$lastUpdate", "$createdDate" ] },
                    1000 * 60 * 60 * 24
                ]
            },
            "then": "$$KEEP",
            "else": "$$PRUNE"
        }
    }}
])

因此，您正在查看毫秒值，因为该差值大于一天的毫秒值. $subtract 进行差值的数学计算，当两个减去日期，返回毫秒数.

So you are looking at the milliseconds value from the difference being greater than the milliseconds value for one day. The $subtract does the math for the difference, and when two dates are subtracted the difference in miliseconds is returned.

$redact运算符将逻辑表达式用作"if"，并且在条件为true的情况下，它将在"then"中采取操作，以对文档进行$$KEEP.在false的位置，然后使用$$PRUNE从结果中删除该文档.

The $redact operator takes a logical expression as "if", and where that condition is true it takes the action in "then" which is to $$KEEP the document. Where it is false then the document is removed from results with $$PRUNE.

请注意，由于这是逻辑条件，而不是设置值或值范围，因此不使用索引".

Note that since this is a logical condition and not a set value or a range of values, then an "index" is not used.

由于聚合管道中的操作是本机编码的，因此这是您可以获得的这样一条语句的最快执行.

Since the operations in the aggregation pipeline are natively coded, this is the fastest execution of such a statement that you can get though.

替代方法是使用 $where 进行JavaScript评估.这需要一个JavaScript函数表达式，该表达式需要类似地返回true或false值.在外壳中，您可以这样简写:

The alternate is JavaScript evaluation with $where. This takes a JavaScript function expression that needs to similarly return a true or false value. In the shell you can shorthand like this:

db.collection.find(function() {
    return ( this.lastUpdate.valueOf() - this.createdDate.valueOf() )
       > ( 1000 * 60 * 60 * 24 );
})

相同，只是JavaScript评估需要解释，并且运行速度比.aggregate()等效语言慢得多.同样，这种类型的表达式不能使用索引来优化性能.

Same thing, except that JavaScript evalution requires interpretation and will run much slower than the .aggregate() equivalent. By the same token, this type of expression cannot use an index to optimize performance.

对于最佳结果，将差异存储在文档中.然后，您可以直接在该属性上直接进行查询，当然您也可以为其建立索引.

For the best results, store the difference in the document. Then you can simply query directly on that property, and of course you can index it as well.

这篇关于mongodb:查询两个日期字段之间的时间段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！