International datetime comparisons in real-time web apps

[Savvoulidis Iordanis]

I'm creating a sports betting site and have a real trouble dealing with dates
and times depending on each country's hour difference. I've read much about
UTC and internationalization (cultures, SQL server CONVERT function, how
ASP.NET deals with datetime formats etc) but I can't figure out how to start
the puzzle.

So, how does a sports betting site deal with dates? In order to place a bet
without worrying about each country's time difference, players from all
around the world, should see the starting date of an event in their browsers,
displayed in their local time:

E.g. for a basketball event in Italy (where let's say the DB server
resides), that starts on May 14, 2009 at 18:30 (Italian local time), an
Italian player would see the date as '14.5.2009 18:30' (as is), a player in
New York should see the date like '5/14/2009 13:30', and a player in Athens
would see it like '14/5/2009 '19:30',
because of the -5 and +1 hour difference respectively).

Players can place a bet on the event's outcome, up to one minute before it
starts. After it that, it dissapears from their pages (auto refresh). Don't
forget that the server resides in Italy, and it checks for events to
deactivate, in Italian local time. So, for the NY player, it will disappear
on 5/14/2009 13:29 (NY local time) and for the player in Athens it will
dissapear on 14/5/2009 19:30 (Athens local time).

QUESTIONS:
1. Microsoft suggests dealing with apps that run internationally, to store
dates in UTC format. But this means, the time part of the Italian local time,
will now change. In my scenario, should the event's date be stored in Italian
local time or UTC time?

2. Is there a format I can set on the gridview's date column, so it is
displayed in local time? Or must I display it using code? I read all the
datetime formatings in MS help but I can't make the correct pick.

3. Should the date comparisons be done with dates converted to UTC or in
localtime?

4. What if the DB server is moved to another country with another timezone,
but the data entry is done from Italy, using the Italian local time ? How
should I compare the starting date with the DB server's date to deactivate
the even?

Too much, I know...
What I really need is guidance, from anybody who has dealt with real time
international web apps.
TIA

 

[Guest]

1. Microsoft suggests dealing with apps that run internationally, to store
dates in UTC format. But this means, the time part of the Italian local time,
will now change. In my scenario, should the event's date be stored in Italian
local time or UTC time?

Just follow what they suggested and store date in UTC. Use
DateTime.UtcNow to get date in .NET or GetUtcDate() in SQL Server.

2. Is there a format I can set on the gridview's date column, so it is
displayed in local time? Or must I display it using code? I read all the
datetime formatings in MS help but I can't make the correct pick.

Do it in the code, to get date from UTC to a server (italian) time use
the DateTime class's ToLocalTime() method:

utc_time.AddHours(2)

where 2 is for GMT +2 (Central Europe Daylight / Summer Time)

3. Should the date comparisons be done with dates converted to UTC or in
localtime?

Use UTC

4. What if the DB server is moved to another country with another timezone,
but the data entry is done from Italy, using the Italian local time ? How
should I compare the starting date with the DB server's date to deactivate
the even?

Again, because of this, just use UTC.

Using UTC will simplify conversion. For example, for NY you should use
UTC -4. If you will use italian time you would need to convert from
italian to UTC and then add an offset for NY.