MongoDB sort by date

by

In this MongoDB tutorial, we will discuss and learn more about how to sort by date in MongoDB and how we can sort by date differently.

MongoDB sort by date

A few days back, I was working on a blog page and recognized that the post was from old to the new order, which is not a good thing while reading the blogs. They should be in the opposite direction (display latest posts first) so that we can easily read the blogs. As in MongoDB fetching sorting are 2 main operations, we will discuss and learn more about this issue in this article.

In MongoDB, there is a sort() method and $sort aggregation which are used to solve the problem. With this help, we can sort the data in ascending or descending order.

Example:

>db.posts.find().pretty()
{
	"_id" : 1,
	"title" : "MongoDB",
	"body" : "MongoDB is an open-source database",
	"date" : "2021-01-01T00:00:00.000Z",
        "Country" : "United Kingdom"
}
{
	"_id" : 2,
	"title" : "MySQL",
	"body" : "MySQL is a popular open-source relational database management system",
	"date" : ISODate("2020-01-01T00:00:00Z"),
        "Country" : "United States of America"
}
{
	"_id" : 3,
	"title" : "SQL",
	"body" : "SQL is a database computer language",
	"date" : ISODate("2021-01-01T00:00:00Z"),
        "Country" : "New Zealand"
}

Here, I inserted some documents into the posts collection and we will sort them in ascending order using the date field so that earlier dates come first. 

>db.posts.find().sort({ date: 1 }).pretty()
{
	"_id" : 1,
	"title" : "MongoDB",
	"body" : "MongoDB is an open-source database",
	"date" : "2021-01-01T00:00:00.000Z",
        "Country" : "United Kingdom"
}
{
	"_id" : 2,
	"title" : "MySQL",
	"body" : "MySQL is a popular open-source relational database management system",
	"date" : ISODate("2020-01-01T00:00:00Z"),
        "Country" : "United States of America"
}
{
	"_id" : 3,
	"title" : "SQL",
	"body" : "SQL is a database computer language",
	"date" : ISODate("2021-01-01T00:00:00Z"),
        "Country" : "New Zealand"
}

Note that after sorting it. The first document contains a date string instead of a date object so the date string comes first even though its date is later than the date in document 2.

Read: MongoDB sort by field

MongoDB sort by date descending order

In MongoDB, for sorting the date in descending order you have to use the sort() method and specify the parameter like date field name and direction(ascending or descending order).

sort(date : 1)  #For ascending order
sort(date : -1) #For descending order

Example:

> db.product.find().pretty()
{
        "_id" : 1,
        "item" : {
                "name" : "HighLander",
                "type" : "toyota"
        },
        "price" : 2.8
        "date" : "2021-01-01T00:00:00.000Z"
}
{
        "_id" : 2,
        "item" : {
                "name" : "Swift",
                "type" : "suzuki"
        },
        "price" : 3.9
        "date" : ISODate("2020-01-01T00:00:00Z")
}
{
        "_id" : 3,
        "item" : {
                "name" : "Mirage G4",
                "type" : "mitsubishi"
        },
        "price" : 3.2
        "date" : ISODate("2021-01-01T00:00:00Z")
}

These are the documents we have inserted into the product database.

Now we will sort the documents in descending order

>db.product.find().sort(date:-1).pretty()
{
        "_id" : 3,
        "item" : {
                "name" : "Mirage G4",
                "type" : "mitsubishi"
        },
        "price" : 3.2
        "date" : ISODate("2021-01-01T00:00:00Z")
}
{
        "_id" : 2,
        "item" : {
                "name" : "Swift",
                "type" : "suzuki"
        },
        "price" : 3.9
        "date" : ISODate("2020-01-01T00:00:00Z")
}
{
        "_id" : 1,
        "item" : {
                "name" : "HighLander",
                "type" : "toyota"
        },
        "price" : 2.8
        "date" : "2021-01-01T00:00:00.000Z"
}

Read: Create tables in MongoDB

MongoDB sort by date string

In MongoDB, There are two different methods that help you to store the date/time in MongoDB.

In the first method, you use the date object to store the date in the documents.

Syntax:

new Date();

Example:

