Question 300948 in Mongodb Question posted in
The official documentation can be found here.

Why this query works in MongoDB?

For instance, I have this JSON inject

{
    "insert": "2023-12-08T09:49:00"
}

It is String

I have sent this query here

db.example.find({ "insert" : { "$gt" : "2023-12-08T09:48:00.000Z", "$lt" : "2023-12-08T10:23:38" } })

How is possible that it works? I couldn’t find any implicit conversion between String to UTCdate in docs.

I know this field must be a Date for best practices, but that got me curious if there any kind of explanation.

Tags:

2

Answers


  1. 0

    In MongoDB, the $gt and $lt operators are used for range queries. When you use these operators with date fields, MongoDB expects the values to be in the BSON date format, which is essentially the JavaScript Date object.

    In your case, you have a JSON document with a string representing a date, When you perform a query like you provided, MongoDB is able to perform implicit conversion in the background. This is because MongoDB is built on BSON, which has a date type that is compatible with the JavaScript Date object.

    When MongoDB encounters a string in a query where a date is expected, it attempts to convert that string to a date implicitly, treating it like a JavaScript Date object. This conversion allows MongoDB to compare the dates even though one is stored as a string in the document.

    This implicit conversion can be convenient but may lead to unexpected behavior in certain cases. Explicitly storing dates as Date types in MongoDB is considered a best practice because it ensures consistency and avoids potential issues related to implicit conversions. It also allows you to take full advantage of MongoDB’s date-related query operators and optimizations.

    Login or Signup to reply.
  2. 0

    It works because it’s doing a String comparison between two Strings. One of the many advantages of having dates in UTC-ISO format: YYYY-MM-DDTHH:MM:SSZ. Of course, this is not recommended for dates since string comparisons are much much slower than datetime comparisons, since those are effectively numbers (floating point).

    So the parts that make a date bigger are to the left and also blends well with string comparison.

    Example: Like "aaa" < "bbb", same goes for "2022" < "2023". It’s just character-by-character comparison.

    The string "2023-12-08T09:49:00" is less than the string "2023-12-08T10:23:38" – from left to right in each string, they are equal until 0 < 1. It just happens to also look like a meaningful date format to humans.

    Where things go wrong is if you tried to compare datetimes with different timezones, then it’s just treated as a string and it wouldn’t work correctly.

    The datetime 2023-12-08T09:49:00+0800 > 2023-12-08T10:23:38+0100 (note the timezone specified seconds). But as strings, it would say that the first < the second since no meaning or actual value is extracted from the timezone specification.

    Btw, this behaviour of ISO format dates when stored-as-strings occurs in nearly all programming languages, since it’s just string comparison. (Can’t think of any where strings don’t behave like this.)

    Btw, if your document had that as a real date and not a string looking like a date, it would have the $date part, and always UTC in Mongo:

    {
  "insert": {
    "$date": "2023-12-08T09:49:00.000Z"
  }
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search