Converting datetime to/from decimal to preserve milliseconds in MySQL

Question

MySQL time, datetime and timestamp types do not preserve milliseconds. However, MySQL does support storing datetime information using decimal(17,3). I came up with the following c# code to convert back and forth between (.net) datetime and decimal.

    public static decimal ToDecimalForMySql(System.DateTime dateTime)
    {
        return dateTime.Year * 10000000000m + dateTime.Month * 100000000m + dateTime.Day * 1000000m + dateTime.Hour * 10000m + dateTime.Minute * 100m + dateTime.Second + dateTime.Millisecond * 0.001m;
    }

    public static System.DateTime FromDecimalForMySql(decimal dateTime)
    {            
        long truncated = (long) Math.Truncate(dateTime);
        int msecs = (int) (1000 * (dateTime - truncated));
        long month;
        int day, hour, minute, second;
        return new System.DateTime((int) Math.DivRem(truncated, 10000000000, out month), Math.DivRem((int) month, 100000000, out day),
            Math.DivRem(day, 1000000, out hour), Math.DivRem(hour, 10000, out minute), Math.DivRem(minute, 100, out second), second, msecs);
    }

Is there a more efficient way of doing this, perhaps using bit fields to convert back from decimal to datetime?

MySQL 5.6.4 supports microsecond resolution in TIME, TIMESTAMP, and DATETIME types. (See the end of this [bug](http://bugs.mysql.com/bug.php?id=8523)) — pilcrow, May 07 '12 at 16:56
It does, but I am still using the current generally available release, 5.5.x, which does not support microseconds. — Jimmy, May 07 '12 at 17:09

score 1 · Answer 1 · answered Aug 08 '12 at 08:25

I'm using the following:

public static string ToDecimalForMySql(System.DateTime dateTime)
{
    return dateTime.toString("yyyyMMddHHmmss.fff");
}

Note that the above function return type is string, not decimal. This can be used directly in your SQL query string.

To retrieve out of MySQL, I've found it most efficient to use:

SELECT CONCAT('',DecimalDateTimeField) FROM table [where clause etc]

Then in C#, assuming you're using MySQLDataReader (variable name "rs"):

DateTime dt = DateTime.Parse((string)rs[0]);

score 0 · Answer 2 · answered May 07 '12 at 16:59

0

Could you not take the date time and store each element, da,y hour, min, sec, milli sec in a separate cell within your table in mysql. You would then need a function that breaks up your date/time before you send it and a function that reformats it upon retrieval.

answered May 07 '12 at 16:59

aaron burns

267
4
15

I could, but that I don't see how that would be better. The approached described in the question is supported by MySQL (i.e., it allows queries like this to work: select timecol from ttable where timecol > now() ) – Jimmy May 07 '12 at 17:07

score 0 · Answer 3 · edited May 23 '17 at 10:08

May I suggest you represent your numeric datetime as an epoch timestamp representing the number of seconds since midnight January 1st, 1970 UTC, rather than by overloading the base 10 representation of a number as you are attempting.

Conversion of a DateTime object to an epoch timestamp involves subtracting new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc) from the object in question (as UTC), and converting the resultant TimeSpan object to its .TotalSeconds.

In reverse, an epoch timestamp (again, fractional seconds since '1970-01-01 00:00:00') is added to the DateTime object representing the epoch via AddSeconds.

See, for example, this question.

Benefits: This approach will work well with the MySQL function FROM_UNIXTIME(). It will also be seen as an olive branch by your UNIXy colleagues, who will immediately recognize the fractional field as a datetime measurement.

Converting datetime to/from decimal to preserve milliseconds in MySQL

3 Answers3