Upload Data from Siard Suite to MySQL: Time Shift summer-wintertime despite -Duser.timezone=GMT or -Duser.timezone=UTC

[ibbenz]

Whereas it is possible to prevent the time shift summer--> wintertime by using -Duser.timezone=GMT or -Duser.timezone=UTC in the case of loading data from .siard into Siard GUI it seems that this does not work properly in case of the upload into My SQL. The following attempts were made in order to prevent the timeshift:

1. Try I start the Siard GUI from cmd window, read the .siard into Siard GUI and upload the data afterwards to MySQL: java -jar -Duser.timezone=UTC ./lib/siardgui.jar

The data is loaded into Siard GUI without timeshift as it should. But loading the data afterwards from Siard GUI into a MySQL-DB causes a timeshift.

Printscreen:

2. Try Direct upload into MySQL using the Command Window. Each of the following attempts did not succeed (time shift remained).

java -cp C:/KOST/Software_KOST/SIARD_Suite/siard_suite-2.1/lib/siardcmd.jar -Duser.timezone=GMT ch.admin.bar.siard2.cmd.SiardToDb -o -s=C:/KOST/2022_07_11_SIARD_Test/0_Testdatei/0_1-3_Northwind_simple_2022_07_10_V04.siard -j=jdbc:mysql://localhost:3306/siardsimple -u=root -p=**** SimpleDB siardsimple

java -Duser.timezone=GMT -cp C:/KOST/Software_KOST/SIARD_Suite/siard_suite-2.1/lib/siardcmd.jar ch.admin.bar.siard2.cmd.SiardToDb -o -s=C:/KOST/2022_07_11_SIARD_Test/0_Testdatei/0_1-3_Northwind_simple_2022_07_10_V04.siard -j=jdbc:mysql://localhost:3306/siardsimple -u=root -p=**** SimpleDB siardsimple

java -cp C:/KOST/Software_KOST/SIARD_Suite/siard_suite-2.1/lib/siardcmd.jar ch.admin.bar.siard2.cmd.SiardToDb -Duser.timezone=GMT -o -s=C:/KOST/2022_07_11_SIARD_Test/0_Testdatei/0_1-3_Northwind_simple_2022_07_10_V04.siard -j=jdbc:mysql://localhost:3306/siardsimple -u=root -p=*** SimpleDB siardsimple

The .siard-Data: 0_1-3_Northwind_simple_2022_07_10_V04.zip

[walkerjan]

Thanks a lot for reporting this issue.

We shall investigate it and consider respective measures to be taken.

[mburri]

@ibbenz

That's because your database now uses a different timezone to parse/interpret the string that comes from the arichve file than you've set for the application when you run it with -Duser.timezone=UTC. So now you have to configure the same time zone for your mysql database in the mysql config file like this:

[server]
default-time-zone=+01:00

But I think that is less than ideal - first - +01:00 is actually not a time zone but an offset... but that is an other topic.

It's quite a comlex - i think SIARD Suite stores dates in UTC - and thus exactly defines a point in time. Now the SIARD GUI converts this timestring into a human readable date - think of the watch on your wall - and it does it in your time zone, that means your systems current time zone - not in UTC. So I think the initial behaviour was correct and you should not overwrite your timezone when starting the SiardGUI.

But I'm not sure If its the expected behaviour for users. We have to discuss this internally, check the actual implementation and discuss with users what their actual expectations and requirements are.

[ibbenz]

If I do not overwrite the timezone in Siard Suite and export the timestamp-data afterwards from Siard Suite into a MySQL-DB, the 1h-timeshift occurs. If I overwrite the timezone in Siard Suite and export the timestamp-data from Siard Suite into MySQL-DB the timeshift occurs too. So, in each case the timeshift occurs.

[ibbenz]

The timeshift also occurs in case of Oracle, postgreSQL and Microsoft SQL Databases.

TIMESTAMP-Date in the imported SIARD: 1968-12-08T11:22:33.123456789 TIMESTAMP-Date in the database: 1968-12-08 12:22:33.123457 TIMESTAMP-Date in the exported SIARD: 1968-12-08T11:22:33.123456800Z