英文:
How to ensure the data type precision remains the same after importing data with MySQL plugin?
问题
从MySQL使用DolphinDB MySQL插件导入DATETIME类型的数据时，我发现毫秒部分被丢弃了。例如，2023.12.12 09:12:01 500被更改为2023.12.12 09:12:01。
我尝试使用以下脚本解决这个问题：
db=database("dfs://test6", VALUE, `ag2306`fu2305`fu2309)
def replaceTable(mutable t){return t.replaceColumn!(`datetime,nanotimestamp(t[`datetime]))}
t=mysql::loadEx(conn, db, "tb", `symbol, "SELECT * FROM dbtickdata LIMIT 100",,,,,replaceTable)
然而，尽管数据类型已被替换，结果仍然不是我期望的。有谁知道如何解决这个问题？
英文:
When importing data of type DATETIME from MySQL with DolphinDB MySQL plugin, I found that the milliseconds were discarded. For example, 2023.12.12 09:12:01 500 was changed to 2023.12.12 09:12:01.
I have tried to solve this problem with the following script:
db=database("dfs://test6", VALUE, `ag2306`fu2305`fu2309)
def replaceTable(mutable t){return t.replaceColumn!(`datetime,nanotimestamp(t[`datetime]))}
t=mysql::loadEx(conn, db, "tb",`symbol, "SELECT * FROM dbtickdata LIMIT 100",,,,replaceTable)
However, the result is still not what I expected, even though the data type has been replaced. Does anyone know how to solve this problem?
答案1
得分: 1
造成这个问题的原因是MySQL插件将MySQL的DATETIME解析为DolphinDB的DATETIME，而DolphinDB的DATETIME不支持比秒更高的精度。因此，当您将表传递给mysql::loadEx的transform时，其列的时间类型已经是DolphinDB的DATETIME。更高精度的尾数秒部分已被丢弃。
要解决这个问题，请参考以下步骤：
- 使用
mysql::extractSchema获取MySQL表的架构； - 在架构表中将类型为DATETIME的列更新为NANOTIMESTAMP；
- 将架构表传递给
mysql::loadEx的架构。
英文:
The cause is that the MySQL plugin parses the MySQL’s DATETIME into the DolphinDB’s DATETIME, which doesn’t support a higher precision than seconds. So when you pass a table to the transform of mysql::loadEx, the time type of its column has already been DolphinDB’s DATETIME. The trailing fractional seconds part in higher precision has been discarded.
To solve this problem, refer to the following steps:
- use
mysql::extractSchemato get the schema of the MySQL table; - update the column of type DATETIME to NANOTIMESTAMP in the schema
table; - pass the schema table to the schema of
mysql::loadEx.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论