UTC Time, but how?

[Liming]

Hello All,

In our project management software, we just noticed an issue. If team
members are across the world, then there is an issue with due dates
being consistent. (even though we have not yet had such customers, but
we want to address this)

For example.

A team is consists of 3 team members. John (U.S Eastern time), Jack
(U.S West), Kate (China, Beijing time)

Problem

- Web server and database servers are located in Virgina U.S (East
coast)

- John on U.S east cost assigned a task to Kate and Jack, with a Due
12/13/2007 7:00pm (U.S eastern time)

- To Kate in China, she should be seeing Due Date: 12/14/2007
8:00am (Beijing Time) (13 hours difference ahead )

- To Jack on U.S west coast, Due Date: 12/13/2007 4:00pm (3 hours
behind)


Our issue right now is that everyone see the due date as 12/13/2007
7:00pm, which Kate will finish early (that's good), but Jack will
finish 3 hours late... to John at least.


What is it that I have to do in

1) Our c# code
2) Database server
3) ASP.NET page which auto detects user's UICulture and CultureInfo

Basically, what is the process of going through saving to database,
retriving from database and then display the date time to a user
specifically to their time zone?

Any suggestions is greatly appreciate it. I'm still trying to read up
on UTC, a bit confused

Thanks

 

[Dmitry Duginov]

Basically, what is the process of going through saving to database,
retriving from database and then display the date time to a user
specifically to their time zone?

Any suggestions is greatly appreciate it. I'm still trying to read up
on UTC, a bit confused

I don't believe that TimeZone (in the context you are talking about) has
been implemented in .NET framework. So, you would have to assosiate your
specific time zone information with each user's profile, keep the time in
the database in UTC. Both saving and retrieving time will require
calculations bazed on user's time zone offset. (don't forget to factor DST
for both user and web server).

D.

 

[bruce barker]

the typical is to store the utc date in the database. then when you display
dates, you translate the date to the local to the local time zone. if you
are just using dates, and not times, then you need to decide the timespan a
univeral date represents.

if you say 12/21/2007, its it begin/end of day a for each timezone (thus
making the day really 48 hours long) or is there a fixed time (8:00pm pst).
once you decide this you can convert the date.

you next problem is getting the local timezone of the client. the browser
doesn't send it. you have two option, use javascript to convert the dates to
utc, or fill a hidden field with the current time and clac the timezone on
the server.

-- bruce (sqlwork.com)

 

[Liming]

Thanks Dmitry and Bruce. Okay..from the sound of it, it's becoming a
lot more complicated than I thought... the picture is a bit more
clearer now, is the following process the right process from a web
application's perspective according to your description?

1) Ask the user to specify their timezone information when registered,
save it using ASP.NET Profile service (How do I find out all the
available time zone in the world? U.S Eastern, U.S Pacific, Beijing
China, Tokyo Japan, etc etc?)

2) Change all database scripts where I did getDate() to getutcdate()?

3) In our ASP.NET application, everywhere where we asked to input
"Due Date" (with date and (hours:minutes) ), in my back end c# data
access class, I have to convert that to UTC before saving to database?
or do I have to first calcuate their timezone offset && dst first
before convert to UTC? (any examples to point to? These information
are so rare on the web to my surprise after googling)

4) After saving to the database, when I retrieve from database and
display to the user, I convert UTC back to Local Time (server's time)
and then adjust it to user's time in their timezone saved in their
profile and if there is DST, adjust that as well?

Sorry if I'm asking these beginner questions. First time I need to
deal with an application across timezones.

Thanks again!