[google-appengine] Re: date range in query
I have applied the DATETIME method as suggested but unable to use
inequality operator for 2 datetime fields.
I have tried this too
b=db.gqlquery('SELECT * FROM myTable WHERE field1=:val1 and
sdate=DATETIME(2009,11,20,0,0,0)')
b=b.filter('where edate=DATETIME(2009,11,20,0,0,0)')
But it does not work. How can I use the comparsion so as to get
results based on using the two table fields?
Thanks
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
Google App Engine group.
To post to this group, send email to google-appengine@googlegroups.com
To unsubscribe from this group, send email to
google-appengine+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/google-appengine?hl=en
-~--~~~~--~~--~--~---
[google-appengine] Re: date range in query
gql as below works.
SELECT * FROM Image where added DATETIME(2009,1,1,0,0,0) and added
DATETIME(2009,2,1,0,0,0)
we must use the integer form (the manual lists b0rked forms)
http://code.google.com/p/googleappengine/issues/detail?id=878
cheers
niklas
On Feb 3, 12:02 pm, arnie parvez...@rediffmail.com wrote:
For a datastore table with two db.DateTimeProperty columns start_date
and end_date, i need to use a date range in query but below given
query is not working
current_date=datetime.datetime.strptime(self.request.get
('current_date'), '%Y-%m-%dT%H:%M:%S')
db.GqlQuery('SELECT * FROM myTable where start_date=:cdate and
end_date=:cdate',cdate=current_date)
in the parameters I am passing current_date=2009-01-10T00:00:00
Should we not compare dates this way?
But this is not working
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
Google App Engine group.
To post to this group, send email to google-appengine@googlegroups.com
To unsubscribe from this group, send email to
google-appengine+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/google-appengine?hl=en
-~--~~~~--~~--~--~---
[google-appengine] Re: date range in query
Hi,
db.GqlQuery('SELECT * FROM myTable where start_date=:cdate and
end_date=:cdate',cdate=current_date)
You are trying to use in-equality filters on two properties in a
single query.. Does this not raise an Exception?
There was a post on how to do this more efficiently in the groups
somewhere, but in your case I think you need to query based on one
date from the datastore and do some logic in the view.
SELECT * FROM Image where added DATETIME(2009,1,1,0,0,0) and added
DATETIME(2009,2,1,0,0,0)
In this example both are applied to the same property.
Thanks,
Arun Shanker Prasad.
On Feb 3, 4:17 pm, niklasr nikla...@gmail.com wrote:
gql as below works.
SELECT * FROM Image where added DATETIME(2009,1,1,0,0,0) and added
DATETIME(2009,2,1,0,0,0)
we must use the integer form (the manual lists b0rked
forms)http://code.google.com/p/googleappengine/issues/detail?id=878
cheers
niklas
On Feb 3, 12:02 pm, arnie parvez...@rediffmail.com wrote:
For a datastore table with two db.DateTimeProperty columns start_date
and end_date, i need to use a date range in query but below given
query is not working
current_date=datetime.datetime.strptime(self.request.get
('current_date'), '%Y-%m-%dT%H:%M:%S')
db.GqlQuery('SELECT * FROM myTable where start_date=:cdate and
end_date=:cdate',cdate=current_date)
in the parameters I am passing current_date=2009-01-10T00:00:00
Should we not compare dates this way?
But this is not working
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
Google App Engine group.
To post to this group, send email to google-appengine@googlegroups.com
To unsubscribe from this group, send email to
google-appengine+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/google-appengine?hl=en
-~--~~~~--~~--~--~---
[google-appengine] Re: Date range overlapping query in Datastore
Thanks a lot! That's helpful!
On Dec 25, 1:58 am, Garrett Davis garrettdavis...@gmail.com wrote:
You can handle your query if you add another field in your Kind, a
ListProperty containing both the start date and end date.
Try this:
class FicaTable(db.Model):
a table of salary amounts subject to Social Security tax, by
tax year
eff_date = db.DateTimeProperty()
exp_date = db.DateTimeProperty()
date_range = db.ListProperty(datetime.datetime)
max_tax_base = db.IntegerProperty()
OASDI_RATES = \
2001 80400
2002 84900
2003 87000
2004 87000
2005 9
2006 94200
2007 97500
2008 102000
2009 106800
def flush_tax_tables():
flush any leftover data from previous runs
query_all_records = FicaTable.all()
rec_count = query_all_records.count()
if rec_count:
print 'Deleting %d records from datastore' % (rec_count)
for rec in query_all_records:
rec.delete()
def parse_date(_mm_dd):
return datetime.datetime(*time.strptime(_mm_dd, %Y-%m-%d)
[0:3])
def load_tax_tables():
for table_data in OASDI_RATES.splitlines():
(year, max_tax_base) = table_data.split()
eff_date = parse_date(year + '-01-01')
exp_date = parse_date(year + '-12-31')
table_vals = {'eff_date' : eff_date,
'exp_date' : exp_date,
'date_range': [eff_date, exp_date],
'max_tax_base': int(max_tax_base)
}
table_rec = FicaTable(**table_vals)
table_rec.save()
query_all_records = FicaTable.all()
rec_count = query_all_records.count()
print 'Loaded %d tax table records' % (rec_count)
from google.appengine.api.datastore_errors import BadFilterError
def query_tax_tables():
this fails with the message: invalid filter:
Only one property per query may have inequality filters ...
query_template = WHERE eff_date = :1 AND exp_date = :1
q = FicaTable.gql(query_template, datetime.date(2005,05,15))
print
print Trying 'traditional' query with two date fields
try:
rec = q.fetch(1)
except BadFilterError:
print I told you this wouldn't work
but this works:
print
print Trying query against date_range
query_template = WHERE date_range = :1 AND date_range = :1
query_date = datetime.date(2005,05,15)
q = FicaTable.gql(query_template, query_date)
table_rec = q.get()
date_str = query_date.isoformat()
# datetime.time.strftime(%Y-%m-%d, query_date)
print 'The maximum amount subject to FICA OASD for %s is %d' % \
(date_str, table_rec.max_tax_base)
if __name__ == __main__:
flush_tax_tables()
load_tax_tables()
query_tax_tables()
Let me know if anyone has any problems understanding or running the
example.
After I'm sure it's accurate and well-enough documented, I should post
it to the App Engine 'cookbook':http://appengine-cookbook.appspot.com/
Garrett Davis
On Dec 23, 2:23 pm, maverick zhihong@gmail.com wrote:
Due to the limitation of GAE's datastore Inequality Filters Are
Allowed On One Property Only, a simple date range overlapping query
become a problem.
I have a Kind which contains 2 properties: date_begin, date_end, I
need to run a query to find out all rows which overlapping the date
range given (datebegin2, dateend2). in a SQL query it just simple
@StartDate1 = @EndDate2 AND @StartDate1 = @EndDate1 in WHERE
clause, however in GQL, we have a problem.
Does anyone have a solution for this? Thanks !
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
Google App Engine group.
To post to this group, send email to google-appengine@googlegroups.com
To unsubscribe from this group, send email to
google-appengine+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/google-appengine?hl=en
-~--~~~~--~~--~--~---
[google-appengine] Re: Date range overlapping query in Datastore
You can handle your query if you add another field in your Kind, a
ListProperty containing both the start date and end date.
Try this:
class FicaTable(db.Model):
a table of salary amounts subject to Social Security tax, by
tax year
eff_date = db.DateTimeProperty()
exp_date = db.DateTimeProperty()
date_range = db.ListProperty(datetime.datetime)
max_tax_base = db.IntegerProperty()
OASDI_RATES = \
2001 80400
2002 84900
2003 87000
2004 87000
2005 9
2006 94200
2007 97500
2008 102000
2009 106800
def flush_tax_tables():
flush any leftover data from previous runs
query_all_records = FicaTable.all()
rec_count = query_all_records.count()
if rec_count:
print 'Deleting %d records from datastore' % (rec_count)
for rec in query_all_records:
rec.delete()
def parse_date(_mm_dd):
return datetime.datetime(*time.strptime(_mm_dd, %Y-%m-%d)
[0:3])
def load_tax_tables():
for table_data in OASDI_RATES.splitlines():
(year, max_tax_base) = table_data.split()
eff_date = parse_date(year + '-01-01')
exp_date = parse_date(year + '-12-31')
table_vals = {'eff_date' : eff_date,
'exp_date' : exp_date,
'date_range': [eff_date, exp_date],
'max_tax_base': int(max_tax_base)
}
table_rec = FicaTable(**table_vals)
table_rec.save()
query_all_records = FicaTable.all()
rec_count = query_all_records.count()
print 'Loaded %d tax table records' % (rec_count)
from google.appengine.api.datastore_errors import BadFilterError
def query_tax_tables():
this fails with the message: invalid filter:
Only one property per query may have inequality filters ...
query_template = WHERE eff_date = :1 AND exp_date = :1
q = FicaTable.gql(query_template, datetime.date(2005,05,15))
print
print Trying 'traditional' query with two date fields
try:
rec = q.fetch(1)
except BadFilterError:
print I told you this wouldn't work
but this works:
print
print Trying query against date_range
query_template = WHERE date_range = :1 AND date_range = :1
query_date = datetime.date(2005,05,15)
q = FicaTable.gql(query_template, query_date)
table_rec = q.get()
date_str = query_date.isoformat()
# datetime.time.strftime(%Y-%m-%d, query_date)
print 'The maximum amount subject to FICA OASD for %s is %d' % \
(date_str, table_rec.max_tax_base)
if __name__ == __main__:
flush_tax_tables()
load_tax_tables()
query_tax_tables()
Let me know if anyone has any problems understanding or running the
example.
After I'm sure it's accurate and well-enough documented, I should post
it to the App Engine 'cookbook':
http://appengine-cookbook.appspot.com/
Garrett Davis
On Dec 23, 2:23 pm, maverick zhihong@gmail.com wrote:
Due to the limitation of GAE's datastore Inequality Filters Are
Allowed On One Property Only, a simple date range overlapping query
become a problem.
I have a Kind which contains 2 properties: date_begin, date_end, I
need to run a query to find out all rows which overlapping the date
range given (datebegin2, dateend2). in a SQL query it just simple
@StartDate1 = @EndDate2 AND @StartDate1 = @EndDate1 in WHERE
clause, however in GQL, we have a problem.
Does anyone have a solution for this? Thanks !
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
Google App Engine group.
To post to this group, send email to google-appengine@googlegroups.com
To unsubscribe from this group, send email to
google-appengine+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/google-appengine?hl=en
-~--~~~~--~~--~--~---
