1

I have an application on app engine, and this application has an entity called Appointment. An Appointment has a start_time and a end_time. I want to fetch appointments based on the time, so I want to get all appointments for a given day, for example.

Since app engine doesn't support inequality query based on two fields, what can I do?

Rafael
  • 2,373
  • 4
  • 22
  • 28

3 Answers3

2

You could add another field for the date. A ComputedProperty would probably make sense for that. Or you could fetch from the start of the day, in batches, and stop fetching once you reach the end of your day. I'd imagine you could come up with a sensible default based on how many appointments you'd typically have in one day to keep this reasonably efficient.

Greg
  • 10,350
  • 1
  • 26
  • 35
  • I'm doing something similar to this. I'm storing the date as a parameter, and two times as start_time and end_time. There are, however, some problems with this approach. The first The first thing that comes to mind is that I can't support periods which go over midnight (from one day to another). There are ways to fix this problem though, so so far I'm happy with this solution. – Rafael Sep 21 '12 at 04:52
  • Remember that "dates" are not directly related to "time". 1 am in GMT is always 1 am (the same Long value), but it can be a different date depending on a time zone of a user. You say that your appointments cannot go through the midnight, but there is always a midnight somewhere. – Andrei Volgin Sep 21 '12 at 17:31
  • Also, there is such thing as daylight saving time, which does not affect "time", but does affect "date". – Andrei Volgin Sep 21 '12 at 17:38
  • That's fine. We use UTC time for everything, and so far our system only needs to work in daylight hours in Brazil, so there's no need to do something more complex to support this. – Rafael Sep 21 '12 at 21:00
1

The biggest problem is that a "date" means a different start and end "time" depending on a time zone of a user. And you cannot force all of your users to stick to one time zone all of the lives, not to mention DST changes twice a year. So you cannot simply create a new property in your entity to store a "date" object as was suggested. (This is why GAE does not have a "date" type property.)

I built a scheduling app. When a user specifies the desired range for events (it can be a day, a week or a month), I retrieve all events that have an end time larger than the start time of the requested range, and then I loop through them until I find the last event which has a start time smaller than the end time of the range.

You can specify how many entities you want to fetch in one request depending on a requested range (more for month than for a day). For example, if a given calendar is likely to have 5-10 events per day, it's enough to fetch the first 10 entities (you can always fetch more if condition is not met). For a month you can set a batch size to 100, for example. This is a micro-optimization, however.

Andrei Volgin
  • 40,755
  • 6
  • 49
  • 58
  • I liked it... I won't use it now because the storing date option works fine for me, but I'll keep this is mind for the future. Thanks! – Rafael Sep 21 '12 at 21:03
0
"SELECT * FROM appointments WHERE start_time < {0} AND end_time > {0}".format(time_lookup)

or if appengine wont let you do that maybe

Things.all().filter("end_time >", foo).filter("start_time <", foo)

http://nick.zoic.org/art/python/multiple_inequalities/

also Compare many date ranges in google app engine datastore (Many to many, Python) may help

Community
  • 1
  • 1
Joran Beasley
  • 110,522
  • 12
  • 160
  • 179
  • The first query does not work because app engine doesn't allow it. The e second one is bad... If I'm not mistaken, this would bring all Things from the database to the memory, and I don't want that. – Rafael Sep 21 '12 at 04:48