The use of the date time stamp in applications has become more complex as the world got smaller thanks to proliferation of distributed systems, web applications and web services. Users and the applications are often times in disparate geographical locations. The user located on the East Coast may be running a web application executing on the server in a datacenter on the West Coast. From the user’s perspective, the application location is irrelevant. He expects it to execute correctly and display the date and time stamps in the local time zone meaningful to him. This poses challenges to the application architect. Applications displaying or depending on date and time need to be designed and deployed in a time zone neutral way. In a multi-tier application, the front back ends typically depend on the date and time. The front end displays information such as date of birth, account opening date, transaction date time, etc. This type of information needs to be consistent regardless of user’s geographical location. Two people located on two different continents, should see the transaction date time in their local time zone. In order to accomplish this, the application should solely rely on and utilize Universal Coordinated Time (UTC). According to Wikipedia, UTC is the time standard by which the world regulates clocks and time. Computer servers, online services and other entities that rely on having a universally accepted time use UTC for that purpose.
Since UTC is meaningless to the end user, it is the presentation layer’s responsibility to translate the UTC date time to the user’s local time zone.

The presentation layer needs to assume the UTC format of the date time received from the lower level tier. Having assumed the UTC format, the presentation layer is responsible for converting it to the local time zone. The C# conversion of a UTC date time to local date time is as following:

DateTime recordCreatedLocal = recordCreatedInUtc.ToLocalTime();

The same rules, but reverse conversion applies for the date time entered by the user in the application. User would enter the local date time which the presentation layer is responsible for converting to UTC format before sending it to the next tier.

DateTime recordCreatedUtc = recordCreatedInLocal.ToUniversalTime();

The middle tier such as webservice also needs to utilize the UTC format for date time communication between front and back ends.

Lastly, the back end such as database store should also use UTC format for date time fields as well as the metadata such as record creation date, record update date, etc. Even though the metadata is never displayed to the user, it may be used for data retrieval. For instance, let’s assume a security breach has occurred around 1 pm UTC time. We would query the database for all the records that were modified between 12 pm – 2pm based on the record modified date field. If the record modified date is stored in the UTC format then we could send a query to the database from any time zone as long as it is in UTC format regardless of the geographical location and time zone of the database server.

Time zone consideration should be a critical item during application design and deployement. Incorrect time zone format may render application unusable. It is especially important to consider proper date time format during database design. It is extremely tough to rectify database design oversights because of the dependencies of the upper tiers.

When designing, think globally.