Commerce Server handling of DateTime values and time zones

  • Article

The different systems in Commerce Server take somewhat different approaches for how they store DateTime values.  The desired convention is that DateTimes are stored in the database in Universal Time (UTC, or GMT if you prefer) and the API's surface and consume DateTimes in local time.  The design rationale for storing DateTimes in UTC is that UTC is unambiguous and, well, universal.  So if you were to back up the database in a datacenter in one time zone and restore it in another, the datetime data would still be valid and unchanged in meaning.  Campaigns would still start and stop at the universal time at which they were configured to do so, for example. 

Unfortunately due to legacy reasons some of the subystems continue to use local time for storage.  They are all consistent, however, in surfacing DateTimes in the local time zone through the public API's.  So at least Web site developers don't have to deal with any of this complexity.  Local time is generally desirable at the API layer for developer convenience since typically that's what you would display to an end user on the site, and .NET 2.0 makes it trivially simple to covert a DateTime between local and UTC without data loss.

DateTime.Kind and Web Service Serialization

Before we get into all the Commerce Server ideosyncracies around DateTime, let's look at how .NET 2.0 deals with DateTimes.  In .NET 1.0 and 1.1, DateTimes didn't have any TimeZone specification associated with them, which led to a number of problems.  It was up to the application/system to decide what convention would be used - e.g. local, UTC, or some fixed timezone.  But when it came time to serialize a DateTime data type into XML, for example as part of a Web service call, the .NET serialization engine needs to make an assumption.  The assumption made in .NET 1.0 and 1.1 was that the DateTime is local time, so you better not violate that unless you're doing your own serialization (as a string that includes TZ information, for example).

If you were to look at the SOAP message body, however, you'll see that the XML is unambiguous - the DateTime value includes an offset from UTC.  That's how you can have a client and server in different timezones and not lose the universal nature of a DateTime across the Web service call.  And in time zones that use daylight savings time like here in the US, .NET would get it wrong for an hour of the year in the fall due to the ambiguous nature of times between 1:00 and 2:00 AM on the day of the transition.

Fortunately, .NET 2.0 addresses the ambiguity issue with DateTimes by introducing the DateTime.Kind enumeration property.  This can be any of the values Local, Utc, or Unspecified.  Anthony Moore covers the enhancements in some detail in this blog post.  He also describes how the DST transition problem has been addressed so you can reliably convert from UTC to local and back again without any danger of a conversion error, every hour, for all 365 days of the year.

Note that the BCL team is working on further enhancements for TimeZone support for the upcoming Orcas release of the framework.

DateTimes and DataSets

Commerce Server 2007 is architected for .NET 2.0, and given that DateTime is fixed to be unambigous in 2.0 and so long as we made sure all of the serializable entity properties of DateTime type were unambiguously specified as local or UTC, we were done, right?  Well, almost, but not quite.  DataSets in .NET 2.0 also got a little knowledge about DateTime and time zones. DataSets now use the DataSetDateTime enumeration to specify the timezone of DataColumns containing DateTime values.  The default is interesting - UnspecifiedLocal.  Huh?  How can it be both unspecified and local?  Well, this type of DateTime column is unspecified for all purposes except serialization, where it's treated as if it were a local time.  As we'll see, that's perfect for Catalog, which uses DataSets heavily and stores DateTime values in local time (unspecified, really, but assumed local or otherwise by the application).  In that case, the DateTime values in the DataSet are already in local time and serialize and translate perfectly across clients and servers in different timezones.  But for the Orders and Marketing systems, which use UTC storage in the database and also use DataSets in some cases, Commerce Server needs to specify the dates as UTC, then converts the times in the column to local.

CS2007 Orders and Marketing Systems

The Marketing and Orders systems follow the desired convention of storing data in UTC in the database and returning them in local time at the API layer.  This includes the format of DateTimes in the campaigns caches.

Note:  When migrating data for these systems from CS2000 or CS2002, existing data for Orders and Marketing will be converted from local time to UTC since the previous versions of the product stored the data in local time for these resources.

CS2007 Product Catalog System

The Product Catalog system in previously releases of Commerce Server did not make any assumption about the time zone of DateTime data type properties stored within the catalog.  You simply got out the value you put in, much as with SQL server.

But in order to correctly serialize DateTimes across the Web service boundary and when client and server may be in different time zones, we had to put a stake in the ground.  For better or worse we decided to go with the assumption that the DateTime values are in local time.  As long as your DB isn't restored to a new time zone or something weird like that, it should not represent a problem for you.

The big thing to be aware of with DateTimes in the catalog system is the XML representation.  The XML representation used by catalog unfortunately does not include TimeZone information and so this is something to be aware of in any of the following situations:

  • When using BizTalk adapters to exchange catalogs across time zones
  • When using the Commerce Server staging feature when staging catalogs across time zones
  • When otherwise dealing with catalong import/export across time zones

In these cases, the same value that was exported to the XML will be imported on the other system with no accounting for the local time zone.

The Catalog System lets you specify queries directly using SQL syntax.  This leads to some interesting challenges in allowing the date-time translation to work between the CatalogManager application and the Catalog Web service when they are in different time zones.  So you might notice the CatalogManager application constructing searches like this to make the conversion explicit:

[IntroductionDate] < DATEADD(mi, DATEDIFF(mi, getutcdate(), getdate()), N'20061202 16:00:00')

in the UI, this search was specified as: "Date of Introduction Before 12/2/2006 8:00:00 AM (PST)".  So the UI converts it to UTC, then uses the DateAdd/DateDiff functions to get it explicitly converted as part of the query.  A little bit round-about way to get there, but it works!

CS2007 Profiles System

The Profiles system treats DateTime values similarly to the catalog system.  Storage of DateTimes uses the local time zone.  This is an important assumption here, as the Membership provider will set a lot of DateTime values for you automatically by default.  Queries and results in the Profiles system agent API are represented in XML including the TZ offset and so don't have the same ambiguity issues that catalog XML import-export does.

CS2007 Data Warehouse

The Data Warouse has a configurable time zone storage scheme.  You can configure the time zone and start day of week that the Data Warehouse uses to store DateTime information.  This can be configured from Commerce Manager from the Properties tab of the Data Warehouse Global Resource (figure below).  By default the Data Warehouse uses the time zone and start day of week based on the SQL Server settings at the time the Data Warehouse resource is first created.

The Data Warehouse Time Settings 
Notice that these settings impact only data newly imported through the DTS tasks, so be sure to consider configuring this as appropriate when you first set up your Data Warehouse since you would otherwise need to reimport your data after a configuration change to get the setting applied consistently to all of your data.

The Commerce Server OLEDB Provider (CSOLEDB) is used by the DTS tasks to transfer data from the runtime tables to the Data Warehouse. The CSOLEDB Provider expects dates in UTC and writes them to the Data Warehouse in the configured time zone (described above).

All World Wide Web Consortium (W3C) log files are always logged in UTC and converted by the Web Log Import DTS task.