How to save and retain Date milliseconds (MySQL)

Question

I need to generate the current Date in vb.net that's have also the millisecond. Actually I use:

Date.Now

But this returns a simple date as 28/12/2015 16:53, I want also the millisecond something like:

28/12/2015 16:53:48640864

I tried with:

Public Shared Function MillisecondsDate()

    Return Date.Now.ToString("HH:mm:ss.fffffff")

End Function

but this returns a bad result:

16:53:56.9884043

I need a format compatible with MySql for an accurate lastUpdated field. Any ideas?

Answer 1

From comments: my db field is a timestamp and I fix temp using a varchar instead a datetime or timestamp

A TimeStamp column isnt quite the same as a DateTime column. According to the Documentation:

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)

It is something you may never see, even MySQL WorkBench UI converts it back.

Fractional Seconds

The issue of Fractional Seconds is something else. When defining a column as DateTime or TimeStamp, fractional seconds are dropped by default, for compatibility with older versions.

Rather than defining any sort of Date column as string/text/varchar - and of course having to parse it back - you can define either type to retain fractional seconds. In MySQL 5.6.4+, you can use: DATETIME(n) or TIMESTAMP(n) (yes, thats why the parens show in the UI dropdown list). Where n is the number of fractional digits to store (0-6). For .NET, 3 would equate to milliseconds. After that, the WorkBench UI shows the milliseconds as well:

• StartDate is DateTime(0) (no fractionals)
• LastUpdated is TimeStamp(3)
• Foo is TimeStamp(6)

Trigger

A LastUpdated column would seem best managed by the database so you don't have to do so via code -- or forget to do so!. This is pretty simple using a default value and a Before_Update trigger:

• Define the column as TIMESTAMP(3)
• Specify CURRENT_TIMESTAMP(3) as the default value

ADD COLUMN LastUpdated TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '';

This would automatically set the LastUpdated value on new items added to the current time (UTC etc) with milliseconds (TIMESTAMP(6) might be better, but the question only frets about ms). Be sure to specify the same number of digits in the default.

Then, rather than passing DateTime.Now manually (or converting to string) when updating each record, add a trigger to update the column for you. In MySql Workbench:

CREATE DEFINER=`root`@`localhost` TRIGGER `test`.`demo_BEFORE_INSERT` BEFORE INSERT ON `demo` FOR EACH ROW  
BEGIN  
    SET new.LastUpdated := now(3);  
END 

Most of that is boilerplate that the UI Tool creates. You just need to type:

    SET new.LastUpdated := now(3);  

For this type of column, you'd can add the same thing on the BEFORE_INSERT trigger in place of a default value. In MySQL WorkBench, click the "Triggers" tab when viewing the table definitions - just add the SET statement.

Test Code

This shows the fractional milliseconds making the round trip from code to db and back, and shows that the trigger works (uses the table shown above):

Dim Usql = "UPDATE Demo SET Foo = @p1 WHERE Id=5"
Dim Ssql = "SELECT Name, StartDate, Foo, LastUpdated FROM Demo WHERE Id=5"

Dim dtVar As DateTime = DateTime.Now
Console.WriteLine("DT ms in code var: {0}", dtVar.Millisecond)

Using dbcon = GetMySQLConnection()
    dbcon.Open()
    Using cmd As New MySqlCommand(Usql, dbcon)
        cmd.Parameters.AddWithValue("@p1", dtVar)
        cmd.ExecuteNonQuery()
    End Using

    Using cmd As New MySqlCommand(Ssql, dbcon)
        Using rdr As MySqlDataReader = cmd.ExecuteReader
            If rdr.HasRows Then
                rdr.Read()

                Dim tempDT = rdr.GetMySqlDateTime(1)    ' DateTime(0)
                Console.WriteLine("DT.MS from DB {0}", tempDT.Millisecond)

                Dim mydt = rdr.GetMySqlDateTime(2)    ' TimeStamp(6)
                Console.WriteLine("Micro from DB {0} ", mydt.Microsecond)

                ' either get method retains the ms      TimeStamp(3)
                Dim lstupD = Convert.ToDateTime(rdr("lastUpdated"))
                Console.WriteLine("MS from trigger {0}", lstupD.Millisecond)
            End If
        End Using
    End Using
End Using

Result:

DT ms in code var: 615
DT.MS from DB 0
Micro from DB 615413
MS from trigger 615

The columns defined to store fractional seconds do so down to microseconds (ticks). Note that the Update SQL doesn't reference the LastUpdated column, yet it is updated courtesy of the trigger. If you are step-debugging and using a variable as above the trigger time can vary from the others because time is passing as you step through.

Answer 2

Try this

Public Shared Function MillisecondsDate()

    Return Date.Now.ToString("dd/MM/yyyy HH:mm:ss.fff")
End Function

Answer 3

Try this function:

Public Shared Function MillisecondsDate()
    Return DateTime.UtcNow.ToString("dd/MM/yyyy HH:mm:ss.fff")
End Function