I host a SQL Server 2005 database that has users in different time zones.
I just discovered that data that is saved in Central time zone at 12:30am on Jan 9th is displayed as being entered on Jan8th on a machine looking at the data from the Eastern Time Zone.
A look at the XML for a serialized dataset shows the date is being saved with UTC offsets.
I need to understand what exactly is happening with the date times so I can know what to expect. For example, when I send a select to the database across time zones, is it changing the date
If both users run a report of data entered on the 9th, it appears they are going to get different reports, which sounds like a nightmare to me.
Can anyone summarize
1) what is being done with the dates and
2 What is the best way to deal with saving and displaying dates when being accesssed from multiple time zones
Thanks
RickN
Best Practice for Handling time
Mark Coffman
If you don't want the database (or the XML Serializer for that matter) to do something to your dates you should store that data as something other than datetime, like text. You can use DateTime.ToString("o") for a round-trip textual representation.
Dan's (and all others) date/time guidelines are geared towards ensuring dates stored, transferred, and formatted are not relative and are keyed to the local time where the formatting occurs and local time isn't an issue in date/time calculations. If you don't want your date/time to be formatted in local time, these practices aren't for you. Typically a string would be used to store and transfer the date/time information and it would be up to the code displaying (formatting) the data to process it as it sees fit.
Jon Abbott
As I look into this more, the fact that dates are being changed by the serialization process is a nightmare, for our hosted .net remoting application, that works across time zones. We are using SQL Server 2005 and .Net 2.0.
Dates being added to the database via serialized datasets are being adjusted to reflect the date and time of the data base server time zone. A few of the problems are:
1) a date can be adjusted to now reflect a different day and potentially a different month and year! Entering a birthdate of 1/25 from a computer in Boston can appear as 1/24 when viewed on a computer in LA. We have a lot of dates that should never vary, Birthdates are just one of them.
2) While serialized datasets adjust the date, method and stored procedure date parameters are not adjusted. A record saved from Hawaii at 10pm on 1/15 will be recorded as being saved as 1/16 on the Boston Server. Now, when the person ask for a report of data entered on 1/15, the data will not appear on the list. The monthly report in Hawaii will show the date correctly as 1/15 while the Boston report will show the same data as 1/16. This means we need to do an exhaustive test of every technique we use to determine how dates are being changed. Further, the local UTC offset must be saved with every date if there is going to be any hope of reconciling the data.
3) Two users in different time zones viewing the same report on the same data with the same report filters are very likely to see different dates for the same data on their reports. This is unacceptable in any business class app.
4) If our database is restored to a server in a different time zone, it would appear that our date based data would be rendered useless and wrong without a difficult to impossible modification of the data in the database, and it can only be fixed if the originating timezone offset data is saved for each date, otherwise all dates are rendered hopelessly wrong and useless.
As for me, I'd prefer to have full control over how dates are saved, if I want a date converted to the local machine time zone, let me convert it. The minor benefits being espoused by MS for this automated conversion can be handled in other ways and is far outweighed, in my opinion, by the negatives outlined above.
If anyone has resolved these date issues in a straightforward way, please let me know. Otherwise, for sql storage purposes, we are contemplating moving our dates out of the DateTime type to one or more int based columns.
Rick
AnthoDesigns
Peter:
Thanks, the reference was exactly what I needed ...six months ago. However, I may not be understanding your summary of the principals, especially where you say to store UTC values, because that is where I got into trouble.
I was attempting to store UTC dates, along with the offset, on the assumption that what I was sending would be saved as sent. As discussed in the article, the deserialization process adjust the date value to the db Server time zone value. So, when the client is in a different time zone then the server, the time is adjusted.
For example, we had two data rows saved at the same instant, one in the database server time zone and one in a PST time zone 3 hours earlier. We converted the data to UTC, (which caused the dates to be indentical) and saved the rows, along with the offset, assuming that the UTC time for both would be the same in the database. After looking at the data in the database, the PST time was different by 3 hours. It had been changed by the deserialization process. In our case, the 3 hours also changed the day.
So, as the article suggest, we will now store the dates in local time, along with the offset. With the offset, we can calculate the UTC or any other reference point.
Thanks,
Rick
ThePatrickP
The basic principles are: convert to UTC in the context where the date/time is entered (if you're n-tier, this means converting to UTC on the client computer) because that's there the timezone information is, store and perform calculations on DateTime objects storing UTC values, and convert DateTime from UTC to local time in the context where the date/time should be formatted (again, if n-tier, this means on the client computer).