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

问题描述

我有mongo文档，其中包含last_active日期和创建日期.我想搜索last_active的 day 与创建的 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" } }
        ] 
    } 
})

或将聚合框架与 管道

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 +:

您还可以将聚合框架与 $$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 管道，该管道选择集合中的字段并创建一个新字段，该字段保存逻辑条件查询以及随后的

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 } }
])

---

另一种方法是使用 $where 运算符，但请注意，由于使用

---

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屋！