When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips
Search

Sections:
Book Reviews
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
Web Hosts
XML
Information:
Advertise
Feedback
Author an Article
Jobs

ASP ASP.NET ASP FAQs Message Board Feedback ASP Jobs
 
Print this Page!
Published: Wednesday, April 7, 2010

Advice for Storing and Displaying Dates and Times Across Different Time Zones

By Scott Mitchell


Introduction


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!

- continued -

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 smalldatetime data 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 datetime data type, although the concepts discussed work equally well with the smalldatetime and datetime2 data 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 the 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:
  1. 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.)
  2. 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.
  3. 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:
    1. 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
    2. Send down the time as UTC and then use JavaScript to display the local time based on the user's computer settings.
The remainder of this article explores how to display date and times using options 1, 2, and 3b. Before we examine these two options, let's take a minute to examine the demo that is available for download at the end of this article. This demo project is a very, very simple guest book application. In short, it has a SQL Server 2008 Express Edition database with a single table, 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 EntryDate column, which is of type datetime and has a default value of getutcdate(). This EntryDate value is not entered by the user. Instead, when signing the guest book the user enters only his name and a message; the EntryDate value is set automatically to the current UTC date and time. The guest book entries are displayed using a ListView control. In the ItemTemplate the EntryDate value 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.
For more details or to create your own custom format specifier, see Custom Date and Time Format Strings.

You can use the DateTime structure's ToString("formatSpecifier") overload or the String.Format method to output the date and time in this manner, as the following code snippet shows:

string formattedDateTime = String.Format("{0:d MMM yyyy h:mm tt}", dateTimeVariable);

- or -

string formattedDateTime = dateTimeVariable.ToString("d MMM yyyy h:mm tt");

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 DateTime structure. Specifically, I've added two public methods, ToRelativeDateString() and 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:

DateTime dt = new DateTime(2010, 4, 7, 9, 50, 30);

string formattedDateTime = dt.ToRelativeDateStringUtc();

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 method - ToRelativeDateStringUtc() - by calling it as if it were a method defined on the DateTime structure itself. The ToRelativeDateStringUtc() 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:

namespace skmExtensions
{
   public static class DateTimeExtensions
   {
      public static string ToRelativeDateString(this DateTime date)
      {
         return GetRelativeDateValue(date, DateTime.Now);
      }

      public static string ToRelativeDateStringUtc(this DateTime date)
      {
         return GetRelativeDateValue(date, DateTime.UtcNow);
      }

      private static string GetRelativeDateValue(DateTime date, DateTime comparedTo)
      {
         TimeSpan diff = comparedTo.Subtract(date);

         if (diff.Days >= 7)
            return string.Concat("on ", date.ToString("MMMM dd, yyyy"));
         else if (diff.Days > 1)
            return string.Concat(diff.Days, " days ago");
         else if (diff.Days == 1)
            return "yesterday";
         else if (diff.Hours >= 2)
            return string.Concat(diff.Hours, " hours ago");
         else if (diff.Minutes >= 60)
            return "more than an hour ago";
         else if (diff.Minutes >= 5)
            return string.Concat(diff.Minutes, " minutes ago");
         if (diff.Minutes >= 1)
            return "a few minutes ago";
         else
            return "less than a minute ago";
      }
   }
}

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 comparedTo 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.

Displaying the Date and Time in the Visitor's Local Time Zone Using JavaScript


JavaScript offers a variety of functions for creating, manipulating, and displaying dates and times. (Patrick Hunlock has an excellent reference on his blog that highlights these functions. See Javascript Dates-The Complete Reference.) In JavaScript, you can create a date by passing in a string with a time zone specified. You can then use the toLocaleString() function to convert that time to the user's local time zone.

To illustrate this concept, consider the following snippet of JavaScript code:

<script type="text/javascript">
   var dt = new Date('April 7, 2010 3:02 PM UTC');
   var formattedDateTime = dt.toLocaleString();
   document.write(formattedDateTime);
</script>

If you put the above content in a web page and then view that page through a browser, the JavaScript will execute immediately when it's reached. The script starts by creating 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 value as a string, which JavaScript will parse. Also note that this string includes the time zone, in this case UTC.

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 toLocaleDateString() or toLocaleTimeString() functions.)

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:

string formattedDateTime = String.Format("<script type=\"text/javascript\">document.write(new Date('{0:MMMM d, yyyy hh:mm:ss tt} UTC').toLocaleString());</script>", dateTimeVariable);

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:

<div class="entry">
   This website is quaint. A guest book! How 1998ish!
</div>

<div class="entryMeta">
   ... Entry left by <span class="entryLeftBy">Alexander</span>
   <script type="text/javascript">document.write(new Date('April 6, 2010 05:11:04 PM UTC').toLocaleString());</script>
</div>

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.

Conclusion


Many data-driven web applications require storing and displaying date and time values. One concern in such applications is how best to store these date/time values, considering that users visiting the site may be coming from a variety of time zones. For certain applications it is best to store and display the literal time. Any sort of application where the data and time is specific to a geographic location - such as airline bookings or conference scheduling - usually qualifies. For those cases where the date and time values are assigned automatically (usually to the current date and time) it is often best to store the date and time in UTC. For displaying the date and time values you can either display them literally, use relative ages, or adjust the date and time to the visitor's time zone. When adjusting the date and time to the visitor's time zone you either need to have them specify their time zone offset relative to UTC (or to whatever time zone your date/time values are stored in) or use JavaScript to convert the stored time to the visitor's local time automatically.

Happy Programming!

  • By Scott Mitchell


    Attachments:


  • Download the code used in this article

    Further Readings:


  • Using Coordinated Universal Time (UTC) to Store Date/Time Values
  • New Date Data Types in Microsoft SQL Server 2008
  • Extending Base Type Functionality with Extension Methods
  • Javascript Dates-The Complete Reference


  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article