MySQL Bugs: #69242: Only DateTime milliseconds are stored, not all possible fraction digits are used

Bug #69242	Only DateTime milliseconds are stored, not all possible fraction digits are used
Submitted:	15 May 2013 11:54	Modified:	4 Aug 2014 3:47
Reporter:	Jan Blessenohl	Email Updates:
Status:	Open	Impact on me:	None
Category:	Connector / NET	Severity:	S4 (Feature request)
Version:	6.6.5	OS:	Microsoft Windows
Assigned to:		CPU Architecture:	Any
Tags:	DateTime fractions no stored

Description:
The .NET connector only stores the milliseconds of a System.DateTime. Even if there are nanoseconds stored in the database, the .NET connector does not copy them over to the returned System.DateTime.

This is a critical issue because of the data loss that this behavior produces.

How to repeat:
Store and load a System.DateTime with a .NET application using the latest .NET connector. Check if the 6 fraction digits are stored and loaded.

Suggested fix:
Use the ticks instead of the milliseconds in the System.DateTime

Hi Jan,

Thanks for your interest and feedback on this product. Could you please mention which server version you are using nanoseconds with? According to the MySql documentation the bigger value stored inside any DateTime/Time type is the microseconds value (10^6) so nanoseconds should be managed in another column if needed. Currently we're only managing up to microseconds for compatibility with the server. Here's a sample on how you should read this value since the DateTime of the .Net Framework by itself cannot stored directly this whole value you should use the MySqlDateTime type.

var c = new MySqlConnection("server=localhost;userid=root;pwd=;database=test;");
var cmd = new MySqlCommand("CREATE TABLE Test (id INT NOT NULL, dt DATETIME(6))");
cmd.Connection = c;
cmd.ExecuteNonQuery();

cmd.CommandText = "INSERT INTO Test VALUES(?id, ?dt)";
cmd.Parameters.Add(new MySqlParameter("?id", 1));

MySqlParameter datetimepar = new MySqlParameter();
datetimepar.ParameterName = "?dt";
datetimepar.MySqlDbType = MySqlDbType.DateTime;
datetimepar.Value = "2013-01-01 12:34:59.999999";   //storing the microseconds value

cmd.ExecuteNonQuery();

cmd.CommandText = "SELECT dt FROM Test WHERE id = 1";
cmd.Parameters.Clear();
cmd.Connection = c;

MySqlDataReader rdr = cmd.ExecuteReader();

while (rdr.Read())
{
Console.Writeline(rdr.GetDateTime(0).ToString("hh:mm:ss.ffff"));   /// "12:34:59.9990"
Console.Writeline(rdr.GetMySqlDateTime(0).Millisecond);  //999999
}

rdr.Close();

In order to add the nanoseconds support we would need to add a new class that could handle this value and probably use the ticks as you suggests. But we need to review if this wouldn't lead to any compatibility problems since this could add more work for the driver.

Hope you can give us some feedback on this.

Thanks in advance.

Hi Gabriella,

I am developing Telerik OpenAccess, an Object Relational Mapper that supports various backends. I am trying to store the ISO part of the System.DateTime, which means 6 fractional digits plus time zone. My guess is that microseconds are already transported and just stripped off during DateTime<->MySqlDateTime conversions, should not make it slower. I can use your workaround (works fine, except that I do not like string operations here) but nevertheless want to use the abstract ADO methods to be backend independent.

Jan

Hi Jan,

I understand your point. We can consider this as a feature request for a future version.

