Date Time managed property-- Issues and workarounds

When you define Date Time column in SharePoint, you have two options

You map this column with date time managed property. By the time it gets indexed in FS4SP, it is indexed with date time.

Following table gives an idea about this, for a farm in CST.

From the perspective Query, you can use either KQL or FQL. KQL is supported OOTB from search center.

KQL

This does not support date time query. You can only
specify date and not time in the query.
Each query that you run is converted into range query

FQL

As KQL is most used, let us understand how each of Date query works with KQL

This is from OOTB Search center and executing queries from east coast(CST).

For the KQL query, where user is looking for documents for certain date--> datesubmitted:2012-07-12
- The final query that is executed within FAST for this is range query looking for documents between 2012-07-12T06:00:00Z and
  2012-07-13T06:00:00Z
For the KQL query, where user is looking for documents earlier than certain date--> datesubmitted <2012-07-13
- For this FAST will look for documents earlier than 2012-07-13T06:00:00Z
For the KQL query, where user is looking for documents later than certain date--> datesubmitted >2012-07-11
- For this FAST will look for documents later than 2012-07-12T06:00:00Z

Large Enterprise Scenario

In typical large enterprise, user could add/update documents from anywhere in the world. Depending on where they are, local time
would vary. So when London user enter 2012-07-12 for date submitted, that specific time is represented differently for someone Houston.
Similarly users who is searching for content could be anywhere in the world and their local time varies accordingly.

In order to solve this problem, FAST does the following

Date time properties are represented as UTC in Index
Then when user query depending on where they are, search page should convert the query to UTC and execute.

So what is the issue with this approach?

As you can see, this will not work when daylight saving time is enabled.

Note that the following example is based on a FAST farm running in CDT (Central Daylight Time).

Date Submitted is Date and Time property (with date only setting). Assume that it has vale 07-10-2012
This will indexed as 2012-07-12T05:00:00Z in FAST

Following steps details how this query executed. This is from OOTB Search center and executing queries from a farm in CDT.

For the KQL query, where user is looking for documents for certain date--> datesubmitted:2012-07-12
- The final query that is executed within FAST for this is range query looking for documents between 2012-07-12T06:00:00Z and
  2012-07-13T06:00:00Z
- Obviously the above document will not be picked as it is not within this range.
For the KQL query, where user is looking for documents earlier than certain date--> datesubmitted <2012-07-13
- For this FAST will look for documents earlier than 2012-07-13T06:00:00Z
- The above document will be returned. But then you will also get document whose date submitted is 2012-07-13.
For the KQL query, where user is looking for documents later than certain date--> datesubmitted >2012-07-11
- For this FAST will look for documents later than 2012-07-12T06:00:00Z
- For this above document will not be returned.

So the main factors that complicate this is daylight saving Time. This affect both the query that is executed and actual value indexed in FAST.

What is the work around to resolve this problem?

This can be resolved simply as follows

Regional settings can be set at web application level, site collection or site level
In order to minimize the effect of changing the regional setting, you can create search center at site collection level or at site vel.
Change the time zone to a zone that is at least 1 hour ahead to the time zone where the FAST farm is located. It is fun to analyze the queries after changing the time zone. Go ahead and play with it.

Note that this will not work when you have Date and Time property (with Date and Time setting).

Couple of articles that give additional details about this topic

Comments

Anonymous
June 25, 2014
I found in SP2013, the OOB managed property "ArticleStartDate" do not follow the regional settings. Hence all search to this property MUST use GMT-0. Is it a bug? Any workaround? Thanks.
Anonymous
January 04, 2015
Seems that datetime values are stored as UTC time but when performing a query it does not do the conversion of you timezone. I live in Belgium UTC + 1 hour. If I store a date for example: 2014-12-29. I expect that it is stored as 2014-12-29 00:00:00. Since it's converted to UTC it's actualy stored as 2014-12-28 23:00:00 When I want to find the document I need to search on the 2014-12-28 date instead of the 2014-12-29 date. Very confusing.