GQL Query (python) to retrieve data using timestamp

Question

I have a table in Google Datastore that holds n values in n columns, and one of them is a timestamp.

The timestamp property is defined like this, inside the table class (Java):

@Persistent
private Date timestamp;

The table is like this:

    id    |    value    |            timestamp    
----------------------------------------------------------
     1    |    ABC      |    2014-02-02 21:07:40.822000   
     2    |    CDE      |    2014-02-02 22:07:40.000000   
     3    |    EFG      |       
     4    |    GHI      |    2014-02-02 21:07:40.822000   
     5    |    IJK      |       
     6    |    KLM      |    2014-01-02 21:07:40.822000

The timestamp column was added later to the table, so some rows have not the corresponding timestamp value.

I'm trying, using Python Google App Engine to build an api that returns the total number of rows that have a timestamp >= to some value.

For example:

-- This is just an example
SELECT * FROM myTable WHERE timestamp >= '2014-02-02 21:07:40.822000'

I've made this class, in python:

import sys
...
import webapp2

from google.appengine.ext import db

class myTable(db.Model):
    value = db.StringProperty()
    timestamp = datetime.datetime

class countHandler(webapp2.RequestHandler):
    def get(self, tablename, timestamp):

        table = db.GqlQuery("SELECT __key__ FROM " + tablename + " WHERE timestamp >= :1",  timestamp )
        recordsCount = 0

        for p in table:
            recordsCount += 1

         self.response.out.write("Records count for table " + tablename + ": " + str(recordsCount))

app = webapp2.WSGIApplication([
    ('/count/(.*)/(.*)', countHandler)
], debug=True)

I've successfully deployed it and I'm able to call it, but for some reason I don't understand it's always saying

Records count for table myTable: 0

I'm struggling with the data type for the timestamp.. I think the issue is there.. any idea? which type should it be declared?

Thank you!

Are you sure that timestamp is not a string ? It needs to be datetime in my knowledge. — Jimmy Kane, Feb 19 '14 at 14:01
@JimmyKane I've defined it as `Date` in the Java class, so it should be date.. I'll give it a try, python side, with `timestamp = db.StringProperty()` if this is what you mean — BeNdErR, Feb 19 '14 at 14:25
I think that you need to have timestamp in a datetime format. Just convert it to datetime and use that object for the query. Inform if it worked. I have some time to use it in my queries and atm I don't have the convenience to test. — Jimmy Kane, Feb 19 '14 at 14:29
@JimmyKane sorry to be boring, but you mean python or java side? (maybe in python there's some `datetime` different than `datetime.datetime`?) — BeNdErR, Feb 19 '14 at 14:38
no it's datetime.datetime (if you import datetime). What I mean is that I can see in the python side(where your problem is) that you use the timestamp as a string. I think you should convert that to datetime object. Look here for help. http://stackoverflow.com/questions/969285/how-do-i-translate-a-iso-8601-datetime-string-into-a-python-datetime-object Later if I have some time I will setup a test case for this. — Jimmy Kane, Feb 19 '14 at 14:46
@JimmyKane I thought that the parameterized query maintained the type.. I'll test your suggestion asap, thank you! — BeNdErR, Feb 19 '14 at 14:51
@JimmyKane your hint solved my problem, thank you. If you reply to the original question, I'll accept your answer! thanks again! — BeNdErR, Feb 19 '14 at 16:37

score 2 · Accepted Answer · edited May 23 '17 at 12:25

2

You problem (as discussed in the comments as well) seems to be that you are passing a string (probably) to the GqlQuery parameters.

In order to filter your query by datetime you need to pass a datetime object in to the query params. For that take a look here on how to convert that.

Small example:

# not sure how your timestamps are formatted but supposing they are strings
# of eg 2014-02-02 21:07:40.822000
timestamp = datetime.datetime.strptime(timestamp, "%Y-%m-%d %H:%M:%S.%f" )

table = db.GqlQuery("SELECT __key__ FROM " + tablename + " WHERE timestamp >= :1",  timestamp)

edited May 23 '17 at 12:25

Community

1
1

answered Feb 19 '14 at 16:54

Jimmy Kane

16,223
11
86
117

1

Actually, I used the other example: `tmstmp = dateutil.parser.parse(timestamp)`. Note that, this way, I needed to download and import dateutil lib. – BeNdErR Feb 19 '14 at 17:02
@BeNdErR Sure that will work as well. I am a bit more familiar the datetime module. Glad that it solved it. – Jimmy Kane Feb 19 '14 at 17:04
1

probably your solution would have saved me the import of the new lib :) – BeNdErR Feb 19 '14 at 18:00

GQL Query (python) to retrieve data using timestamp

1 Answers1