Write date to mySQL with millisecond precision

Hey everybody,

I have some data, that I want ot parse in an mySQL database. The data consists of a timestamp which has millisecond precision and some other colums. The data is stored in a table.

I use mysql-connector-java-5.1.47mysql-connector-java-5.1.47-bin.jar as described to connect to the database and write with:

sqlwrite(conn,'Messdaten',testtable(1:5,:))

Inserting into the database works, however the millisecond precision is gone altough I declared the column as datetime(3). The data that is being stored has the e.g. the value 2019-02-16 10:59:46.000 instead of 2019-02-16 10:59:46.425

Can somebody please gibe me a hint what I am doing wrong ?

Thanks in advance!

Answer 1

[Rajanya]

Hi @Benjamin Lemmer,

I understand that you are trying to insert data containing ‘datetime’ values with millisecond precision into a MySQL database table. The reason the precision of the ‘datetime’ is lost, despite the correct format in ‘sqlwrite’, is because of the version of MySQL and the connector that you are using.

According to an externally published bug report from MySQL, microseconds precision is not retained by TIME, DATETIME, and TIMESTAMP fields in any version prior to 5.6.4. Feeding it a timestamp that has fractions of a second in all such versions, causes the datetime field to come out with all 0s.

A good idea would be to use later versions of MySQL connectors.

Refer to the bug report here - https://bugs.mysql.com/bug.php?id=8523

Hope this helps!