在 Mongo 上按日期字符串(升序)排序

本文介绍了在 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 (docs.mongodb/manual/reference/operator/aggregation/dateFromString/) 管道运算符将字符串日期转换为日期，然后对值进行排序:

Within MongoDB 3.6 we can use aggregation framework and use the $dateFromString (docs.mongodb/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 文档(php/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 (php/manual/en/mongocollection.aggregate.php)