DateTime.Kind, and SetLastModified problem （C#）

I'm using Response.Cache.SetLastModified to set the time of last modified entry in the database to the affected webpage, so that web browser knows when to cache and when to refresh for new page.

The SetLastModified gives "ArgumentOutOfRangeException" because it thinks the given time is newer than current time, and that is not allowed. The problem is, the given time was saved into database 2 minutes ago, how can it be greater (newer) than current time? I checked the sql server which is sitting in another server, and figured out they are using the same time, same timezone. The database time entry was saved as "LastModDateTimeGMT=getutcdate()".


After carefully checking all the possibilities (that means 2 hours), I focus on the DateTime.Kind property. When the DateTime entry is retrieved from database

DateTime LastModified = (DateTime)(row["LastModDateTimeGMT"])

The value of LastModified has the value of UTC time (that means 7PM, when it is 12PM local time), and the LastModified.Kind is "Unspecified".

DateTime.Kind property tells us, when the .Kind value is "Unspecified", you can call .ToLocalTime() to get LocalTime, assuming the current value is in UTC, or you can call .ToUniversalTime() to get UTC time, assuming the current value is in Local. So, in my case, SetLastModified(LastModified) calls the LastModified.ToUniversalTime() and gets 2PM of tomorrow, when this time is compared with the current time (7PM UTC, 12PM local), it prompts error because the given time is newer than current time.

After setting the .Kind to the UTC because I know the datetime in database is in UTC:

DateTime LastModified = (DateTime)(row["LastModDateTimeGMT"])
LastModified = DateTime.SpecifyKind(LastModified , DateTimeKind.Utc);

everything is good.

So the error actually comes from Sql Server: When a datetime is saved, there is no way to know what timezone it is in:

Time zone offset aware and preservation No
Daylight saving aware No
(datetime (Transact-SQL))

So C# can only give "Unspecified" to the .Kind value when the value is retrieved.

The "Unspecified" is a very dangerous status. The system (or the Microsoft Programers) tries very hard to guess what timezone that is or what timezone you want to have and gives you a wild guess result. Each time you retrieve a datatime from database or from other sources, you should always specify a .Kind for it to prevent this kind of uncertainty.

Another suggestion is: Always use UTC time (getutcdate) in database and in programing, so that you don't have trouble if your server is physically moved into to another timezone, or your program is deployed by another team that you don't know where they are.

Note: By default, the "Cache-Control" is "Private". In this status, the Response.Cache.SetETag will fail. There is no ETag output to client. So Response.Cache.SetCacheability(HttpCacheability.Public or PublicOrPrivate); must be enforced before setting ETag.