Federico Builes
Federico Builes

Reputation: 5127

Filtering by dates in Google App Engine's Datastore

I'm having some issues trying to filter a set of objets by their date. Right now I can get all of a user's transactions like this:

> t = Transaction.all().filter("client_email =", "some_email").filter("application_id =", "foo").fetch(100)
> len(t)
# => 4

Now, if I want to set up some date filters:

> min = datetime.datetime(2009, 2, 3, 0, 0)
> max = datetime.datetime(2009, 11, 4, 0, 0)

> len(Transaction.gql("WHERE client_email =:1 AND date >:2 AND date <=:3 AND application_id =:4", 
                      "some_email", min, max, "foo").fetch(100))
# => 2

I know all those transactions have a date < max && > min:

> map(lambda x: x.date, t)
# => [datetime.datetime(2009, 10, 2, 22, 43, 51), datetime.datetime(2009, 10, 5, 2, 5, 24), datetime.datetime(2009, 10, 7, 16, 51, 5), datetime.datetime(2009, 10, 7, 16, 6, 53)]

Yup, all those transactions were made in October 2009. I filtered them in Python just to confirm that it was right:

> trans = filter(lambda x: x.date < max and x.date >= min, t)
> len(trans)
# => 4

So yes, they all have the correct dates but due to something in my query the results don't match the expected results, any idea of what might be wrong in the query?

Upvotes: 2

Views: 2371

Answers (1)

Jose Montes de Oca
Jose Montes de Oca

Reputation: 879

Have you tried making the query date info compliant with App Engine GQL syntax?. E.g syntax:

DATETIME(year, month, day, hour, minute, second)
DATETIME('YYYY-MM-DD HH:MM:SS')
DATE(year, month, day)
DATE('YYYY-MM-DD')
TIME(hour, minute, second)
TIME('HH:MM:SS')

Try running the following GQL:

"WHERE client_email =:1 AND date > DATE(:2) AND date <= DATE(:3) AND application_id =:4", "some_email", "2009-02-03", "2009-11-04", "foo"

Upvotes: 1

Related Questions