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

ecdbertecdbert MemberPosts:19Maven
inHelp
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?
Tagged:

Best Answer

  • Sharan_GadiSharan_Gadi Administrator, Moderator, Employee, MemberPosts:6RM Team Member
    Solution Accepted
    @ecdbert——你三ed modifying the database connection to not to use TimeZone parameter as shown below in screenshot and see if that helps ?

    ecdbert MichaelKnopf

Answers

  • MichaelKnopfMichaelKnopf Administrator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert, MemberPosts:31RM Data Scientist
    edited February 2021
    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 toAmerica/New_Yorkthe current date and time (date_now()) is displayed as:Feb 25, 2021 4:57:14 AM EST. Writing the date to an Excel file usingWrite Excelresults in a file that shows the date value as2021-02-25 10:57:14when 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
  • ecdbertecdbert MemberPosts:19Maven
    @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.
  • MichaelKnopfMichaelKnopf Administrator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert, MemberPosts:31RM Data Scientist
    edited February 2021
    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.
  • ecdbertecdbert MemberPosts:19Maven
    @Thanksagain. 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.


  • ecdbertecdbert MemberPosts:19Maven
    Sharan_Gadi. Awesome. That's it! Worked perfectly. Thanks much.
Sign InorRegisterto comment.