Sorting on ISODate attributes works, but the "$lt" and "$gt" queries are failing following a transfer to Atlas

Working with Data
,
1

Hi,

I am having this especially weird issue, that is turning me crazy ! I am centralising data on my Atlas Cluster from bare-metal servers running community MongoDB databases locally. However I have an issue with my ISODates() attributes that are converted to “$date” dictionnaries, after which the sort() query still works but the “$lt” and “$gt” queries are failing.

Attribute in local db

"createdTime": ISODate("2023-01-20T11:20:50.268Z")

Attribute in the Atlas Cluster’db :

 "createdTime" : { '$date': '2023-01-20T11:20:50.268Z' }

In my Atlas Cluster’s collection I am still able to sort on the time attribute :

db.MyCollection.find().sort({createdTime:-1}).map(x => x.createdTime)
[
  { '$date': '2023-01-20T11:20:50.268Z' },
  { '$date': '2023-01-20T11:20:26.587Z' },
  { '$date': '2023-01-20T11:20:04.108Z' },
]

But when I try to use the operators “$gt” and “$lt” then mongo doesn’t know what to do :

db.MyCollection.countDocuments({ createdTime: { $lt: ISODate("2023-02-01T00:00:00.000Z")} })
0
// even with "$date" as a key
db.MyCollection.countDocuments({ "createdTime.$date": { $lt: ISODate("2023-02-01T00:00:00.000Z")}})
0

Eventually I figured I could make it work with a Javascript function to convert the “$date” objects back to ISODate, but I want to be able to use the “$lt” and “$gt” in my python API, relying on pymongo.

Any idea how I could solve what I assume is a date formatting issue ?

Thanks a lot for your help, apologies if this is the wrong place to ask.

1 Like
3

I am not fluent in python, but most of the python date queries I saw are using datetime.datetime rather than ISODate.

4

Hi @Barthelemy_Leveque,

Welcome to the MongoDB Community forums :sparkles:

As @steevej mentioned Python does not recognize ISODate() which is a Javascript function. It uses datetime() instead. Please see the following example for more details:

from pymongo import MongoClient
import pprint

client = MongoClient("mongodb://localhost:27017/")
db = client["test"]
collection = db["time"]

doc = collection.find_one()

pprint.pprint(doc)

It will return you the output as follows:

{'_id': ObjectId('63da39ed6cd602b05233cc45'),
 'time': datetime.datetime(2023, 1, 20, 11, 20, 50, 268000)}

And you can further add a code snippet to the above code to convert your $date format to ISODate format:

if "time" in doc:
    created_time = doc["time"]
    iso_date = created_time.isoformat()
    print("Time as ISO date:", iso_date)

which will return the following:

Time as ISO date: 2023-01-20T11:20:50.268000

Furthermore, I tried the following query

on my local MongoDB server version: 6.0.1 using mongosh

admin        0.000GB
config       0.000GB
local        0.000GB
my_database  0.000GB
test         0.000GB

> use test
switched to db test

> db.time.countDocuments({ time: { $lt: ISODate("2023-02-01T00:00:00.000Z")} })
3

> db.time.find()
{ "_id" : ObjectId("63da39ed6cd602b05233cc45"), "time" : ISODate("2023-01-20T11:20:50.268Z") }
{ "_id" : ObjectId("63da39ed6cd602b05233cc4d"), "time" : ISODate("2023-01-20T11:20:50.268Z") }
{ "_id" : ObjectId("63da39ed6cd602b05233cc35"), "time" : ISODate("2023-01-21T11:20:50.268Z") }

> db.time.countDocuments({ time: { $lt: ISODate("2023-02-01T00:00:00.000Z")} })
3
> db.time.countDocuments({ time: { $gt: ISODate("2023-01-01T00:00:00.000Z")} })
3
> db.time.countDocuments({ time: { $lt: ISODate("2023-01-01T00:00:00.000Z")} })
0

And for me $lt, $gt, and $gte all worked.

After that I tried the same query using pymongo:

from pymongo import MongoClient
from datetime import datetime

client = MongoClient("mongodb://localhost:27017/")
db = client["test"]
collection = db["time"]

new_time = datetime(2023, 2, 1)
print(new_time)

query = {"time": {"$lt": new_time}}
result = collection.count_documents(query)

print("Number of documents matching:", result)

And it also returned an output very similar to the above:

2023-02-01 00:00:00
Number of documents matching: 3

I hope it helps, and if not please share the version of the MongoDB server installed on your local machine and the code snippet you have written so that we can better understand the issue.

Best,
Kushagra

2 Likes