Gravatar for

Question by Simon, Nov 5, 2014 4:40 PM

Date field not returning the expected results.

Using the following query, I would expect an item with the following value for that field to be returned: '9/1/2014 12:00:00 AM':

(@foriginalz32xpostedz32xdate29877>=2014/09/01 AND @foriginalz32xpostedz32xdate29877<2014/09/02)

But it does not return anything. With this query:

(@foriginalz32xpostedz32xdate29877>=2014/08/31 AND @foriginalz32xpostedz32xdate29877<2014/09/01)

There are results.

Could it be because of the way date-only fields in Sitecore are indexed (having the 12:00AM time tacked on)? Could the processor be calculating '9/1/2014 12:00:00 AM' as '8/31/2014 11:59:59 PM'?

Is there a format to filter by time as well as date?


1 Reply
Gravatar for

Answer by Martin Laporte, Nov 6, 2014 4:38 AM

That is certainly the problem, as a date with no time defaults to midnight. The syntax for specifying time in a date query is described here:

Gravatar for

Comment by Cris Corra, Nov 6, 2014 11:26 AM

Thank you. I've tried adding the time syntax, but I'm still not getting expected results. For my item with a date value of "9/1/2014 12:00:00 AM", here are my results:

(@foriginalz32xpostedz32xdate29877=2014/09/01@12:00:00) - does not return my item

(@foriginalz32xpostedz32xdate29877<2014/09/01@12:00:00) - does return my item

Gravatar for

Comment by Martin Laporte, Nov 7, 2014 3:17 AM

It might be a timezone thing. Dates entered in the query are adjusted depending on your timezone, whereas dates in the index are stored in GMT and offset as appropriate at query time. I'm pretty sure the 12 o'clock time that is put by the Sitecore connector is in GMT.

E.g. if you query for @date=…@12:00:00 it might instead search for @date=…@7:00:00 (GMT), depending on where you are (by you I mean your browser).

I think the root issue is really that the connector shouldn't put a time part for dates with no time. I had a discussion about that yesterday with the Sitecore folks @ Coveo.

Gravatar for

Comment by Cris Corra, Nov 7, 2014 9:34 AM

The servers and browsers of our current users are in the same timezone. Is there a way to specify GMT when passing the query? I agree the time should not be added if it doesn't exist, and I look forward to a solution for that. But in the meantime, we are testing at a Staging level and need this to work.

Gravatar for

Comment by Martin Laporte, Nov 7, 2014 10:10 AM

You can specify the data-timezone attribute on the root element of your search interface (the one you call init on). Something like: data-timezone="UTC" should do the trick.

Gravatar for

Comment by Cris Corra, Nov 12, 2014 4:49 PM

I've had our Coveo developer working on this, and he has found the following case, described in his words: [I think I found something… I think it might have to do with the Daylight vs the Standard time change. If I do search for the month of January, I don't get results for February even if there is document for the 1st of February. Right now, we are GTM-5 but the document date for august or september is GTM -4. This is the date for the documents of September 1st: 1409544000000. Which is: Mon Sep 01 2014 00:00:00 GMT-0400 (Eastern Daylight Time).

Gravatar for

Comment by Cris Corra, Nov 12, 2014 4:49 PM


So Coveo is not doing the search on "the same timezone". We are now on gtm-5, so search for months which are in gtm-5 would be fine. For the months in gtm-4, I think we would have the problem.]

Can you confirm this is the issue?

Gravatar for

Comment by Martin Laporte, Nov 13, 2014 3:29 AM

I'm guessing that the Sitecore connector puts in a time of 12 o'clock in the current DST setting, instead of using the one relevant to the document date.

When performing a query with a time part the index is smart enough to adjust the filter depending on when the document dates are. E.g. if I want to find a email sent between 9 and 10 it'll work for emails all year round, even if the current DST setting is offset by one hour. That's why the JS UI sends the name of the timezone to the index instead of only a GMT offset, and believe me figuring out the timezone in JS is a fun endeavour.

Gravatar for

Comment by Martin Laporte, Nov 13, 2014 3:32 AM

(that character limit IS annoying)

My advice is to open a case with support about this. To me, setting a time part for dates when there is in fact none is not an appropriate behavior from the connector itself. At least they should address the DST thing (if my guesses are right), but I'd push for them to drop the time part entirely.

Ask a question