3

I'm attempting to format a GQL query that pulls data between two dates. I've referred to several existing StackOverflow threads (GQL SELECT by date for example), and have tried following the formatting shown there, but for some reason when I test my query out it gives me an error.

Here is the query I'm attempting to use:

SELECT * FROM Packets WHERE timestamp > DATETIME(2017,12,23) AND timestamp < DATETIME(2017,12,29) LIMIT 10

It gives this error:

"GQL query error: Encountered "2017" at line 1, column 50. Was expecting one of: <SINGLE_QUOTE_STRING>, <DOUBLE_QUOTE_STRING>"

I've tried enclosing the dates in strings, I've tried using the DATE object, every format I can think of gives me some sort of error. What am I doing wrong?

James
  • 736
  • 1
  • 5
  • 19

2 Answers2

3

The error is right, the DATETIME method needs a single string parameter.

According to the GQL reference, to instanciate a DATETIME in a query the format must be 'YYYY-MM-DDThh:mm:ss.SSSSSS+zz:ZZ':

DATETIME DATETIME() represents a timestamp. must be in the time format specified in RFC 3339 section 5.6. (However, the second precision is limited to microseconds and leap seconds are omitted.) This standard format is: YYYY-MM-DDThh:mm:ss.SSSSSS+zz:ZZ ...

Your example working:

SELECT * FROM Packets WHERE timestamp > DATETIME('2013-09-20T09:30:20.00002-08:00') AND timestamp < DATETIME('2013-09-29T09:30:20.00002-08:00') LIMIT 10

You can check the complete article here : https://cloud.google.com/datastore/docs/reference/gql_reference

Pablo Chvx
  • 1,809
  • 18
  • 31
  • I tried this, being careful to set the date range to match entities I verified were in the datastore: SELECT * FROM Packets WHERE timestamp > DATETIME('2017-04-03T09:30:20.00002-08:00') AND timestamp < DATETIME('2017-04-07T09:30:20.00002-08:00') LIMIT 10 And it still returned no queries. Any idea why not? – James Jun 15 '17 at 20:51
  • Make sure that your 'timestamp' property is indexed. If it is not indexed, no results will return and also no error. – Pablo Chvx Jun 15 '17 at 21:30
  • Yes, it's indexed. – James Jun 15 '17 at 21:31
  • Maybe the entities that match the query arent indexed? Entities of a type can be mixed (indexed/unindexed) – Pablo Chvx Jun 15 '17 at 21:41
  • I can find them using "query by kind", which shouldn't be possible if they aren't indexed. – James Jun 15 '17 at 21:43
  • 1
    I figured it out, I think. Typo in query. – James Jun 15 '17 at 21:45
  • Thanks a lot, I can do my query!! – Richard Rebeco Sep 12 '18 at 20:38
0

Thanks for comment this problem.

With the answers of each one I can do a very easy (but almost impossible in GQL) Query.

Check this out, I hope it will help to someone:

SELECT * FROM Task WHERE recordDate >= DATETIME('2018-09-09T00:00:00.00000-03:00') 
AND recordDate <= DATETIME('2018-09-20T23:59:59.99999-03:00')

Where "2018-09-09T00:00:00.00000-03:00" is the full datetime value and it means:

  • 2018-09-09 -> Date Indicator (YYYY-MM-DD in my case)
  • T -> Indicator that the next values are Time values
  • 00:00:00.00000 -> Time Indicator (HH:mm:ss:[miliseconds])
  • -03:00 -> Time Zone indicator (Chile in my case)

I really hope this post will be useful to anyone that have the same trouble with dates using GQL

Richard Rebeco
  • 753
  • 11
  • 13