Re: TimeZone in Connection String Parameters
Posted by: Fernando Gonzalez.Sanchez
Date: March 15, 2013 06:47PM

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();
}
}
}

Options: ReplyQuote


Subject
Written By
Posted
Re: TimeZone in Connection String Parameters
March 15, 2013 06:47PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.