Dates/Times retrieved from DB gets convert to time zone in settings- How can I prevent this

ecdbert

I just realized that the dates retrieved from a database using the 'Read Database' operator gets converted to what ever the time zone setting is in the preferences.  I do not want this to happen.  I want RM to use return the date/time exactly as it is in the database.  How can I do this?

Sharan_Gadi

@ecdbert - Have you tried modifying the database connection to not to use TimeZone parameter as shown below in screenshot and see if that helps ?

MichaelKnopf

RapidMiner uses a timezone independent representation of dates internally and uses the settings to display the date in your time zone of choice.

For example, if I change my RapidMiner Studio settings to America/New_York the current date and time (date_now()) is displayed as: Feb 25, 2021 4:57:14 AM EST. Writing the date to an Excel file using Write Excel results in a file that shows the date value as 2021-02-25 10:57:14 when opened in Excel. This matches my Windows settings that use central European time. Please note, that both representations refer to the same point in time.

In other words, as long as the values shown in RapidMiner Studio refer to the same point in time as the values in the database, no conversion was applied.

Hope this helps.

- Michael

ecdbert

@Michael. Thanks for your reply. That’s fine in normal circumstances. However, I’m transferring the data to another database and the RapidMiner conversion is what is sent. What I need to have transferred is the original date/time with the original Timezone. In other words, the value shown in RapidMiner is not that of the original data.  I do not want to change the settings each time. 

MichaelKnopf

Since RapidMiner's date representation is timezone independent, RapidMiner does not know the original time zone.

What you describe could be caused by two things: first, there might be a misunderstanding between RapidMiner and one of the two database connections about the format used to exchange date values. In that case the actual data would be corrupted.

Second, the target database might insist on storing a timezone alongside the "point in time". In that case RapidMiner might default to the current settings.

Either way, details will depend on the database systems in use. I will put you in contact with our support.

ecdbert

@Thanks again. The source DB is SQL Server and the destination is MySQL. Weird thing is both fields are date only fields, not date time. I’m using simple retrieve from and write to database operators.

I’ll await a response from support.

Sharan_Gadi

@ecdbert - Have you tried modifying the database connection to not to use TimeZone parameter as shown below in screenshot and see if that helps ?

ecdbert

Sharan_Gadi. Awesome.  That's it! Worked perfectly.  Thanks much.