Overview

One of the most common pain points in querying mongodb is datetime operations. Even if you know the query operators, typing the query will raise doubts. In this blog I will try to help you out on date queries in MongoDB.

MongoDB Query Example (less than)

Assume that you have the following record stored in MongoDB Collection and you like to query those records and get records which are less than 22 Feb 2022. See the below document for record stored in DB

_id:6213291e5abd9c9b33c50ad5
StudentId:1
Name:"Pallav Lalitv"
EnrolmentDate:2022-02-01T00:00:00.000+00:00
_id:621329925abd9c9b33c50ad6

StudentId:2
Name:"Rohan Niraj"
EnrolmentDate:2022-01-01T00:00:00.000+00:00
_id:621329b35abd9c9b33c50ad7

StudentId:3
Name:"Aswathi Savitri"
EnrolmentDate:2022-03-01T00:00:00.000+00:00

We are going to use ISODate Attribute for Querying records, This will allow query to use any standard comparison operator (e.g., =, >, <, !=) for date search.

Query MongoDB ISODate Attribute

Get record of date less than “2022-02-02T08:30.00.000Z”

Using ISODate syntax ( Table: DB.students )

{
     "EnrolmentDate" : {
         "$lt" : new ISODate("2022-02-02T08:30:00.000Z")
     }
}

Output:

_id:6213291e5abd9c9b33c50ad5
StudentId:1
Name:"Pallav Lalitv"
EnrolmentDate:2022-02-01T00:00:00.000+00:00
_id:621329925abd9c9b33c50ad6

StudentId:2
Name:"Rohan Niraj"
EnrolmentDate:2022-01-01T00:00:00.000+00:00

Other Operations are below as per MongoDB Doc

Name	Description
$eq	Matches values that are equal to a specified value.
$gt	Matches values that are greater than a specified value.
$gte	Matches values that are greater than or equal to a specified value.
$in	Matches any of the values specified in an array.
$lt	Matches values that are less than a specified value.
$lte	Matches values that are less than or equal to a specified value.
$ne	Matches all values that are not equal to a specified value.
$nin	Matches none of the values specified in an array.

Date Already Stored as String

If the date is already stored in string format

Records

_id:6213291e5abd9c9b33c50ad5
StudentId:1
Name:"Pallav Lalitv"
EnrolmentDate:"2022-02-01T00:00:00.000Z"
 
_id:621329925abd9c9b33c50ad6
StudentId:2
Name:"Rohan Niraj"
EnrolmentDate:"2022-01-01T00:00:00.000Z"

_id:621329b35abd9c9b33c50ad7
StudentId:3
Name:"Aswathi Savitri"
EnrolmentDate:"2022-03-01T00:00:00.000Z"

For operation on date which is stored in string format in mongoDB we can use `$dateFromString` in an aggregate query.

( Table: DB.students )

$project

{
  StudentId: 1,
  Name: 1,
  EnrolmentDate: { $dateFromString: {
    dateString: "$EnrolmentDate",
    format: "%Y-%m-%dT%H:%M:%S.%LZ"
  }}
}

$match

{
  "EnrolmentDate": {
   "$lt" : new Date("2022-02-01T00:00:00.000Z")
  }
}

MongoDB Query Example (Between Dates)

Suppose we need records of students who are enrolled in January month.

Using ISODate syntax ( Table: DB.students ) – In January 2022

{
      "EnrolmentDate": {
           "$gte" : new Date("2022-01-01T00:00:00.000Z"),
           "$lt" : new Date("2022-02-01T00:00:00.000Z")
      }
}

MongoDB Query Example (Only Dates)

Suppose we need records of students who are enrolled on 5th January 2022.

In this we only need date comparison no time comparison required

Using ISODate syntax ( Table: DB.students )

{
      "EnrolmentDate": {
           "$eq" : new Date("2022-01-05"),
      }
}

MongoDB Query Example (Past/Future Date)

Suppose we need records of students who are enrolled before 30 days or before that. The time difference needs to be give in milliseconds

Using ISODate syntax ( Table: DB.students ) – 30 days before

{
      "EnrolmentDate": {
           "$lt" : new Date(ISODate().getTime() - 1000 * 60 * 60 * 24 * 30)
      }
}

Same way we can use for (seconds, minutes & hours):

• No Of Seconds : 1000 * seconds
• No Of Minutes : 1000 * 60 * minutes
• No Of Hours : 1000 * 60 * 60 * hours
• No Of Days : 1000 * 60 * 60 * 24 * days

Examples that might be helpful

After 30 Days

{ "EnrolmentDate": { "$gt": new Date(ISODate().getTime() + 1000 * 60 * 60 * 24 * 30)}}

Before 16 hours

{ "EnrolmentDate": { "$lt": new Date(ISODate().getTime() - 1000 * 60 * 60 * 16)}}

After 15 hours

{ "EnrolmentDate": { "$gt": new Date(ISODate().getTime() + 1000 * 60 * 60 * 15)}}

Before 32 Minutes

{ "EnrolmentDate": { "$lt": new Date(ISODate().getTime() - 1000 * 60 * 32)}}

After 16 Minutes

{ "EnrolmentDate": { "$gt": new Date(ISODate().getTime() + 1000 * 60 * 16)}}

Before 45 Seconds

{ "EnrolmentDate": { "$lt": new Date(ISODate().getTime() - 1000 * 60 * 45)}}

After 15 Seconds

{ "EnrolmentDate": { "$gt": new Date(ISODate().getTime() + 1000 * 60 * 15)}}