CODEWITHSUNDEEP
mongodbmongodb-querypymongopython-datetime
Sarang Bhatnagar
Sarang Bhatnagar

Reputation: 49

replacing datetime.datetime() object in pymongo query

I am using pymongo in my project and findind in the db like this-

db.collection.aggregate({{ "$match": {
                         "date":{"$gte":{{parameter1}},
                                 "$lte":{{parameter2}}}})

This is the query-

{ "$match": {
   "date":{"$gte":{{parameter1}},
          "$lte":{{parameter2}}}

NOw I want to replace {{parameter1}} and {{parameter2}} with exact datetime.datetime(2022-06-15) and datetime.datetime(2022-06-10) The query should look like this after replacement-

{ "$match": {
"date":{"$gte":datetime.datetime(2022-06-10),
        "$lte":datetime.datetime(2022-06-15)}

I cant put the datetime.datetime directly inside query since thats not the demand of my project and parameter1 and parameter2 are dynamic. Everytime I put datetime.datetime() in a variable I get ISO date and hence the query returns 0 results.

Sample document-

{
  "_id": "ObjectId(\"62a84e1975bd4e000927040f\")",
  "UUID": "2130941",

  "accessDT": "2022-06-14T08:57:42.000Z",
  "eventItemId": "",
  "notificationEventId": "",
  "notificationEnv": "",
  "log_type": "log",
  "day": 14,
  "month": 6,
  "year": 2022,
  "hour": 8,
  "minute": 57,
  "second": 42,
  "week": 2,
  "flag_version": "v1"
},

Query after replacement should look like this in python-

[{'$match': {'accessDT': {'$gte': datetime.datetime(2022, 6, 13, 0,
0), '$lte': datetime.datetime(2022, 6, 13, 23, 59, 59, 999)}]

Currently when I replace parameters in python query looks like this-

[{'$match': {'accessDT': {'$gte': "2022-06-13T08:53:20.000Z", '$lte': "2022-06-14T08:53:20.000Z"}]

Upvotes: 1

Views: 402

Answers (1)

rickhg12hs
rickhg12hs

Reputation: 11912

I can demonstrate the datetime usage method you are looking for by using local ipython with pymongo. The sample collection is data in database sample_weatherdata on a MongoDB Atlas GCP.

Here's the ipython session.

$ ipython
Python 3.10.4 (main, Mar 25 2022, 00:00:00) [GCC 11.2.1 20220127 (Red Hat 11.2.1-9)]
Type 'copyright', 'credits' or 'license' for more information
IPython 8.4.0 -- An enhanced Interactive Python. Type '?' for help.

In [1]: import pymongo
In [2]: import datetime
In [3]: import pandas as pd

In [4]: connection_str = "mongodb+srv://<loginName>:<password>@<project>.w426d.mongodb.net/?retryWrites=true&w=majority"

In [5]: startTime = datetime.datetime.fromisoformat("1984-03-10")
In [6]: endTime = startTime + datetime.timedelta(days=1, microseconds=-1)

In [7]: startTime, endTime
Out[7]: 
(datetime.datetime(1984, 3, 10, 0, 0),
 datetime.datetime(1984, 3, 10, 23, 59, 59, 999999))

In [8]: match0 = {
   ...:   "$match": {
   ...:     "ts": {
   ...:       "$gte": startTime,
   ...:       "$lte": endTime
   ...:     }
   ...:   }
   ...: }

In [9]: project0={
   ...:   "$project": {
   ...:     "_id": False,
   ...:     "ts": True,
   ...:     "callLetters": True,
   ...:     "seaSurfaceTemperature": True
   ...:   }
   ...: }

In [10]: sort0 = {
    ...:   "$sort": {
    ...:     "ts": 1,
    ...:     "callLetters": 1
    ...:   }
    ...: }

In [11]: agg = [ match0, project0, sort0 ]

In [12]: with pymongo.MongoClient(connection_str, server_api=pymongo.server_api.ServerApi('1')) as client:
    ...:     result = client["sample_weatherdata"]["data"].aggregate(agg)
    ...:     df = pd.DataFrame(result)
    ...: 

In [13]: df
Out[13]: 
                      ts callLetters            seaSurfaceTemperature
0    1984-03-10 00:00:00        0JSV  {'value': 24.3, 'quality': '9'}
1    1984-03-10 00:00:00        2100   {'value': 8.4, 'quality': '9'}
2    1984-03-10 00:00:00        3ELE                              NaN
3    1984-03-10 00:00:00        3EYN   {'value': 4.0, 'quality': '9'}
4    1984-03-10 00:00:00        3FJV                              NaN
...                  ...         ...                              ...
1434 1984-03-10 21:00:00        TFSE                              NaN
1435 1984-03-10 21:00:00        TFZK  {'value': 10.0, 'quality': '9'}
1436 1984-03-10 21:00:00        WVFN   {'value': 0.9, 'quality': '9'}
1437 1984-03-10 22:00:00        3EBA  {'value': 20.0, 'quality': '9'}
1438 1984-03-10 23:00:00        5LFX   {'value': 9.0, 'quality': '9'}

[1439 rows x 3 columns]

Upvotes: 2

Related Questions