Hi,
The feature is available in MySql server, is not available at Connector/NET in the connection string, but you can issue a sql statement to set it, the server variable is "time_zone".
The following sample unit test from Connector/NET sources (MySql.Data.MySqlClient.Tests.DateTimeTests plays that value, first ensures is UTC, then inserts a timestamp in a table row, reads it, ensure its DateTimeKind is reported as UTC, then changes the time zone to a local one (+5:00), then reads the value again and checks its DateTimeKind is reported as Local.
(This sample sets it globally, of course you can also set it per connection, see
http://dev.mysql.com/doc/refman/5.5/en//time-zone-support.html for details).
public void TimestampCorrectTimezone()
{
DateTime dt = DateTime.Now;
MySqlCommand cmd = new MySqlCommand("select timediff( curtime(), utc_time() )", rootConn);
string s = cmd.ExecuteScalar().ToString();
int curroffset = int.Parse(s.Substring(0, s.IndexOf(':') ));
string prevTimeZone = "";
// Ensure timezone is UTC
if (curroffset != 0)
{
cmd.CommandText = "SELECT @@global.time_zone";
prevTimeZone = cmd.ExecuteScalar().ToString();
cmd.CommandText = "set @@global.time_zone = '+0:00'";
cmd.ExecuteNonQuery();
// Refresh time_zone value
rootConn.Close();
rootConn.Open();
}
try
{
cmd.CommandText = string.Format("INSERT INTO `{0}`.Test VALUES(1, curdate(), NULL, NULL, current_timestamp())", conn.Database); ;
cmd.ExecuteNonQuery();
cmd.CommandText = string.Format( "SELECT dt,ts FROM `{0}`.Test", conn.Database );
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
DateTime ts = reader.GetDateTime(1);
Assert.AreEqual(ts.Kind, DateTimeKind.Utc);
}
// Now set it to non-UTC
cmd.CommandText = "set @@global.time_zone = '+5:00'";
cmd.ExecuteNonQuery();
// Refresh time_zone value
rootConn.Close();
rootConn.Open();
cmd.CommandText = string.Format("SELECT dt,ts FROM `{0}`.Test", conn.Database);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
DateTime ts = reader.GetDateTime(1);
Assert.AreEqual(ts.Kind, DateTimeKind.Local);
}
}
finally
{
if (!string.IsNullOrEmpty(prevTimeZone))
{
// restore modified time zone if any
cmd.CommandText = string.Format( "set @@global.time_zone = '{0}'", prevTimeZone );
cmd.ExecuteNonQuery();
rootConn.Close();
rootConn.Open();
}
}
}