MySql Timestamp to C# DateTime conversion

DerekT P

I have a MySQL database with a table that has a TIMESTAMP field. All I need to do is get that value into a DateTime variable in C#. I have a DataRow containing the timestamp value in column TimeStampCol (or it could be a datareader, same difficulty arises). Have tried the following: (datarow in variable DR)

DateTime MyVar;
MyVar = (DateTime)DR["TimeStampCol"]); // Throws System.FormatException
MyVar = Convert.ToDateTime(DR["TimeStampCol"]); // Thows System.FormatException
MyVar = new DateTime(1970, 1, 1, 0, 0, 0, 0).AddSeconds(Double.Parse(DR["TimeStampCol"].ToString())).ToLocalTime() // Gives a date in the year 7702...

I understand that the Timestamp is being returned as a long integer number, so am not surprised by the first two errors. There's probably a .Net function to do this, but I can't find it... surely this should be a trivial operation? Thanks!

Lost User

MySQL :: MySQL 5.5 Reference Manual :: 11.3.7 Conversion Between Date and Time Types[^]

DerekT P

My question is how to convert the returned Timestamp into a DateTime value in C# ... I can handle the values fine within MySql.

Lost User

Look at the documentation for C# DateTime types. It explains which conversions it accepts (e.g. strings, seconds, [D,M,Y] etc.). If it cannot accept the value returned from MySQL then you will need to convert that value into one that it can handle.

DerekT P

"you will need to convert that value into one that it can handle." Oddly, that's exactly the question I asked. "My question is how to convert the returned Timestamp into a DateTime value in C#" You suggest looking at the documentation for C# DateTime types. Not sure if you've spent quite as many hours as I have poring over these, plus the MySql documentation, nor the scores of Google search results I've read through; it's possible I've overlooked the solution, and if so, would really appreciate it if you could just point me to the specific page where you found this information. You would expect this to be a straightforward conversion, but it appears not to be. It is in my power to change the database definition so the data is stored as a DateTime rather than TimeStamp, but that seems to be the "wrong" solution to what I would expect should be a straightforward datatype conversion issue.

David A Gray

The following statement in the article that you cited tells me enough to give a reasonable hint.

Quote:

Conversion of TIME or DATETIME values to numeric form (for example, by adding +0) results in a double-precision value with a microseconds part of .000000

The TIMESTAMP is a double-precision floating point number, which is interpreted as follows. - The fractional part, if nonzero, is the number of microseconds in the time. - The integral part is always present, is a Unix time. With these two bits of information, you can use the following function, which you can implement as a static method, to convert the Unix timestamp.

public static DateTime UnixTimeStampToDateTime( double unixTimeStamp )
{
// Unix timestamp is seconds past epoch
System.DateTime dtDateTime = new DateTime(1970,1,1,0,0,0,0,System.DateTimeKind.Utc);
dtDateTime = dtDateTime.AddSeconds( unixTimeStamp ).ToLocalTime();
return dtDateTime;
}

The above is taken from the accepted answer to How can I convert a Unix timestamp to DateTime and vice versa?. Though I haven't tested it, I suspect it is at least essentially correct. Since the input is double precision, you can amend it to handle the decimal part, which should be converted to ticks, where one tick is equal to 100 nanoseconds. The resulting tick count should then be added to the .Ticks property on the DateTime to get the final answer.

David A. Gray Delivering Solutions for the Ages, One Problem at a Time Interpreting the Fundamental Principle of Tabular Reporting

Lost User

I think this message was meant for the OP rather than me.

David A Gray

You are correct. Thank you for calling that to my attention!

David A. Gray Delivering Solutions for the Ages, One Problem at a Time Interpreting the Fundamental Principle of Tabular Reporting

David A Gray

The following statement in the article that you cited tells me enough to give a reasonable hint.

Quote:

Conversion of TIME or DATETIME values to numeric form (for example, by adding +0) results in a double-precision value with a microseconds part of .000000

The TIMESTAMP is a double-precision floating point number, which is interpreted as follows.

The fractional part, if nonzero, is the number of microseconds in the time.
The integral part is always present, is a Unix time.

With these two bits of information, you can use the following function, which you can implement as a static method, to convert the Unix timestamp.

public static DateTime UnixTimeStampToDateTime( double unixTimeStamp )
{
// Unix timestamp is seconds past epoch
System.DateTime dtDateTime = new DateTime(1970,1,1,0,0,0,0,System.DateTimeKind.Utc);
dtDateTime = dtDateTime.AddSeconds( unixTimeStamp ).ToLocalTime();
return dtDateTime;
}

The above is taken from the accepted answer to How can I convert a Unix timestamp to DateTime and vice versa?. Though I haven't tested it, I suspect it is at least essentially correct.

Since the input is double precision, you can amend it to handle the decimal part, which should be converted to ticks, where one tick is equal to 100 nanoseconds. The resulting tick count should then be added to the .Ticks property on the DateTime to get the final answer.

David A. Gray Delivering Solutions for the Ages, One Problem at a Time Interpreting the Fundamental Principle of Tabular Reporting