>db.product.insertOne({ "productId" : 101, "productDeliveryDateTime": new Date() });
{
        "acknowledged" : true,
        "insertedId" : ObjectId("611c9e39e1fdc428cf238757")
}

Here, the product is the collection name and inserted date field using the Date() object.

In the second method, you have to use ISODate() to store the date field in the database. ISO stands for International Organization for Standardization.

Syntax:

new ISODate();

Example:

>db.product.insertOne({ "productId" : 102, "productDeliveryDateTime": new ISODate() });
{
        "acknowledged" : true,
        "insertedId" : ObjectId("611c9e39e1fdc428cf238758")
}

Here, we inserted the date field into the product collection using ISODate(). You can display all the documents using the find() method:

>db.product.find().pretty()
{
        "_id" : ObjectId("611c9e39e1fdc428cf238757"),
        "productId" : 101,
        "productDeliveryDateTime" : ISODate("2021-08-18T05:44:25.081Z")
}
{
        "_id" : ObjectId("611ca108e1fdc428cf238758"),
        "productId" : 102,
        "productDeliveryDateTime" : ISODate("2021-08-18T05:56:24.144Z")
}

Note that the Date object is the best way to store the date/time.

MongoDB sort by date string means if documents contain both the Date() string method and the ISODate() method, sorting in the direction(ascending or descending order).

Remark that after sorting it display the output as the first document would be the date string instead of a date object, this comes first even though its date is later than the date in document 2.

Example:

> db.language.find().pretty()
{
        "_id" : 1,
        "item" : {
                "name" : "Python",
                "type" : "Very high level dynamic data types"
        },
        "price" : 2.8,
        "date" : "2021-01-10T00:00:00.000Z"
}
{
        "_id" : 2,
        "item" : {
                "name" : "C++",
                "type" : "High-level computer programming language"
        },
        "price" : 3.9,
        "date" : ISODate("2020-12-01T00:00:00Z")
}
{
        "_id" : 3,
        "item" : {
                "name" : "Java",
                "type" : "Java is a high-level, class-based, object-oriented programming language"
        },
        "price" : 3.2,
        "date" : ISODate("2021-08-01T00:00:00Z")
}

Here, these are some documents we inserted into the language collection. You can also sort them by using the sort() method.

> db.language.find().sort(date:1).pretty()
{
        "_id" : 1,
        "item" : {
                "name" : "Python",
                "type" : "Very high level dynamic data types"
        },
        "price" : 2.8,
        "date" : "2021-01-10T00:00:00.000Z"
}
{
        "_id" : 3,
        "item" : {
                "name" : "Java",
                "type" : "Java is a high-level, class-based, object-oriented programming language"
        },
        "price" : 3.2,
        "date" : ISODate("2021-08-01T00:00:00Z")
}
{
        "_id" : 2,
        "item" : {
                "name" : "C++",
                "type" : "High-level computer programming language"
        },
        "price" : 3.9,
        "date" : ISODate("2020-12-01T00:00:00Z")
}

You can see after sorting the data in ascending order using the date field. The date string field comes in the first place.

Note that the Date object is the best way to store the date/time.

Read: Pros and cons of MongoDB

MongoDB sort by date not working

There could be any reason MongoDB does not work but you have to remember some of the steps so that you can easily work with sort by date in MongoDB.

  • Always remember that with help you are storing the data into the collection.
  • There are two ways to store the date field inside the document.
  • Date() string and ISODate() data type are use to store date in documents.
  • When you sort a date field using the sort() method, check which data type you used.

Example:

>db.document.insertOne({ "productId" : 1001, "productDeliveryDateTime": new Date() });
{
        "acknowledged" : true,
        "insertedId" : ObjectId("611c9e39e1fdc428cf238802")
}

In this example, we insert the Date using the Date() string and the collection name is the document. Same way using ISODate() you can insert a date in the document.

Read: How does MongoDB create collection

MongoDB sort by date using compass

In MongoDB, sort by date using compass then you have to follow some steps with that help you can easily sort the data.

  • Open MongoDB Compass
  • Open the database in which you created the collection and stored data
mongodb sort by date field
  • Click on the Aggregation that is on the right side of the documents.
mongodb sort by date string
  • Click on ADD STAGE and choose sort from the drop down list.
  • Write the field name by which you want to sort the documents and write sortOrder.
