All geek questions in one place

How is the time zone handled in the DateTime ADO.NET + SQL Server column life cycle?

Using SQL Server 2008. This is a really minor question, and I could really use some detailed information, but the Google information seems to have danced a bit on this topic, and it would be nice if there was detailed information on how it works. ..

Say I have a datetime column, and in ADO.NET I set it to DateTime.UtcNow.

1) Does SQL Server match DateTime.UtcNow or compensate it again depending on the time zone in which the server is installed, and then returns it with feedback when requested? I think I know that the answer is “of course, he keeps it, not compensating him again”, but I want to be sure.

So, I request it and drop it from the object on the DateTime after receiving it, say, in the IDataReader column. As far as I know, System.DateTime has metadata that internally tracks whether it is a UTC DateTime, or it is an offset DateTime, which may or may not call .ToLocalTime () and .ToUniversalTime () to have different behavior depending on this state , Thus,

2) Does this casted System.DateTime object already know that it is a UTC DateTime instance, or did it assume that it was biased?

Now let's say that I do not use UtcNow, I use DateTime.Now when doing ADO.NET INSERT or UPDATE.

3) Does ADO.NET pass the SQL Server offset and does SQL Server save DateTime.Now with offset metadata?

So, I request it and drop, say, from the IDataReader column in DateTime.

4) Does this Cast.DateTime object already know that this is the offset time, or does it assume that it is UTC?

+4
source share
3 answers

I did some unit tests to answer my own question in all four parts.

1: Does SQL Server match DateTime.UtcNow or compensate it again depending on the time zone in which the server is installed, and then returns it with feedback when prompted?

This is done):

cmd.CommandText = "INSERT INTO testtbl (val) VALUES (@newval)"; cmd.Parameters.Add(new SqlParameter("@newval", DateTime.UtcNow)); cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT CAST(val as varchar) value FROM testtbl"; Console.WriteLine(cmd.ExecuteScalar());

The result of this at 1:30 pm local time (-7h, or 8:30 UTC) was:

 Jun 3 2010 8:30PM

Then I tried this:

 cmd.CommandText = "INSERT INTO testtbl (val) VALUES (@newval)"; cmd.Parameters.Add(new SqlParameter("@newval", DateTime.UtcNow)); cmd.ExecuteNonQuery(); Console.WriteLine("change time zone to utc"); Console.ReadLine(); cmd.CommandText = "SELECT CAST(val as varchar) value FROM testtbl"; Console.WriteLine(cmd.ExecuteScalar()); Console.WriteLine("change time zone back to local");

Done at 21:25 UTC, it returned

 Jun 3 2010 9:25PM

Compare this with DateTime.Now:

 cmd.CommandText = "INSERT INTO testtbl (val) VALUES (@newval)"; cmd.Parameters.Add(new SqlParameter("@newval", DateTime.Now)); cmd.ExecuteNonQuery(); Console.WriteLine("change time zone to utc"); Console.ReadLine(); cmd.CommandText = "SELECT CAST(val as varchar) value FROM testtbl"; Console.WriteLine(cmd.ExecuteScalar()); Console.WriteLine("change time zone back to local");

Done at 15:55 (local; -7h), returned:

 Jun 3 2010 3:55PM

2: So, I request it and drop it from the object on the DateTime after receiving it, say, from the IDataReader column. Does this casted System.DateTime object already know that it is a UTC DateTime instance, or did it assume that it was biased?

None.

 cmd.CommandText = "INSERT INTO testtbl (val) VALUES (@newval)"; cmd.Parameters.Add(new SqlParameter("@newval", DateTime.UtcNow)); cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT val value FROM testtbl"; var retval = (DateTime)cmd.ExecuteScalar(); Console.WriteLine("Kind: " + retval.Kind); Console.WriteLine("UTC: " + retval.ToUniversalTime().ToString()); Console.WriteLine("Local: " + retval.ToLocalTime().ToString());

The result of this (done at 13:58 local time):

 Kind: Unspecified UTC: 6/4/2010 3:58:42 AM Local: 6/3/2010 1:58:42 PM

That is, .ToUniversalTime() finished the alignment from local time to UTC time not once, but twice (??), and .ToLocalTime() ultimately did not compensate at all.

3: Does ADO.NET pass the SQL Server offset and does SQL Server save DateTime.Now with offset metadata?

Without performing any unit tests, the answer is already known as an “only with DateTimeOffset” SQL type. SQL datetime does not perform offsets.

4: Does this cast System.DateTime object already know that this is the offset time, or does it assume it is UTC?

None. The SQL type DateTimeOffset is returned as a .NET DateTimeOffset structure.

The following runs at 15:31 local time, when the offval column is a type of SQL datetimeoffset,

 cmd.CommandText = "INSERT INTO testtbl (offval) VALUES (@newval)"; cmd.Parameters.Add(new SqlParameter("@newval", DateTime.Now)); cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT offval value FROM testtbl"; object retvalobj = cmd.ExecuteScalar(); Console.WriteLine("Type: " + retvalobj.GetType().Name); var retval = (DateTimeOffset)retvalobj; Console.WriteLine("ToString(): " + retval.ToString()); Console.WriteLine("UTC: " + retval.ToUniversalTime().ToString()); Console.WriteLine("Local: " + retval.ToLocalTime().ToString());

The result is:

 Type: DateTimeOffset ToString(): 6/3/2010 3:31:47 PM +00:00 UTC: 6/3/2010 3:31:47 PM +00:00 Local: 6/3/2010 8:31:47 AM -07:00

Amazing mismatch.

