TypeError: can't compare datetime.date to DateProperty

Question

I am trying to query if a certain date belongs to a specific range of dates. Source code example:

billing_period_found = BillingPeriod.query(
        ndb.AND(
            transaction.date > BillingPeriod.start_date,
            transaction.date < BillingPeriod.end_date)
        ).get()

Data definition:

class Transaction(ndb.Model):
    date = ndb.DateProperty(required=False)

class BillingPeriod(ndb.Model):
    start_date = ndb.DateProperty(required=False)
    end_date = ndb.DateProperty(required=False)

Getting the following error: TypeError: can't compare datetime.date to DateProperty

The message error does make sense because datetime is different from DateProperty. However, as you can see, the definition for transaction.date is not datetime, so I am not getting where this attempt to convert datetime to date is coming from. Anyways - If I figure out how to convert datetime to DateProperty, I guess it would fix the problem.

Any ideas on how to solve this?

Thanks!

Answer 1

The App Engine datastore does not allow queries with inequalities on multiple properties (not a limitation of ndb, but of the underlying datastore). Selecting date-range entities that contain a certain date is a typical example of tasks that this makes it impossible to achieve in a single query.

Check out Optimizing a inequality query in ndb over two properties for an example of this question, and, in the answer, one suggestion that might work: query for (in your case) all BillingPeriod entities with end_date greater than the desired date, perhaps with a projection to just get their key and start_date; then, select out of those only those with start_date less than the desired date, in your own application (if you only want one of them, then a next over the iterator will stop as soon as it finds one).

Edit: the issue above is problem #1 with this code; once solved, problem #2 arises -- as clearly listed at https://cloud.google.com/appengine/docs/python/ndb/queries, the property is ndb queries is always on the left of the comparison operator. So, one can't do date < BillingPeriod.end_date, as that would have the property on the right; rather, one does BillingPeriod.end_date > date.