SIARD --> MySql: Error with TIMESTAMP and Problems with Datetime
ibbenz opened this issue · 1 comments
Description:
Date and Time cannot be read properly into a MySQL database in the desired format.
(siard to mySQL)
Background: A study about the capabilities of dbptk, Fullconvert (Spectral Core) and the Siard Suite Tool
Steps required to reproduce the bug:
- open the SIARD file 3_Northwind_simple_2022_07_10_dbptk_V04 in dbptk-Desktop
- Choose "send it to live db" and choose "MySQL"
- Fill in the "General"-Table and check the "disable Encryption"-box
Bug:
"RESTException: Remote exception caused by GenericExeption: Text ' 1968-12-08T11:22:33.123456789' could not be parsed at index 29 caused by DateTimeParseException"
The error still occurred when the Date was changed in table6.xml (employees) to:
1968-12-08T11:22:33,123456789
1968-12-08T11:22:33
1968-12-08T11:22:33+00:00
The adaptions above were made due to a scientific article of dbptk:
https://repositorium.sdum.uminho.pt/bitstream/1822/43479/1/dbptk-ipres16.pdf
Some values obtained from the database may not be in a standard format and must be converted to the standard format. A common example of such values are the DATE, DATETIME, TIMESTAMP and other date or time data types, because the format used internally by DBPTK is the ISO standard for representation of dates and times (ISO 8601)[10]
In order to avoid the timestamp error, another adaptation was made:
- Change in metadata.xml of the SIARD-File
TIMESTAMP
DATETIME
to DATETIME
DATETIME
- Open the SIARD 0_1-3_Northwind_simple_2022_07_10_dbptk_V03.zip in
in dbtpk Desktop - Look into Edit Metadata. The DATETIME datatypes are read as:
type name: Character Varying(21474 83647)
original type name: Datetime - send .siard to db by "send it to live db"
results in the MySQL table 'employees':
'1968-12-08T11:22:33.123456789'
dataype: longtext
The SIARD-files used (the file extension has to be changed to .siard)
0_1-3_Northwind_simple_2022_07_10_dbptk_V04.zip
The issue occurs also for postgreSQL but not for Microsoft SQL