在 Mongo 上按日期字符串(升序)排序 [英] Sort by date string (ascending) on Mongo

问题描述

我有一个日期列的数据库，格式为 dd/mm/yyyy，我想按日期升序排序.

I have a database with a date column in the format dd/mm/yyyy and I'd like to sort by the date in ascending order.

$cursor = $collection->find($filter)->sort(array('date' => 1, 'tripID' => 1));

日期是一个字符串，我也在对 tripID 进行过滤，但该方面正在发挥作用.问题是目前我得到:

The date is a string and I'm also filtering on the tripID but that aspect is working. The problem is that at present I'm getting:

01/01/2014
01/02/2014
02/01/2014
02/02/2014

我想要的是:

01/01/2014
02/01/2014
01/02/2014
02/02/2014

是否可以使用查询来实现这一点，还是需要在应用程序中完成?

Is it possible to achieve this using the query or would it need to be done in the application?

推荐答案

假设我们有您问题中给出的列表

Say we have the list given in your question

> db.dates.insertMany([{ "date": "01/01/2014" },
    { "date": "01/02/2014" },
    { "date": "02/01/2014" },
    { "date": "02/02/2014" }])
{
        "acknowledged" : true,
        "insertedIds" : [
                ObjectId("5a314eae330cc13d0c9b10c4"),
                ObjectId("5a314eae330cc13d0c9b10c5"),
                ObjectId("5a314eae330cc13d0c9b10c6"),
                ObjectId("5a314eae330cc13d0c9b10c7")
        ]
}

在 MongoDB 3.6 中，我们可以使用聚合框架并使用 $dateFromString (https://docs.mongodb.com/manual/reference/operator/aggregation/dateFromString/) 管道运算符将字符串日期转换为日期，然后对值进行排序:

Within MongoDB 3.6 we can use aggregation framework and use the $dateFromString (https://docs.mongodb.com/manual/reference/operator/aggregation/dateFromString/) pipeline operator to convert the string date in to a date and then sort the value:

> db.dates.aggregate([
    { "$project" : { "date" : { "$dateFromString" : { "dateString" : "$date"} } } },
    { "$sort" : { "date" : 1 } }
    ])
{ "_id" : ObjectId("5a314eae330cc13d0c9b10c4"), "date" : ISODate("2014-01-01T00:00:00Z") }
{ "_id" : ObjectId("5a314eae330cc13d0c9b10c5"), "date" : ISODate("2014-01-02T00:00:00Z") }
{ "_id" : ObjectId("5a314eae330cc13d0c9b10c6"), "date" : ISODate("2014-02-01T00:00:00Z") }
{ "_id" : ObjectId("5a314eae330cc13d0c9b10c7"), "date" : ISODate("2014-02-02T00:00:00Z") }

在 MongoDB 3.6 之前，有一种解决方法可以将您的字符串转换为词法字符串日期:

Pior to MongoDB 3.6 there is a workaround to convert your string to a lexicial string date:

> db.dates.aggregate([
    { "$project" : {
        "date" : {
            "$let" : {
                "vars" : { "parts":{ "$split" : [ "$date", "/" ] } },
                "in" : {
                    "$concat" : [
                        { "$arrayElemAt" : [ "$$parts" , 2 ] },
                        "-",
                        { "$arrayElemAt" : [ "$$parts", 1 ] },
                        "-",
                        { "$arrayElemAt" : [ "$$parts", 0 ] } ]
                    }
                }
            }
        }
    },
    { "$sort" : { "date" : 1 } }
])

{ "_id" : ObjectId("5a314eae330cc13d0c9b10c4"), "date" : "2014-01-01" }
{ "_id" : ObjectId("5a314eae330cc13d0c9b10c6"), "date" : "2014-01-02" }
{ "_id" : ObjectId("5a314eae330cc13d0c9b10c5"), "date" : "2014-02-01" }
{ "_id" : ObjectId("5a314eae330cc13d0c9b10c7"), "date" : "2014-02-02" }

我知道这是在 javascript 中，你提到它在 php 中，但实际上是相同的，请查看 php 文档(http://php.net/manual/en/mongocollection.aggregate.php)

I know this is in javascript and you mentioned it was in php, but it's pratically the same, checkout the php documentation (http://php.net/manual/en/mongocollection.aggregate.php)

这篇关于在 Mongo 上按日期字符串(升序)排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！