Advice for Storing and Displaying Dates and Times Across Different Time ZonesBy Scott Mitchell
A common question I receive from clients, colleagues, and 4Guys readers is for recommendations on how best to store and display dates and times in a data-driven web application. One of the challenges in storing and displaying dates in a web application is that it is quite likely that the visitors arriving at your site are not in the same time zone as your web server; moreover, it's very likely that your site attracts visitors from many different time zones from around the world.
Consider an online messageboard site, like ASPMessageboard.com, where each of 1,000,000+ posts includes the date and time it was made. Imagine a user from New York leaves a post on April 7th at 4:30 PM and that the web server hosting the site is located in Dallas, Texas, which is one hour earlier than New York. When storing that post to the database do you record the post's date and time relative to the visitor (4:30 PM), the relative to the web server (3:30 PM), or some other value? And when displaying this post how do you show that date and time to a reader in San Francisco, which is three hours earlier than New York? Do you show the time relative to the person who made the post (4:30 PM), relative to the web server (3:30 PM), or relative to the user (1:30 PM)? And if you decide to store or display the date based on the poster's or visitor's time zone then how do you know their time zone and its offset? How do you account for daylight savings, and so on?
This article provides guidance on how to store and display dates and times for visitors across different time zones and includes a demo that gives a working example of some of these techniques. Read on to learn more!
Storing Dates And Times
When building a data-driven web application that needs to store dates, the first decision you'll need to make is how to store those dates and times in the database. Database systems usually offer at least one data type for storing date and time information. Microsoft SQL Server has long offered a data type named
datetime, which stores both a date part, which can range between 1753-01-01 through 9999-12-31, and a time part, which stores the hours, minutes, seconds, and microseconds with accuracy to 0.00333 seconds. There's also the
smalldatetimedata type, which stores a smaller range of date values (1900-01-01 through 2079-06-06) and only stores time accurate to the minute. SQL Server 2008 introduced a number of new date/time types, including a data type to store just times (
time), a data type to store just dates (
date), and a data type to store much wider ranging dates and more precise times (
datetime2). This article focuses on using the
datetimedata type, although the concepts discussed work equally well with the
datetime2data types. For more information on these additional date/time data types added to SQL Server 2008, refer to New Date Data Types in Microsoft SQL Server 2008.
After deciding on a data type to store the date and time information, you'll next need to decide whether to store dates and times literally or relative to a time zone. If you let a user enter a date and time or if the dates and times are tied to a specific geographical location, chances are you'll want to store the dates and times literally. Imagine that you are building a website for conference planners. The idea is that a conference planner would use your website to enter information about their conference - the location, the dates, the various sessions, the speakers, and so forth - and that this site would be used by customers to review the conference offerings, to book a reservation, and so on. In the screens where a conference planner is entering the conference information, she'll need to enter the dates and times the conference starts and ends. She'll also need to supply the dates and times for each session. You'll likely want to store the date and time values entered by the user literally. What I mean by that is that when a user enters the starting date and time for a particular session (say, April 10th, 2010 1:30 PM), store that actual value in the database. Do not adjust the inputted date/time to the web server's time zone or some other time zone. Likewise, when displaying these date/time values you'll display their literal values and not shift them based on the visitor's time zone.
If the date and time is being auto-generated, such as the date and time a post is made in a messageboard site, you'll need to store the date and time value relative to a time
zone. If you use SQL Server's
getdate() function or .NET's
DateTime.Now property to save the date and time value you are storing the date and time
value relative to the web server's time zone. While this is certainly feasible, it has its downsides. For starters, it makes your application's data specific to the time zone
of the web server. Consider what would happen if you switch to a different web hosting company located in a different time zone. New posts would be reflected in the new time zone
but all existing posts would still be represented in the old time zone.
A better approach is to store the time in Coordinated Universal Time (or UTC), which is the
standard international time that all time zones are expressed as offsets of. (Also, UTC does not adjust for daylight savings.) The primary advantage of storing date/time
values in UTC is that it makes the data transportable, as the data is no longer tied to a particular time zone. To get the current date and time in UTC from SQL Server use
getutcdate() function; in .NET use
DateTime.UtcNow. For more information on the benefits of UTC and using UTC in
a web application, see Using Coordinated Universal Time (UTC) to Store Date/Time Values.
As a general rule of thumb, if you are letting users enter the date/time value then you want to store the date literally, as they likely are entering the date and time relative to the time zone to which it applies (think conferences, airline scheduling, etc.). But if the date/time value is being calculated automatically - most commonly, the current date and time - then it's probably best to store the date/time value in UTC rather than in the web server's time zone or the visitor's time zone.
Displaying Dates and Times
When displaying dates and times to visitors you must decide whether to adjust the date and time for the visitor's time zone. For date/times entered literally or for date/times tied to a specific geographical location (like a plane flight or conference session) then the date should be displayed at it was literally entered and should not be adjusted for the visitor's time zone. For dates and times relative to a time zone offset (either UTC or the web server's time zone) you have three choices when it comes to the display:
- Display the date/time literally. Simple, but only advisable in web applications where the date and time values are not very important. Also, be forewarned that users inspecting the dates and times may get confused. For instance, if in a messageboard application you store the dates and times in UTC and show them literally, a recent post will display an impossible time to a user in a time zone in the Western hemisphere. (A user in California is seven hours earlier than UTC during daylight savings, so for seven hours after a post is made users from California will see the post's date and time as a time in the future.)
- Display the date/time in relative terms. Rather than showing the user the precise date and time, express it in relative terms. Many messageboard websites use this tactic. Instead of showing, "Post made on April 7th, 2010 at 3:30 PM", users would see a message like, "Post left five minutes ago," or "Post made about three hours ago," or "Post left two weeks ago." This works well for those web applications where the time portion of the date is not so much of interest as is the relative age of the data.
- Shift and then display the stored date/time to the visitor's time zone. This approach shows the date/time value in according to the visitor's time zone. To accomplish
this you must either:
- Determine the user's time zone offset from whatever time zone you store the data in (so that you can adjust the time before displaying it), or
Entries, which stores one record for each guest book entry made by a visitor. This table stores the date and time the guest book entry was made via its
EntryDatecolumn, which is of type
datetimeand has a default value of
EntryDatevalue is not entered by the user. Instead, when signing the guest book the user enters only his name and a message; the
EntryDatevalue is set automatically to the current UTC date and time. The guest book entries are displayed using a ListView control. In the ItemTemplate the
EntryDatevalue is passed into a method named
DisplayDate, which formats the date and returns it according to the format specified from a drop-down list.
Displaying the Date and Time Literally
When displaying the date and time literally it is usually best to do so in a format that removes any cultural ambiguity. In the United States, dates are often displayed using the MM/DD/YY, whereas many other countries use the format DD/MM/YY. To remove any ambiguity I recommend including the month name somewhere in the display, using one of the following custom date/time format specifiers:
MMMM d, yyyy h:mm tt, which displays the month name, the date, and the year followed by the 12-hour time value and an AM/PM designator, like April 7, 2010 2:15 PM.
d MMM yyyy h:mm tt, which displays the date, the abbreviated month name, and the year followed by the 12-hour time value and an AM/PM designator, like 7 Apr 2010 2:15 PM.
You can use the
ToString("formatSpecifier") overload or the
String.Format method to output the date and
time in this manner, as the following code snippet shows:
Displaying the Date and Time in Relative Terms
For scenarios where the relative age of the data is more important than the absolute date and time, consider expressing date and time values in relative terms. Returning to the messageboard example, posts made in the last minute can have their date and time value expressed in English as, "less than a minute ago." Posts under an hour could have their time expressed as "X minutes ago," where X is the number of minutes since the post was made. As posts age, the precise relative age is less important. For posts older than a week but less than a month old you could say "Three weeks ago." Such a statement is certainly less precise, but that precision is less important for older posts. And for posts older than a month perhaps you just show the date they were made, omitting the time altogether.
To accomplish this, you need to write a bit of code that determines the difference between the stored date and time value and the current date and time value. (If you stored
the date and time value in UTC then you need to make sure that to compare the stored value against the current UTC date and time instead of the web server's current date and time.)
I've used code like this for so many projects that I've created an extension method on the
structure. Specifically, I've added two public methods,
ToRelativeDateStringUtc(), which return the relative age for a
DateTime value when compared against the current local time (or the current UTC time).
Extension methods were introduced in the versions of VB and C# that shipped with the .NET Framework 3.5. In a nutshell, extension methods allow you to create methods that are treated as if they were defined on a particular type. With the extension methods I've created I can write code like the following:
The first line of code creates a variable named
dt and assigns it to a new
DateTime object of April 7, 2010 9:50:30. Next, I can invoke my extension
ToRelativeDateStringUtc() - by calling it as if it were a method defined on the
DateTime structure itself. The
method returns a string with a relative age (such as, "8 minutes ago"). For more information on creating and using extension methods, refer to
Extending Base Type Functionality with Extension Methods.
The extension method definitions follow, and can be found in the
App_Code folder in the demo project:
All the interesting work happens in the
GetRelativeDateValue method, which starts by computing the delta between the
DateTime value whose
extension method was called and the passed in
DateTime value (which will be either the current local or UTC date and time. Next,
a variety of metrics are checked to determine the appropriate string to return. You are welcome to use this extension method in your own projects and to extend it as needed.
toLocaleString()function to convert that time to the user's local time zone.
a variable named
dt that is assigned a new
Date object. As you can see, when creating a
Date object you can specify the date and time
The next line of script takes that Date object and calls the
toLocaleString() function, which returns the date/time value as a string in the current time zone.
That means that if the user visiting this page is in a time zone that is two hours ahead of UTC then the
toLocaleString() function will report the time as
5:02 PM. For a user who is three hours behind UTC, the function would report the time as 12:02 PM. Finally, the
document.write function emits the value of the
formattedDateTime variable to the browser. When running the above on my computer (which is UTC -7) I see the following output:
Wednesday, April 07, 2010 8:02:00 AM. (To emit just the local date or time use the
In the demo the following code executes if the user opts to see the date and times of the guest book entries in local time:
Here, dateTimeVariable is the
EntryDate value of the current guest book entry being displayed. The value of
formattedDateTime is emitted to
the ListView's ItemTemplate. If you download the demo, you'll see a guest book entry left by Alexander. The markup emitted by the ListView for that particular guest book entry follows:
Alexander's post was made on 'April 6, 2010 at 05:11:04 PM UTC. Note how this date value, along with the appropriate script, is rendered to the browser. When the browser parses this content it will display the appropriate locale date and time. The following screen shot shows this guest book entry when viewed from my computer, which is seven hours behind UTC.