Mongodb querying using $date field

Question

I am newbie in mongodb and trying to query a document based on date and it is not working.

db.orders.insertOne({ _id: 6, item: "chocaltes" , qty: 1, price: 10, order_date: {"$date": 1322006400000}})

Details:

mongodb version: 6.0.12

Find Query:

db.orders.find({order_date: ISODate("2011-11-23T00:00:00Z" )})

it should return the document.

Answer 1

db.orders.insertOne({ 
  _id: 6, 
  item: "chocaltes", 
  order_date: { "$date": 1322006400000 } 
})

This inserts a document into the collection. And, any of the following two queries return that document.

db.orders.find()
db.orders.find({ "order_date.$date": 1322006400000 });

[
  {
    _id: 6,
    item: 'chocaltes',
    order_date: { '$date': 1322006400000 }
  }
]

Note the order_date field. It is of type object, with one embedded field $date. As of MongoDB version 5, you can have field names starting with $ sign (see Field Names with Periods (.) and Dollar Signs ($)).

So, the value for the embedded field order_date.$date is a of data type double with a value 1322006400000.

Find Query: db.orders.find({order_date: ISODate("2011-11-23T00:00:00Z" )})

This will not return the document.

To return a document using the date ISODate("2011-11-23T00:00:00Z") you will need a query like this:

db.orders.find({ 
  $expr: { 
    $eq: [ 
      { $toDate: { $getField: { field: { $literal: "$date" }, input: "$order_date" } } }, 
      ISODate("2011-11-23T00:00:00Z") 
    ] 
  }
})

Note the usage of $getField operator. This is for using the field names with $ sign. The $toDate converts the number 1322006400000 to a date type. The $toDate is an aggregation operator. To use an aggreation operator in a find query you need to use the $expr operator.

Answer 2

You can try in the following way.

gte:2022-02-07T00:00:00.000Z lte:2022-02-07T23:59:59:999Z

The reason for this is that:

To expand a bit on the answer, the dates are stored as full date-time value. Think of it as ISODate("2022-02-07T00:00:00.000Z") is storing February 7th 2022 at midnight. Comparing ISODate("2022-02-07T00:00:00.000Z") and ISODate("2022-02-07T01:00:00.000Z") will not show them as equal since the full “datetime” is being compared.

This answer has been quoted from: Query date range of the same day

Thank You WeDoTheBest4You