ndb query error with datetime field - Google App Engine

Question

I'm having a problem and I don't find any information about.

I define a field in my model like this.

class Dates(ndb.model):
    ...
    date = ndb.DateTimeProperty(required = True) # I want to store date and time
    ...

Later I try a query (now I want all the dates for a day, I don'tn mind the time):

kl = Dates.query(ndb.AND(Dates.date.year == year,
                         Dates.date.month == month,
                         Dates.date.day == day),
                 ancestor = customer.key).fetch(keys_only = True)
dates = ndb.get_multi(kl)

But I get this error log: AttributeError: 'DateTimeProperty' object has no attribute 'year'

I don't know why. I've tried Dates.date() == date, Dates.date == date (<-DateTime obj), ...

My DB is still empty but I suppose this doesn't mind because I'll never have dates for every possible days.

Anybody knows why? Should I go with GQL instead?

Starlet

Answer 1

You can use "range" queries for this. See example below.

import datetime
date = datetime.datetime.strptime('02/19/2013', '%m/%d/%Y')
kl = Dates.query(
    ndb.AND(Dates.date >= date),
            Dates.date < date + datetime.timedelta(days=1))

Will fetch all datetime's with 02/19/2013.

Tribadism

You cannot do that.. check the limitations.. developers.google.com/appengine/docs/python/ndb/queries#intro – Elihu

I tested this by myself. Everything works. Are you sure you tried this code? I can provide a complete example, if you want. – Tribadism

Totally confused it.. my bad.. can you edit your question.. so I can retract my downvote? – Elihu

I tried your solution and the error has vanished, I hope to not have problems in the future if I go on with the DateTimeProperty. I think I was not properly defining the datetime object to compare, strptime method has helped. No errors even not using ranges, only Dates.date==date – Starlet

Ops! I do need use 'ranges' to peek from 00:00:00 to 24:00:00 – Starlet

The range method is nice, but can still be inflexible. If I have a datetime, but if sometimes I want to query purely based on time, and other times I want to query based on date, and at other times I want the entire datetime to be printed, I can't do that with just a single datetime property. I guess I could break it up and have more properties, and re-think what I really need as well. – Unkenned

@kevinze sure, especially with recent datastore update to their pricing model. right now datastore does not require so much optimizations as before. – Tribadism

Answer 2

What you are trying to achieve is not really possible, because you can only query for the whole date and not for some parts of it.

In order to achieve what you are trying there I would suggest you to add few more properties to your model:

class Dates(ndb.model):
    ...
    date = ndb.DateTimeProperty(requiered=True)
    date_year = ndb.IntegerProperty()
    date_month = ndb.IntegerProperty()
    date_day = ndb.IntegerProperty()
    ...

You could update these values on every save or you could use Model Hooks to do it automagically and then your new query will become:

kl = Dates.query(ndb.AND(Dates.date_year == year,
                         Dates.date_month == month,
                         Dates.date_day == day),
                 ancestor=customer.key).fetch(keys_only=True)
dates = ndb.get_multi(kl)

Elihu

Wow!! I wont like to increase my database size. I've thought something like to query between a date_max=date_at_23:59:59 and date_min=date_at_00:00:00. For querying with the whole field. But I get the same class of errors. – Starlet

@OscarParra You shouldn't be afraid of adding more fields, this is not going to slow down your performance or will have any (if at all) difference in the payments. By doing that you will be able also to query stuff by specific years, months, etc.. something that you won't be able to do otherwise, by simply using the DateTimeProperty. – Elihu

@OscarParra Don't forget that you are not allowed to use multiple inequalities in the same filter. Read the limitations from here: developers.google.com/appengine/docs/python/ndb/queries#intro – Elihu

Thanks, then really I don't need a DateTimeProperty if I have to use IntegerProperties for year, month, day, hour and minute in order to perform specific queries. Is this property in fact so unuseful? – Starlet

@OscarParra I wouldn't say it's useless, it is very useful on presenting stuff since you can do date formattings and other stuff directly using the datetime, while the other fields are useful for filtering. – Elihu

Answer 3

Use a DateProperty. Then you can use a simple == query:

>>> import datetime
>>> from google.appengine.ext.ndb import *

>>> class D(Model):
...   d = DateProperty()
...

>>> d = D(d=datetime.date.today())

>>> d.put()
Key('D', 9)

>>> d
D(key=Key('D', 9), d=datetime.date(2013, 2, 20))

>>> D.query(D.d == datetime.date.today()).fetch()
[D(key=Key('D', 9), d=datetime.date(2013, 2, 20))]

Cavendish

But there was a condition to keep also a time. Solution using the '>' and '<' operators will use a lot more resources? I am using this 'trick' even for filtering keys by first letter. – Tribadism

Yes, I need to store the time (I'm storing dental clinic dates, so time is important). Dmitry solution is working fine. What are the advantages of using DateTimeProperty over a few IntegerProperties, if I can't perform complex queries over it, like p.e. get all dates on monday? – Starlet

Answer 4

I expanded @Guido van Rossum code snippet to include <> and timedelta for calculations, mostly for my own satisfaction

import datetime
from datetime import timedelta

from google.appengine.ext.ndb import *

class D(Model):
  d = DateProperty()

now = datetime.date.today()
date1 =  now-timedelta(+500)
date2 =  now-timedelta(+5)

d1 = D(d=now)
d2 = D(d=date1)
d3 = D(d=date2)

d1.put()
d2.put()
d3.put()

date2 =  now-timedelta(+50)

result1 = D.query(D.d == now).fetch(4)
result2 = D.query(D.d > date2).fetch(2)
result3 = D.query(D.d < date2).fetch(2)

result4 = D.query(D.d >= date2, D.d <= now).fetch(2)

print result1
print "+++++++"
print result2
print "+++++++"
print result3
print "+++++++"
print result4

Maratha