CODEWITHSUNDEEP
pythonmongodb
Tobin
Tobin

Reputation: 2149

MongoDB: How to express correctly query for getting data between two dates

Similar questions have been asked here, but none of them completely correspond to my concern.

I'm developing a visitor management application. So suppose I have 5 visitors for whom I have recorded information such as name, phone number, time of entry, time of exit, etc. In a simplified way I have for example this:

  • T_EN = "Time of entry; T_EX = "Time of exit"
1. {name: "John", T_EN: 2019-01-14 08:00:00.608, T_EX: 2019-01-14 16:00:00.078}
2. {name: "Alice", T_EN: 2019-01-14 10:05:00.412, T_EX: 2019-01-14 12:15:00.065}
3. {name: "Eric", T_EN: 2019-01-14 10:45:00.315, T_EX: 2019-01-14 11:00:00.952}
4. {name: "Mark", T_EN: 2019-01-14 11:22:00.548, T_EX: 2019-01-14 13:58:00.875}
5. {name: "Bob", T_EN: 2019-01-14 14:49:00.490, T_EX: 2019-01-14 15:52:00.652}

Now I would like to know which visitors were present that day between 10:00 and 13:00.

What I tested (and that does not work as I hope):

date_start = datetime.datetime.strptime('2019-01-14 10:00:00', '%Y-%m-%d %H:%M:%S')
date_end = datetime.datetime.strptime('2019-01-14 13:00:00', '%Y-%m-%d %H:%M:%S')
Mongo.db.History.find({"$and": [
                      {"T_EN": {"$gte": date_start, "$lt": date_end}},
                      {"T_EX": {"$gte": date_start, "$lt": date_end}}
                     ]})

Logically, in this interval, John, Alice, Eric and Mark were present. But I can not find a query that returns these 4 results. Every time I have John who is excluded.

So can someone help me to correctly express this request?

Upvotes: 0

Views: 34

Answers (1)

Neil
Neil

Reputation: 1633

Your logic for determining which visitors are present is incorrect. You have an observed date range, 10:00 to 13:00. You have visitor date ranges and you need to determine which overlap your observed range. To do so, your query should find visitors which enter before date_end, and exit after date_start.

Mongo.db.History.find({"$and": [
                      {"T_EN": {"$lt": date_end}},
                      {"T_EX": {"$gte": date_start}}
                     ]})

Upvotes: 1

Related Questions