CODEWITHSUNDEEP
google-app-enginepython-2.7google-cloud-datastore
BeNdErR
BeNdErR

Reputation: 17927

GQL Query (python) to retrieve data using timestamp

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!

Upvotes: 0

Views: 907

Answers (1)

Jimmy Kane
Jimmy Kane

Reputation: 16825

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)

Upvotes: 2

Related Questions