I have a simple table in Google App Engine with a date field. I want to query all the rows with the date field valued between now and 6 hours ago. How do I form this query?
+1
A:
SELECT * FROM simpletable
WHERE datefield < DATETIME(year, month, day, hour, minute, second)
computing those year, month, &c, in your application code.
Alex Martelli
2009-06-15 01:13:34
how do you get a count of that query?
erotsppa
2009-06-15 01:16:46
In the Python App Engine, you make a GqlQuery out of that and call the .count() method on it; there's no way to smush the COUNT right into the Gql itself.
Alex Martelli
2009-06-15 01:20:21
And note that counting is inefficient - it's O(n) with the number of counted entities, and in App Engine, can't count to more than 1000.
Nick Johnson
2009-06-15 08:51:44
@Nick: O(1), then ;-)
Steve Jessop
2009-06-17 00:06:05
+3
A:
I know you say GQL, but here's a python helper function I use:
import datetime
def seconds_ago(time_s):
return datetime.datetime.now() - datetime.timedelta(seconds=time_s)
There may well be a more concise way to write it: I'm not a python expert and went with the first thing that worked. Take a look at the datetime docs if you care. It's used like this:
my_query = MyTable.all().filter("date >", seconds_ago(6*60*60))
I'm sure that can be translated to GQL without much bother, but I prefer the object-oriented interface, and I don't know the necessary DATETIME syntax.
In python the query is then used like this:
# get a count
my_query.count()
# get up to 1000 records
my_query.fetch(1000)
# iterate over up to 1000 records
for result in my_query:
# do something with result
Steve Jessop
2009-06-15 01:22:35