You can keep up to date of our releases at the blogs (https://blogs.oracle.com/MySqlOnWindows/) or at the forums (http://forums.mysql.com/list.php?38)  where we keep answering questions and publish all the updates related to the Connector/Net product.

Thanks for you interest in our product.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I have come across this issue also, unfortunately, the .NET connector does not correctly save and read the full 6 decimal places of precision from the stored TIMESTAMP or DATETIME values.  The resulting System.DateTime object does not have sub-millisecond precision.

I have written a workaround to deal with this issue, which sets the MySqlParameter value to the string representation of the DateTime with 6 fractional second decimal places.  When reading the DateTime value, it uses GetMySqlDateTime(), which gets the correct fractional value, but puts it (including microseconds) all in MySqlDateTime.Millisecond.  The workaround is to save the tick value of the microsecond portion of the Millisecond property: (Millisecond % 1000) * 10, and then add it back to the resulting DateTime value.  There are 10 ticks per microsecond.

See code below:

/// <summary>
/// Converts a DateTime to a MySql timestamp string.
/// DateTime.MinValue returns '0000-00-00 00:00:00.000000'.
/// </summary>
/// <param name="timestamp">The timestamp to convert.</param>
/// <returns>The MySql timestamp string.</returns>
public String GetTimestamp(DateTime timestamp) {
	if (timestamp == DateTime.MinValue)
		return "0000-00-00 00:00:00.000000";
	return timestamp.ToString("yyyy-MM-dd HH:mm:ss.ffffff");
}

/// <summary>
/// Creates a parameter with the specified name, type, size, direction, and value using the provider specified in the compoment parameters.
/// </summary>
/// <param name="name">The parameter name.</param>
/// <param name="type">The parameter type.</param>
/// <param name="direction">The parameter direction.</param>
/// <param name="value">The parameter value.</param>
/// <returns>A parameter with the specified name, type, size, direction, and value.</returns>
public DbParameter CreateParameter(String name, DbType type, ParameterDirection direction, Object value) {
	MySqlParameter parameter = new MySqlParameter();
	parameter.ParameterName = name;
	parameter.DbType = type;
	if (size != 0) parameter.Size = size;
	parameter.Direction = direction;
	if (value != null) parameter.Value = value;
	// special handling for DateTime because the mysql connector doesn't handle more than 3 decimal digits correctly
	if (value is DateTime) parameter.Value = GetTimestamp((DateTime)value); 
	return parameter;
}

/// <summary>
/// Helper methods to deal with MySqlDateTimes in result sets.
/// </summary>
internal static class MySqlProviderExtensions
{
	/// <summary>
	/// Gets a MySqlDateTime column value as a System.DateTime. 
	/// If the value is null or zero, this will return DateTime.MinValue.
	/// </summary>
	/// <param name="reader">The data reader to read the column value from.</param>
	/// <param name="column">The ordinal index of the column.</param>
	/// <returns>The column value as a DateTime.  If the column is null or zero
	/// this will return DateTime.MinValue.</returns>
	public static DateTime GetDateTimeExt(this MySqlDataReader reader, Int32 column) {
		if (reader.IsDBNull(column)) return DateTime.MinValue;
		MySqlDateTime dt = reader.GetMySqlDateTime(column);
		// accessing the dt.Value property automatically truncates fractional milliseconds, so save the fractional part as ticks first and add it back later
		int subMsTicks = dt.Millisecond > 1000 ? (dt.Millisecond % 1000) * 10 : 0;
		if (!dt.IsValidDateTime) return DateTime.MinValue;
		return dt.Value.AddTicks(subMsTicks);
	}

	/// <summary>
	/// Gets a MySqlDateTime column value as a System.DateTime. 
	/// If the value is null or zero, this will return DateTime.MinValue.
	/// </summary>
	/// <param name="reader">The data reader to read the column value from.</param>
	/// <param name="column">The name of the column.</param>
	/// <returns>The column value as a DateTime.  If the column is null or zero
	/// this will return DateTime.MinValue.</returns>
	public static DateTime GetDateTimeExt(this MySqlDataReader reader, String column) {
		return reader.GetDateTimeExt(reader.GetOrdinal(column));
	}
}

Hope this helps someone else.  It would be nice if we could use the DateTime value directly instead of converting it to a String first, it just feels dirty to do it that way.