Write date to mySQL with millisecond precision

4 次查看（过去 30 天）
Benjamin Lemmer
Benjamin Lemmer 2019-3-23
回答： Rajanya 2024-10-7
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!

请先登录，再回答此问题。

回答（1 个）

Rajanya
Rajanya 2024-10-7
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!

类别

在 Help Center 和 File Exchange 中查找有关 Database Toolbox 的更多信息

标签

产品


版本

R2018b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by