# sort order
1 for ascending order
-1 for descending order
mongodb sort by date descending

Here, you can see productDeliveryDateTime is the field name and -1 is used for sorting the documents in descending order.

You can see on the bottom-right side of the snapshot, we successfully sort the documents in descending order.

Read: MongoDB backup and restore

MongoDB sort by date using an array

In MongoDB, you have to use the sort() method for sort by date using an array. In the sort() method you have to specify the date field by which you want to sort and direction (ascending or descending order).

Example:

Insert some data into the collection in an array format

> db.student.insertMany([
      { 
        "name"      : "Tom",
        "age"       : 21,
        "timestamp" : new ISODate("2021-04-01" )
      },
      { 
        "name"      : "Emma",
        "age"       : 25,
        "timestamp" : new ISODate("2021-10-31" )
      },
      {
        "name"      : "John",
        "age"       : 29,
        "timestamp" : new ISODate("2021-05-02")
      }
]) 
{
        "acknowledged" : true,
        "insertedIds" : [
                ObjectId("611df0e76cc7e05e5e7fe74b"),
                ObjectId("611df0e76cc7e05e5e7fe74c"),
                ObjectId("611df0e76cc7e05e5e7fe74d")
        ]
}

Here, the student is the collection name and for inserting the date we use the ISODate() function. You can also use the Date() string function as well.

> db.student.find().sort({timestamp:1}).pretty()

{
        "_id" : ObjectId("611df0e76cc7e05e5e7fe74b"),
        "name" : "Tom",
        "age" : 21,
        "timestamp" : ISODate("2021-04-01T00:00:00Z")
}
{
        "_id" : ObjectId("611df0e76cc7e05e5e7fe74d"),
        "name" : "John",
        "age" : 29,
        "timestamp" : ISODate("2021-05-02T00:00:00Z")
}
{
        "_id" : ObjectId("611df0e76cc7e05e5e7fe74c"),
        "name" : "Emma",
        "age" : 25,
        "timestamp" : ISODate("2021-10-31T00:00:00Z")
}

Now you can see all the documents are sorted in ascending order and we also use the pretty() method. This helps us to display the result in an easy-to-read format.

Read: How to create new database in MongoDB

MongoDB sort by date using python

In MongoDB, For sorting the documents by date using python you have to use the sort() method. In this method, you have to define the date field name and direction(ascending or descending order).

sort("date_field_name":1)    #For ascending order
sort("date_field_name":-1)   #For descending order

Example:

In this example, we will sort the documents as per the date field using python.

import pymongo

myclient = pymongo.MongoClient("mongodb://127.0.0.1:27017")
mydb = myclient["Company"]
mycol = mydb["Employee"]

mydoc = mycol.find().sort("productDeliveryDateTime", -1)

for x in mydoc:
  print(x)

First, we have to import the pymongo library for making the connection between MongoDB and python. After that, the class MongoClient enables you to make the connection with the MongoDB server.

Here Company and Employee are the database and collection respectively and now you have to store it into a variable mydb and mycol respectively. You can use any name for the variable.

You have to again declare a variable mydoc and define sort() method condition (Date field productDeliveryDateTime and -1 for sort in descending order) after that with the help of for loop print the values. By default, MongoDB is run on 27017 port.

{
        "_id" : ObjectId("611ccff5e1fdc428cf238759"),
        "productId" : 103,
        "productDeliveryDateTime" : ISODate("2021-08-18T09:16:37.819Z")
}
{
        "_id" : ObjectId("611ca108e1fdc428cf238758"),
        "productId" : 102,
        "productDeliveryDateTime" : ISODate("2021-08-18T05:56:24.144Z")
}
{
        "_id" : ObjectId("611c9e39e1fdc428cf238757"),
        "productId" : 101,
        "productDeliveryDateTime" : ISODate("2021-08-18T05:44:25.081Z")
}

Here you can see all the documents printed in descending order using python.

You may also like reading the following articles.

In this tutorial, we have learned “MongoDB sort by date” using different approaches with examples.

Top 200 SQL Server Interview Questions and Answers

Free PDF On Top 200 SQL Server Interview Questions And Answers

Download A 40 pages PDF And Learn Now.