Returning and performing the test for question No. 1 above, using DateTime.Now instead of DateTime.UtcNow, I confirmed that ADO.NET does NOT convert to universal time before storing in the database.

That is, this is done at 15:27 local time (-7h):

  cmd.CommandText = "INSERT INTO testtbl (val) VALUES (@newval)"; cmd.Parameters.Add(new SqlParameter("@newval", DateTime.Now)); cmd.ExecuteNonQuery(); Console.WriteLine("change time zone to utc"); Console.ReadLine(); cmd.CommandText = "SELECT CAST(val as varchar) value FROM testtbl"; Console.WriteLine(cmd.ExecuteScalar()); Console.WriteLine("change time zone back to local");

.. is back ..

 Jun 3 2010 3:27PM

Doing this at 3:17 pm local time:

 cmd.CommandText = "INSERT INTO testtbl (val) VALUES (@newval)"; cmd.Parameters.Add(new SqlParameter("@newval", DateTime.UtcNow)); cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT val FROM testtbl"; var result = (DateTime)cmd.ExecuteScalar(); Console.WriteLine("Kind: " + result.Kind); Console.WriteLine("ToString(): " + result.ToString()); Console.WriteLine("Add 1 minute, is greater than UtcNow? " + (result.AddMinutes(1) > DateTime.UtcNow).ToString()); Console.WriteLine("Add 1 minute, is greater than Now? " + (result.AddMinutes(1) > DateTime.Now).ToString()); Console.WriteLine("Add 1 minute, is less than UtcNow? " + (result.AddMinutes(1) < DateTime.UtcNow).ToString()); Console.WriteLine("Add 1 minute, is less than Now? " + (result.AddMinutes(1) < DateTime.Now).ToString()); Console.WriteLine("Subtract 1 minute, is greater than UtcNow? " + (result.AddMinutes(-1) > DateTime.UtcNow).ToString()); Console.WriteLine("Subtract 1 minute, is greater than Now? " + (result.AddMinutes(-1) > DateTime.Now).ToString()); Console.WriteLine("Subtract 1 minute, is less than UtcNow? " + (result.AddMinutes(-1) < DateTime.UtcNow).ToString()); Console.WriteLine("Subtract 1 minute, is less than Now? " + (result.AddMinutes(-1) < DateTime.Now).ToString());

Result:

 Kind: Unspecified ToString(): 6/3/2010 10:17:05 PM Add 1 minute, is greater than UtcNow? True Add 1 minute, is greater than Now? True Add 1 minute, is less than UtcNow? False Add 1 minute, is less than Now? False Subtract 1 minute, is greater than UtcNow? False Subtract 1 minute, is greater than Now? True Subtract 1 minute, is less than UtcNow? True Subtract 1 minute, is less than Now? False

Compare this with DateTime.Now:

 cmd.CommandText = "INSERT INTO testtbl (val) VALUES (@newval)"; cmd.Parameters.Add(new SqlParameter("@newval", DateTime.Now)); cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT val FROM testtbl"; var result = (DateTime)cmd.ExecuteScalar(); Console.WriteLine("Kind: " + result.Kind); Console.WriteLine("ToString(): " + result.ToString()); Console.WriteLine("Add 1 minute, is greater than UtcNow? " + (result.AddMinutes(1) > DateTime.UtcNow).ToString()); Console.WriteLine("Add 1 minute, is greater than Now? " + (result.AddMinutes(1) > DateTime.Now).ToString()); Console.WriteLine("Add 1 minute, is less than UtcNow? " + (result.AddMinutes(1) < DateTime.UtcNow).ToString()); Console.WriteLine("Add 1 minute, is less than Now? " + (result.AddMinutes(1) < DateTime.Now).ToString()); Console.WriteLine("Subtract 1 minute, is greater than UtcNow? " + (result.AddMinutes(-1) > DateTime.UtcNow).ToString()); Console.WriteLine("Subtract 1 minute, is greater than Now? " + (result.AddMinutes(-1) > DateTime.Now).ToString()); Console.WriteLine("Subtract 1 minute, is less than UtcNow? " + (result.AddMinutes(-1) < DateTime.UtcNow).ToString()); Console.WriteLine("Subtract 1 minute, is less than Now? " + (result.AddMinutes(-1) < DateTime.Now).ToString());

Done at 15:58 (local, -7h):

 Kind: Unspecified ToString(): 6/3/2010 3:59:26 PM Add 1 minute, is greater than UtcNow? False Add 1 minute, is greater than Now? True Add 1 minute, is less than UtcNow? True Add 1 minute, is less than Now? False Subtract 1 minute, is greater than UtcNow? False Subtract 1 minute, is greater than Now? False Subtract 1 minute, is less than UtcNow? True Subtract 1 minute, is less than Now? True
+8
source

SQL Server does not save time zone information with date / time information placed in the database, and does not make any adjustments to these values ​​(other than using the GetUTCDate () function).

Thus, the interpretation of this data and their time zones completely depends on your application. If you need to track time zone information, you must do this outside of the DateTime columns in SQL Server.

One of our best practices is to always store ONLY UTC dates in SQL Server and, if necessary, make any local adjustments.

+2
source

DateTime does not store any offset information - it only stores the datetime value. DateTimeOffset in .Net will save the timezone offset. If you pass DateTimeOffset to SQL Server, the offset will be stored in the database and returned to you when you query the table.

In the DateTimeOffset type, you can check the Offset property, as well as use the ToLocalTime, ToUniversalTime, and ToOffset methods.

+1
source

Source: https://habr.com/ru/post/1309038/

More articles:


All Articles