MongoEngine filter for date column

Question

I am trying to use MongoEngine to apply a filter on a mongodb collection called Employees. The filter is based on country, city and join_date.

The filter condition is that the number of months obtained by subtracting join_date from today's date should be a minimum of "x" months, where x is a setting value. So, for example, if x is 18 months, I need to find all employees whose join_date was a minimum of 18 months prior to today's date.

I am trying to achieve this by calling the filter() method, but I'm unable to figure out how to do that.

            matching_records = Employees.objects(
                                    country=rule.country, 
                                    city=rule.city) \
                              .filter(relativedelta.relativedelta(datetime.datetime.now, join_date).months > 18)

I get an error, "name join_date is not defined". I am unable to figure out how to get the filter to work. Please help.

score 0 · Answer 1 · answered Apr 08 '20 at 19:27

You need to use the lte (less than or equal) or gte (greater than or equal) operators like this:

from datetime import datetime, timedelta

import dateutil.relativedelta
from mongoengine import *

connect()

now = datetime.utcnow()
yesterday = now - dateutil.relativedelta.relativedelta(days=5)
past = now - dateutil.relativedelta.relativedelta(months=20)

class TestDate(Document):
    dt = DateTimeField()

# Saving 3 objects to verify the query works
TestDate(dt=now).save()
TestDate(dt=yesterday).save()
TestDate(dt=past).save()

TestDate.objects(dt__lte=now - dateutil.relativedelta.relativedelta(months=18)) # return TestData associated with `past`
TestDate.objects(dt__gte=now - dateutil.relativedelta.relativedelta(months=18)) # return TestData associated with `now` and `yesterday`

MongoEngine filter for date column

1 Answers1