How to move local DateTime values through different time zones

Question

What is the best way to store/retrieve a DateTime value that is created by an application with a specific local date and time, stored in a database on a server that may be in a different time zone, then retrieved by other devices in a different time zone, where those devices need to interpret the DateTime value with the same local time as originally entered? For example, a user in the PST time zone creates an event that is supposed to occur on 1/1/10 at 8:00 AM; that event's date time is stored on a server; then a user in the EST time zone retrieves the DateTime and also interprets the event as occurring at 1/1/10 at 8:00 AM, NOT at 11:00 AM.

The current implementation creates a DateTime with the local time zone, but when the DateTime is uploaded to the server using Windows Services, the DateTime gets converted to the server's local time zone before it gets stored in the DB.

Answer 1

Convert them to UTC and pass them around. If you need the time zone of the originator too, you'll have to store that information too. You can use a DateTimeOffset structure for that purpose in .NET and datetimeoffset data type in SQL Server.

Answer 2

Store them all as universal time, and convert to the local time zone on view. You'll find ToUniversalTime and ToLocalTime helpful. Here's the example code from MSDN:

        System.Console.WriteLine("Enter a date and time.");

        string strDateTime = System.Console.ReadLine();

        System.DateTime localDateTime;
        try {
            localDateTime = System.DateTime.Parse(strDateTime);
        }
        catch (System.FormatException) {
            System.Console.WriteLine("Invalid format.");
            return;
        }

        System.DateTime univDateTime = localDateTime.ToUniversalTime();

        System.Console.WriteLine("{0} local time is {1} universal time.",
                                 localDateTime,
                                 univDateTime); 

        System.Console.WriteLine("Enter a date and time in universal time.");
        strDateTime = System.Console.ReadLine();

        try {
            univDateTime = System.DateTime.Parse(strDateTime);
        }
        catch (System.FormatException) {
            System.Console.WriteLine("Invalid format.");
            return;
        }

        localDateTime = univDateTime.ToLocalTime();

        System.Console.WriteLine("{0} universal time is {1} local time.",
                                 univDateTime,
                                 localDateTime); 

Answer 3

Assuming you do actually know both time zones involved, you can use TimeZoneInfo.ConvertTime. If you don't always know the two time zones involved, of course, you're stuffed...

Storing the instant in UTC as suggested elsewhere is a good idea in many scenarios - it doesn't work when you need to capture the idea of "3pm every day" or something similar though.