mongodb difference in time

Question

How can i filter database entries that have a datetime less than 60min in the past?

I tried some date operations as follows in mongodb with two fields timestamp and marketstartime that are of type date in all my documents:

{"$subtract": ["$timestamp", "$marketstartime"]}

but it returns always null for that operation. Why?

My timestamp and marketstartime entries in the db are in date type and look as follows, this should be correct: 2017-12-23 12:00:00.000Z

The actual question I’m trying to solve: How can I get all entries that have a timestamp less than 60 min in the past from now?

Possible duplicate of [Find objects between two dates MongoDB](https://stackoverflow.com/questions/2943222/find-objects-between-two-dates-mongodb) — s7vr, Dec 29 '17 at 20:04
No, it is not a duplicate. Please read the question more carefully. Thanks — Nickpick, Dec 29 '17 at 20:05
Vote retracted. Sorry, I didn't realize it was time difference between two date fields. Try adding sample document from your collection and your complete query. $subtract should give you difference in milliseconds. — s7vr, Dec 29 '17 at 20:18
I think this is the duplicate you're looking for https://stackoverflow.com/questions/18233945/query-to-get-last-x-minutes-data-with-mongodb. You need to use a programatic way of now - x, whether that's in the mongo shell or the pymongo application. — Verran, Dec 29 '17 at 20:41
The $subtract operator seems to work for me. db.getCollection('data').aggregate([{$project: {d1: {$subtract: ["$date1", "$date2"]}}}]) Could you add the query which didn't work for you? — Volodymyr Synytskyi, Dec 29 '17 at 21:04

score 2 · Accepted Answer · answered Dec 30 '17 at 08:05

A query can composed for documents with timestamp value set less than 60 minutes ago.

from datetime import datetime, timedelta

query = {
    '$timestamp': {
        '$lt': datetime.now() + timedelta(minutes=-60)
    }
}

cursor = db.collection.find(query)

mongodb difference in time

1 Answers1