CODEWITHSUNDEEP
pythonmongodbdataframetimestamppymongo
xaroulis gekas
xaroulis gekas

Reputation: 165

Query on mongodb with python

I am executing this query in mongodb with pymongo python :

cursor=mydb1.mongodbtime.find({"$and": [
        { "timestamp1": { "$gte":"2018-01-01 00:05:00 " } },
        { "timestamp1": { "$lte":"2018-01-02 00:05:00 " } }
    ]},
    {"id13":1}
)
for x in cursor:
     pprint(x)

In the output it says that

Process finished with exit code 0

But i dont get any results printed in my screen. Maybe i have done some mistake in the query. My data look like this:

  _id:ObjectId("603fb0b7142a0cbb439ae2e1")
    id1:3758
    id6:2
    id7:-79.09
    id8:35.97
    id9:5.5
    id10:0
    id11:-99999
    id12:0
    id13:-9999
    c14:"U"
    id15:0
    id16:99
    id17:0
    id18:-99
    id19:-9999
    id20:33
    id21:0
    id22:-99
    id23:0
    timestamp1:2010-01-01T00:05:00.000+00:00
    timestamp2:2009-12-31T19:05:00.000+00:00

The code i have used for the timestamps:

    df['date4'] = df['date4'].astype('datetime64[ns]') #swsto gia mongodb
    df['date2'] = df['date2'].astype('datetime64[ns]') #swsto gia mongodb
    df['time3'] = df['time3'].apply(lambda x: datetime.datetime.strptime(x[0] + x[1] + ":" + x[2] + x[3], '%H:%M'))
    df['time5'] = df['time5'].apply(lambda x: datetime.datetime.strptime(x[0] + x[1] + ":" + x[2] + x[3], '%H:%M'))
    df['date2'] = df['date2'].apply(lambda x: arrow.get(x).format("YYYY-MM-DD"))
    df['date4'] = df['date4'].apply(lambda x: arrow.get(x).format("YYYY-MM-DD"))
    df['time3'] = df['time3'].apply(lambda x: arrow.get(x).format("HH:mm:ss"))
    df['time5'] = df['time5'].apply(lambda x: arrow.get(x).format("HH:mm:ss"))
    df['timestamp1'] = (df['date2'] + ' ' + df['time3']).astype('datetime64')
    df['timestamp2'] = (df['date4'] + ' ' + df['time5']).astype('datetime64')
    df.drop(['time3', 'time5', 'date2', 'date4'], axis=1, inplace=True)

Upvotes: 0

Views: 57

Answers (2)

Belly Buster
Belly Buster

Reputation: 8814

For info you don't need the $and; this will work just as well:

cursor=mydb1.mongodbtime.find({
    "timestamp1": {"$gte": datetime.strptime("2018-01-01 00:05:00", "%Y-%m-%d %H:%M:%S"),
                   "$lte": datetime.strptime("2018-01-02 00:05:00", "%Y-%m-%d %H:%M:%S")}},
    {"id13":1})

Upvotes: 1

bauman.space
bauman.space

Reputation: 2023

Have you tried a query with datetime objects rather than strings?

There is a trailing space in your timestamp above that you could include in the strptime

from datetime import datetime
cursor=mydb1.mongodbtime.find(
    {"$and": [
        { "timestamp1": { 
            "$gte": datetime.strptime("2018-01-01 00:05:00", "%Y-%m-%d %H:%M:%S") } 
        },
        { "timestamp1": { 
            "$lte":datetime.strptime("2018-01-02 00:05:00", "%Y-%m-%d %H:%M:%S") } }
    ]},
    {"id13":1}
)
for x in cursor:
     pprint(x)

Upvotes: 1

Related Questions