Jeff Saltfist Jeff Saltfist - 3 years ago 123
Python Question

Pymongo - Query all strings with certain date

I have a mongo db ('test') with a collection ('main') that has a field with datetimes stored. An example date is as follows:


The dates are stored as a string.

I want to query the collection for all data occurring on 2017-07-20 and pull it into a pandas dataframe. Below is the code I am attempting:

import datetime
the_date = datetime.datetime(2017,7,20) # Date I want to query

import pymongo
import pandas as pd
from pymongo import MongoClient
client = MongoClient()
db = client.test
collection = db.main

data = pd.DataFrame(list(collection.find({'time': { '$in':[the_date]}})))


This just returns an empty frame. I am wondering how it is possible to query mongodb for all data with the specific date I am seeking. I see why the $in operator does not work, but don't have a better way.

Answer Source

You are mixing real datetimes with strings. MongoDB won't autoconvert the $in with a datetime to the str()equiv. Try this; note the anchor:

data = pd.DataFrame(list(collection.find({'time': {'$regex': '^2017-07-20'}} )));
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download