GAE Search API Query with "Date = YYYY-MM-DD" returns no results

Question

I am trying to perform a simple Date query on Google App Engine using the Search API. All I am trying to achieve is to obtain a list of all Documents that have a Field equal to a certain Date...

// Prepare the Query
String searchString = "date = 2013-08-26";
Query query = Query.newBuilder().build(searchString);

// Get the Date_Search Index
IndexSpec indexSpec = IndexSpec.newBuilder().setName("Date_Search").build();
Index index = SearchServiceFactory.getSearchService().getIndex(indexSpec);

// Perform the search
Results<ScoredDocument> results = index.search(query);

When I run the above code, no results are returned. However, if I change the query to be date >= 2013-08-26 && date < 2013-08-27 instead, the expected results are returned. It seems as though the date equality logic isn't working.

This is the list of Documents in my Index...

DocId                                   OrderId     date        event_key
3d4e5691-8fb9-42de-bf09-f2303236f091    84288787    2013-08-25  641.0
7e4700fe-dee1-4579-87c5-69b1b1929f39    84288787    2013-08-25  650.0
8c9ca43d-7673-4f12-9f0b-e3328cbdaa85    84288787    2013-08-26  659.0
04fa8025-e01b-42d3-9bf9-21c3d9618ca7    84288787    2013-08-26  668.0
41465d1f-6d8a-431f-b226-141c8d72c064    84288787    2013-08-26  676.0
1ba01a6b-0890-43b3-8225-0ed196b6ee80    84288787    2013-08-27  676.0
a8ef18b1-ffa5-4823-8442-852f7142cad1    84288786    2013-08-25  633.0

I would be expecting that 3 documents would be returned for the date 2013-08-26. When running with the equality date = 2013-08-26 there are no results, but the query date >= 2013-08-26 && date < 2013-08-27 returns the expected 3 results.

The Documents were added to the Index using the following code...

// Build the Date
Calendar calendar = Calendar.getInstance();
Date dateObject = calendar.getTime();

// Create the Document
Builder builder = Document.newBuilder();
builder.addField(Field.newBuilder().setName("event_key").setNumber(eventKey));
builder.addField(Field.newBuilder().setName("date").setDate(dateObject));
Document document = builder.build();

// Get the Date_Search Index
IndexSpec indexSpec = IndexSpec.newBuilder().setName("Date_Search").build();
Index index = SearchServiceFactory.getSearchService().getIndex(indexSpec);

// Store the Document
index.put(document);

According to the Search API Documentation, Date fields are stored and are query-able only to YYYY-MM-DD precision(the Time information is stripped out when storing and querying). Additionally, the Query on Date Fields Documentation shows that date equality queries are supported.

Could someone please help me to understand what the issue is.

Answer 1

This issue has been resolved in SDK 1.8.4.

Please take note that upgrading to 1.8.4 caused my development datastore to become invalid, as Google are allowed to do if required. Once I recreated the data in the 1.8.4 datastore, the Search API worked correctly for the equality Date Search.

Answer 2

Your use of Search for dates is correct. There were bugs in this area, but they have been fixed in version 1.8.4.

A couple of points of clarification:

1. Dates are stored at millisecond resolution, but they're indexed at a resolution of one day. So, for purposes of searching and sorting we have only the date, not the time of day. But when you actually examine a retrieved document, any Date fields will have been restored to their millisecond precision.

2. Dates that appear in a query (in YYYY-MM-DD form) are considered in the UTC time zone. The implication of this is a bit subtle:

   Let's say you're sitting in Australia at 2:00am on 2013/9/10, and you decide to insert a document with the then-current time. Over in Greenwich it's not yet midnight; the date is still September 9. If you search for the document by date, you would find it with [date=2013/09/09] but not with [date=2013/09/10].

   Similarly, if you're sitting in California at 9:00pm one night, it's already past midnight in UTC ...

3. There seems to be a bug in the dev server admin console. On the Full Text Search page, if you display document content that includes a date, the date is rendered in the local time zone instead of UTC. And it also does not include the time component.

Answer 3

Looks like your date is stored not only with year-month-day info but with hours, minutes, seconds and miliseconds.

There can be two solutions:

1. Make sure you are set not year-month-day part to zero

2. Search by range: date >= currentDay && date < nextDay