Find between two dates return empty dataframe

Hi,

i am trying to get all data between range of two datetime using python pandas, but i get empty dataframe.

this is my dummy data

[
  {
    _id: ObjectId("647382945becfcf89a67bd96"),
    stock_ticker_name: 'a',
    created_date: ISODate("2023-05-28T16:34:28.750Z"),
    update_date: ISODate("2023-05-28T16:34:28.750Z")
  },
  {
    _id: ObjectId("647382e25becfcf89a67bd97"),
    stock_ticker_name: 'b',
    created_date: ISODate("2023-05-28T16:35:46.290Z"),
    update_date: ISODate("2023-05-28T16:35:46.290Z")
  },
  {
    _id: ObjectId("6474ae299b2c8d1ec0850e7a"),
    stock_ticker_name: 'c',
    created_date: ISODate("2023-05-29T13:52:41.426Z"),
    update_date: ISODate("2023-05-29T13:52:41.426Z")
  },
  {
    _id: ObjectId("6474ae379b2c8d1ec0850e7b"),
    stock_ticker_name: 'd',
    created_date: ISODate("2023-05-29T13:52:55.360Z"),
    update_date: ISODate("2023-05-29T13:52:55.360Z")
  },
  {
    _id: ObjectId("6475e531d0fdcdc1879844f9"),
    stock_ticker_name: 'e',
    created_date: ISODate("2023-05-30T11:59:45.666Z"),
    update_date: ISODate("2023-05-30T11:59:45.666Z")
  },
  {
    _id: ObjectId("6475e59fd0fdcdc1879844fa"),
    stock_ticker_name: 'f',
    created_date: ISODate("2023-05-30T12:01:35.286Z"),
    update_date: ISODate("2023-05-30T12:01:35.286Z"),
    quantity: '3'
  }
]

and this is my python code

    now = datetime.today().strftime("%Y-%m-%dT%H:%M:%S.%fZ")
    deltas = datetime.today() - timedelta(days=3)
    last_sync = deltas.strftime("%Y-%m-%dT%H:%M:%S.%fZ")
    connection = MongoClient('mongodb://<conn>:27017')
    mongo_db = connection.keblingers
    mongo_collection = mongo_db.stock_ticker
    result = pd.DataFrame(list(mongo_collection.find({"created_date": {'$gte': f"new Date('{last_sync}')", '$lte': f"new Date('{now}')"}})))
    print(result)

and this is the output

Empty DataFrame
Columns: []
Index: []

where is the wrong code?

Hi @blinksatan_182,

Not too familiar with pandas however, can you provide the values of the following variables when you run the code:

  • now
  • deltas
  • last_sync

You can try add an additional line under each with print() perhaps. This will highlight what is being passed through to the result.

Additionally, can you provide reproducible code including the libraries used so that we can attempt to replicate this behaviour on our test environments? Please also advise any versioning where possible.

Regards,
Jason

Hi Jason,

this is the output of now, deltas and last_sync

this is now -> 2023-05-31T10:43:50.621256Z
this is last_sync -> 2023-05-28T10:43:50.621256Z
this is deltas -> 2023-05-28 10:43:50.621256

and below for the full script

from pymongo import MongoClient
import pandas as pd
from datetime import datetime, timedelta

def get_data_mongo():
    now = datetime.today().strftime("%Y-%m-%dT%H:%M:%S.%fZ")
    deltas = datetime.today() - timedelta(days=3)
    last_sync = datetime.strftime(deltas,"%Y-%m-%dT%H:%M:%S.%fZ")
    connection = MongoClient('mongodb://conn')
    mongo_db = connection.keblingers
    mongo_collection = mongo_db.stock_ticker
    result = pd.DataFrame(list(mongo_collection.find({"created_date": {'$gte': f"new Date('{last_sync}')", '$lte': f"new Date('{now}')"}})))
    print(result)

and by the way, if i try to find like name or by quantity it is works, it is just by date i dont know why it is empty data frame

result = pd.DataFrame(list(mongo_collection.find({"stock_ticker_name": "a"})))
result = pd.DataFrame(list(mongo_collection.find({"quantity": {'$gte': 1}})))

Hi @blinksatan_182,

Not sure if this works for you but please take a look at the below code snippet I used for the get_data_mongo() portion:

def get_data_mongo():
    now = datetime.today()
    deltas = datetime.today() - timedelta(days=3)
    last_sync = deltas
    connection = MongoClient('mongodb+srv://<REDACTED>:<REDACTED>@cluster0.<REDACTED>.mongodb.net/?retryWrites=true&w=majority')
    mongo_db = connection.panda
    mongo_collection = mongo_db.collection
    result = pd.DataFrame(list(mongo_collection.find({"created_date": {'$gte': last_sync, '$lte': now}})))
    print(result)

Note: I redacted some credentials from the above

In the above example, I changed variables now and last_sync to datetime objects rather than strings. Additionally, within the $gte and $lte operators, I just provided the datetime objects rather than the stringified versions as well.

The above query returns:

>>>get_data_mongo()
                        _id stock_ticker_name            created_date             update_date quantity
0  647382945becfcf89a67bd96                 a 2023-05-28 16:34:28.750 2023-05-28 16:34:28.750      NaN
1  647382e25becfcf89a67bd97                 b 2023-05-28 16:35:46.290 2023-05-28 16:35:46.290      NaN
2  6474ae299b2c8d1ec0850e7a                 c 2023-05-29 13:52:41.426 2023-05-29 13:52:41.426      NaN
3  6474ae379b2c8d1ec0850e7b                 d 2023-05-29 13:52:55.360 2023-05-29 13:52:55.360      NaN
4  6475e531d0fdcdc1879844f9                 e 2023-05-30 11:59:45.666 2023-05-30 11:59:45.666      NaN
5  6475e59fd0fdcdc1879844fa                 f 2023-05-30 12:01:35.286 2023-05-30 12:01:35.286        3
6  6476d2927bc0c144580bfcdf               NaN 2023-05-29 00:00:00.000                     NaT      NaN

Hope this helps. If not, please provide any further details / queries you have. With any code snippets, it’s highly recommend to alter accordingly and test thoroughly to ensure it meets all your use case and requirements.

Regards,
Jason

1 Like

Hi Jason,

ohh i see so the problem is in the date variable using formatting strftime. so i dont need to format the date.
thank you it is works.

1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.