Problem with Different TimeZones

* In progress...

Scenario:

The users are from different timezones e.g. Philippines, India, Redmond WA USA, Florida. A user input a specific schedule to run a task. The task should execute on his/her timezone.

The servers are in the US. SQL Server 2008 and SQL Azure store the user's time. A windows service installed in the server will execute the task. Currently, in the windows service, the scheduled date is being treated with same timezone as the server.

What.to.do?
1. Is there a way in which I can solve this without changing the database schema?
2. In what ways can I check the user's timezone against the server's time? Can the use of UTC solve this?
3. Or do I really need to update the database, add User's TimeZone column to the table for schedules?

Storing times

• Always store time in UTC

• Convert to local time on display (local being defined by the user looking at the data)

• When storing a timezone, you need the name, timestamp and the offset. This is required because governments sometimes change the meanings of their timezones (eg: the US govt changed DST dates), and your application needs to handle things gracefully... eg: The exact timestamp when episodes of LOST showed both before and after DST rules changed.

Possible Solution (Still needs of POC):

1. User to select date and time. Since currently, there is no option for selecting a time zone, automatically get user's timezone, then save in the database as UTC using DateTime.UTCNow (and Date.getTimeZoneOffset()?)

The problem with automatically getting the user's time zone is this scenario, what if the user wants to schedule a task in Philippine time but she is currently in Japan? The user would mentally/manually convert from PH time to JP time and then enter the date and time? 

2. Retrieve the UTC from the database, then convert to user's local time. How? Use TimeZoneInfo.ConvertTimeFromUtc 

http://stackoverflow.com/questions/3439468/utc-to-users-local-time

SOLUTION:
1. DateTime dateNow = DateTime.Now;
   Console.WriteLine("The date and time are {0} UTC.", TimeZoneInfo.ConvertTimeToUtc(dateNow));


2. TimeZoneInfo cstZone = TimeZoneInfo.FindSystemTimeZoneById("Central Standard Time"); DateTime cstTime = TimeZoneInfo.ConvertTimeFromUtc(timeUtc, cstZone);

Oh men, where should I get the user's current timezone?

Gotcha!

DateTime fromUtcToLocal = TimeZoneInfo.ConvertTimeFromUtc(utcDateTime, TimeZoneInfo.Local);
Console.WriteLine("Local Date and Time {0}:", fromUtcToLocal);

References:
The Authority - http://msdn.microsoft.com/en-us/library/bb397769.aspx
Closest to my requirement - http://stackoverflow.com/questions/832986/how-to-work-with-timezone-in-asp-net
Synchronizing Time Zones - http://stackoverflow.com/questions/179940/c-sharp-convert-utc-gmt-time-to-local-time
Links Worthy of Reading:

http://www.dotnetcurry.com/ShowArticle.aspx?ID=593
http://stackoverflow.com/questions/7577389/how-to-elegantly-deal-with-timezones
The Death of DateTime?
http://stackoverflow.com/questions/2532729/daylight-saving-time-and-timezone-best-practices
http://stackoverflow.com/questions/441109/get-datetime-for-another-time-zone-regardless-of-local-time-zone


Bonus:
If I want to list all timezones:
http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/2ddc265b-434a-4c5d-be27-5d6a92e89070/
http://msdn.microsoft.com/en-us/library/bb397781.aspx