The curious incident of the date column in the night-time

I have been tearing my hair out over a problem with dates and timezones. We have a site column ‘Document Date’ that we are using in our search results page. It only holds a date.

Our client noticed that some dates appeared on the search results page as a day earlier than the value in the lists themselves. For example, the list would show a document date of the 8th of May, but the search results would show the 7th of May. Curious, and the start of a bit of a detective story…

Tracking through the erroneous dates it became clear that these incorrect dates only occurred during British Summer Time (BST). For 2008, the change to BST happened in the last weekend of March (30-31st). The list view and search results for doucments with dates spanning that time show…

List View of Dates around the Summer-time Boundary Search View of documents with dates around the summer-time boundary

This was puzzling, so I took a look inside SQL server to see what was being stored there. You shouldn’t ever work with the database directly – and shouldn’t need to – but I figured there was no harm in looking:

SQL Server view of documents with dates near the summer-time boundary

This is correct. SharePoint actually holds dates as DateTime columns, with the time portion set to zero hundred hours (0:00:00). During the summer time, the UK is on GMT+1, whereas SharePoint always just uses UTC (which for all practical purposes in SharePoint is the same as GMT). Thus, to correct for summer time, our date ( 2008-05-08 0:00:00 ) gets clocked back an hour to UTC time ( 2008-05-07 23:00 ). You can see this happening for the first day of British Summer Time on the line highlighted in yellow.

To reiterate, this is entirely correct. All times in SharePoint are stored as UTC, and then adjusted to the local time when it comes to presentation. Thus, offices in London, Tokyo and New York all use the same ‘clock’ when storing things (the UTC time), but will adjust the displayed time appropriately.

However, looking at our search results, we can see that the date we’re getting is now wrong, due to this time zone shift. The subtraction of an hour moves us into the day before, so our date is wrong! We really don’t want this time shift – heck, we don’t even want the time part, and our customer is entirely within one time-zone.

Search View of documents with dates around the summer-time boundary XML view of results from the search webservice for document dates around the summer time boundary

Therefore, the problem seems to be that the Shared Service Provider isn’t using the same timezone for crawling the content as is used by the users.

Now, I would suggest that maybe search doesn’t adjust the timezone – after all, a single site collection could contain sites from multiple timezones. However, when I tried to replicate the customer’s problem on my system I found that:

  • Summer-time dates went in as 2300 hours the day before, but…
  • … the document date value shown in the search results was correct!

So, it should work okay, but there is something different between my system and theirs. So, what’s different?

  • I examined the site collection’s regional settings – these were the same between my system and the customer’s:
    Site Collection Regional Settings
  • I examined the regional settings of the SSP – these were the same:
    Shared Service Provider Regional Settings
  • I checked the user’s regional settings – these were the same:
    User's Regional Settings

Next, I checked the server’s own time settings. Their server was set to GMT, but not adjusting for daylight savings time. I set mine the same, rebooted and reindexed – and started to get the same problem!

Windows Timezone dialog on the Server

A bit more testing showed that only the changing the ‘Automatically adjust clock for daylight saving changes‘ and the reboot are actually required. This is pretty interesting as it does mean that the search index and query server must work in UTC time too. I guess that makes sense.

Anyway, if you have this problem, the short answer is enable adjusting the servers clock for daylight saving, and reboot.

The curious incident of the date column in the night-time

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.