比较mongo find方法中的2个日期 [英] Compare 2 dates in mongo find method

问题描述

我有包含 last_active 日期和创建日期的 mongo 文档.我想搜索 last_active 的 day 不等于 created 的 day 的所有文档，但我不知道如何编写查询.

I have mongo documents containing a last_active date and a created date. I would like to search for all documents where the day of last_active is not equal to the day of created, but I have no clue how to write the query.

在 MySQL 中，我会这样写:

In MySQL I would write it like that:

WHERE DATE_FORMAT(created, '%Y-%m-%d') != DATE_FORMAT(last_active, '%Y-%m-%d')

推荐答案

对于 MongoDB 3.6 及更新版本:

$expr 运算符允许在查询语言中使用聚合表达式，因此您可以利用 $dateToString 用于转换日期字段的运算符:

The $expr operator allows the use of aggregation expressions within the query language, thus you can leverage the use of $dateToString operator to transform the date field:

db.test.find({ 
    "$expr": { 
        "$ne": [ 
             { "$dateToString": { "format": "%Y-%m-%d", "date": "$created" } }, 
             { "$dateToString": { "format": "%Y-%m-%d", "date": "$last_active" } }
        ] 
    } 
})

或使用聚合框架与$match 管道

or using aggregation framework with $match pipeline

db.test.aggregate([
    { "$match": { 
        "$expr": { 
            "$ne": [ 
                { "$dateToString": { "format": "%Y-%m-%d", "date": "$created" } }, 
                { "$dateToString": { "format": "%Y-%m-%d", "date": "$last_active" } }
            ] 
        } 
    } }
])

<小时>

对于 MongoDB 3.0+:

您还可以将聚合框架与 $redact 管道运算符，允许您使用 $cond 运算符并使用特殊操作 $$KEEP保留"逻辑条件为真或 $$PRUNE 以删除"条件为假的文档.

You can also use the aggregation framework with the $redact pipeline operator that allows you to process the logical condition with the $cond operator and uses the special operations $$KEEP to "keep" the document where the logical condition is true or $$PRUNE to "remove" the document where the condition was false.

考虑运行以下聚合操作来演示上述概念:

Consider running the following aggregate operation which demonstrates the above concept:

db.test.aggregate([
    {
        "$redact": {
            "$cond": [
                { 
                    "$ne": [ 
                        { "$dateToString": { "format": "%Y-%m-%d", "date": "$created" } }, 
                        { "$dateToString": { "format": "%Y-%m-%d", "date": "$last_active" } }
                    ] 
                },
                "$$KEEP",
                "$$PRUNE"
            ]
        }
    }
])

此操作类似于具有 $project 管道选择集合中的字段并创建一个新字段，该字段保存逻辑条件查询的结果，然后是后续的$match，除了 $redact 使用更高效的单个流水线阶段:

This operation is similar to having a $project pipeline that selects the fields in the collection and creates a new field that holds the result from the logical condition query and then a subsequent $match, except that $redact uses a single pipeline stage which is more efficient:

db.test.aggregate([
    {
        "$project": { 
            "created": 1, 
            "last_active": 1,
            "sameDay": { 
                "$cond": [ 
                    { 
                        "$eq": [ 
                            {"$substr" : ["$last_active",0, 10]}, 
                            {"$substr" : ["$created",0, 10]}
                        ] 
                    }, true, false 
                ]
            } 
        } 
    },
    { "$match": { "sameDay": false } }
])

0r

db.test.aggregate([
    {
        "$project": { 
            "created": 1, 
            "last_active": 1,
            "sameDay": { 
                "$cond": [ 
                    { 
                        "$eq": [ 
                            { "$dateToString": { "format": "%Y-%m-%d", "date": "$created" } }, 
                            { "$dateToString": { "format": "%Y-%m-%d", "date": "$last_active" } }
                        ] 
                    }, true, false 
                ]
            } 
        } 
    },
    { "$match": { "sameDay": false } }
])

<小时>

另一种方法是使用 $find() 方法中的 where 运算符，但请注意，由于使用 $where 单独需要表扫描和数据库为集合中的每个文档执行 JavaScript 表达式或函数，因此如果可以，请结合索引查询，因为当您使用标准 MongoDB 运算符(例如 $gt, $in):

---

Another approach would be to use the $where operator in your find() method but note that the query will be fairly slow since using $where alone requires a table scan and the database executes the JavaScript expression or function for each document in the collection, so combine with indexed queries if you can as query performance also improves when you express it using the standard MongoDB operators (e.g., $gt, $in):

db.test.find({ 
   "$where": function() { 
       return this.created.getDate() !== this.last_active.getDate() 
   } 
});

或更紧凑:

db.test.find({ "$where": "this.created.getDate() !== this.last_active.getDate()" });

<小时>

随着输入:

---

With the input:

/* 0 */
{
    "_id" : 1,
    "created" : ISODate("2014-12-19T06:01:17.171Z"),
    "last_active" : ISODate("2014-12-21T15:38:13.842Z")
}

/* 1 */
{
    "_id" : 2,
    "created" : ISODate("2015-07-06T12:17:32.084Z"),
    "last_active" : ISODate("2015-07-06T18:07:08.145Z")
}

/* 2 */
{
    "_id" : 3,
    "created" : ISODate("2015-07-06T06:01:17.171Z"),
    "last_active" : ISODate("2015-07-07T10:04:30.921Z")
}

/* 3 */
{
    "_id" : 4,
    "created" : ISODate("2015-07-06T06:01:17.171Z"),
    "last_active" : ISODate("2015-07-06T09:47:44.186Z")
}

/* 4 */
{
    "_id" : 5,
    "created" : ISODate("2013-12-19T06:01:17.171Z"),
    "last_active" : ISODate("2014-01-20T13:21:37.427Z")
}

聚合返回:

/* 0 */
{
    "result" : [ 
        {
            "_id" : 1,
            "created" : ISODate("2014-12-19T06:01:17.171Z"),
            "last_active" : ISODate("2014-12-21T15:38:13.842Z"),
            "sameDay" : false
        }, 
        {
            "_id" : 3,
            "created" : ISODate("2015-07-06T06:01:17.171Z"),
            "last_active" : ISODate("2015-07-07T10:04:30.921Z"),
            "sameDay" : false
        }, 
        {
            "_id" : 5,
            "created" : ISODate("2013-12-19T06:01:17.171Z"),
            "last_active" : ISODate("2014-01-20T13:21:37.427Z"),
            "sameDay" : false
        }
    ],
    "ok" : 1
}

这篇关于比较mongo find方法中的2个日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！