How to convert MESZ time into UTC?

BlueSkyBlueSky Member Posts: 3 Newbie
Hi,
i have data in the MESZ time format and want to transform it to UTC timestamp.
the csv reader can read the data:

but then i don't know how to transform the data to UTC format and write it in a csv file.
My goal for the outpout would be for the first line/column: "15.05.2022 16:00:00" with no timezone at the end. the process has to take into account that in the winter the difference between MEZ and UTC is 1h and in the summer UTC is 2h behind.

so how do i do this?

Have a good one

PS i attached the files by accident twice. Same files just didn't know how to delete one..

sion="1.0" encoding="UTF-8"?><process version="9.10.008"><br>&nbsp; <context><br>&nbsp;&nbsp;&nbsp; <input/><br>&nbsp;&nbsp;&nbsp; <output/><br>&nbsp;&nbsp;&nbsp; <macros/><br>&nbsp; </context><br>&nbsp; <operator activated="true" class="process" compatibility="9.10.008" expanded="true" name="Process"><br>&nbsp;&nbsp;&nbsp; <parameter key="logverbosity" value="init"/><br>&nbsp;&nbsp;&nbsp; <parameter key="random_seed" value="2001"/><br>&nbsp;&nbsp;&nbsp; <parameter key="send_mail" value="never"/><br>&nbsp;&nbsp;&nbsp; <parameter key="notification_email" value=""/><br>&nbsp;&nbsp;&nbsp; <parameter key="process_duration_for_mail" value="30"/><br>&nbsp;&nbsp;&nbsp; <parameter key="encoding" value="SYSTEM"/><br>&nbsp;&nbsp;&nbsp; <process expanded="true"><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <operator activated="true" class="read_csv" compatibility="9.10.008" expanded="true" height="68" name="Read CSV" width="90" x="45" y="34"><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="csv_file" value="D:\Benutzer\Frank\Downloads\Funding Rate History_BTCUSDT Perpetual_2022-05-15 - Kopie.csv"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="column_separators" value=","/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="trim_lines" value="false"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="use_quotes" value="true"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="quotes_character" value="&quot;"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="escape_character" value="\"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="skip_comments" value="true"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="comment_characters" value="#"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="starting_row" value="1"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="parse_numbers" value="true"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="decimal_character" value="."/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="grouped_digits" value="false"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="grouping_character" value=","/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="infinity_representation" value=""/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="date_format" value="yyyy-MM-dd HH:mm:ss"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="first_row_as_names" value="false"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <list key="annotations"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="time_zone" value="SYSTEM"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="locale" value="Englisch (Vereinigte Staaten von Amerika)"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="encoding" value="windows-1252"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="read_all_values_as_polynominal" value="false"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <list key="data_set_meta_data_information"><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="0" value="time.true.date_time.attribute"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="1" value="contract.false.polynominal.attribute"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="2" value="att3.false.polynominal.attribute"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="3" value="att4.true.polynominal.attribute"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </list><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="read_not_matching_values_as_missings" value="false"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </operator><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <operator activated="true" class="nominal_to_date" compatibility="9.10.008" expanded="true" height="82" name="Nominal to Date" width="90" x="179" y="34"><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="attribute_name" value="time"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="date_type" value="date_time"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="date_format" value="dd.MM.yyyy HH:mm:ss z"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="time_zone" value="UTC"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="locale" value="Deutsch (Deutschland)"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="keep_old_attribute" value="false"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </operator><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <operator activated="true" class="write_csv" compatibility="9.10.008" expanded="true" height="82" name="Write CSV" width="90" x="313" y="34"><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="column_separator" value=";"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="write_attribute_names" value="true"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="quote_nominal_values" value="true"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="format_date_attributes" value="true"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="date_format" value="yyyy-MM-dd HH:mm:ss"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="append_to_file" value="false"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <parameter key="encoding" value="SYSTEM"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </operator><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <connect from_op="Read CSV" from_port="output" to_op="Nominal to Date" to_port="example set input"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <connect from_op="Nominal to Date" from_port="example set output" to_op="Write CSV" to_port="input"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <connect from_op="Write CSV" from_port="through" to_port="result 1"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <portSpacing port="source_input 1" spacing="0"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <portSpacing port="sink_result 1" spacing="0"/><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <portSpacing port="sink_result 2" spacing="0"/><br>&nbsp;&nbsp;&nbsp; </process><br>&nbsp; </operator><br></process><br>




ollyclark

Best Answer

  • mschmitzmschmitz Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert, University Professor Posts: 3,254 RM Data Scientist
    Solution Accepted
    Hi,
    little aftermath about locale:
    you can use patterns which include for example a day of the week, For example:
    EEE, dd.MM.yyyy
    with an English locale you get:
    Fri, 27.05.2022
    With a German locale you get:
    Fr, 27.05.2022

    And with a French locale you get:
    ven., 27.05.2022

    If you don't use a pattern which differs in the locale it makes no difference what you set (i.e for dd.MM.yyyy)

    Best,
    Martin
    - Head of Data Science Services at RapidMiner -
    Dortmund, Germany
    BlueSky

Answers

  • ollyclarkollyclark Employee, Member Posts: 2 RM Team Member
    Hi BlueSky - you can use the "Date to Nominal" operator to transform a date to a string of text for a given time zone (here, you want UTC) and in a given format (for the example you give this is dd.MM.yyyy HH:mm:ss). Then you can use "Write CSV" to write your data out to a file.
    BlueSkyBalazsBarany
  • BlueSkyBlueSky Member Posts: 3 Newbie
    Hi thanks it worked!

    But some fields are barely explained in the help so what is:

    1. What is the "locale" in Date to Nominal actually do?
    2. And System in Write Csv? It takes the encoding for the csv from my System? Linke Windows Ansi?
    3. Is there a way to stop the writer from overwriting an existing csv? That i can chose what to do lie: append or overwrite or stop with error message?
    Have a good evening
  • BlueSkyBlueSky Member Posts: 3 Newbie
    Hi MSchmitz,

    Thanks for that awesome answer. I was already wondering like "yeah but locale doesn't do anything".. Then i saw your additional answer. That did it. I am from germany too :)

    One thing i noticed tho "E" or "EE" or "EEE" doesn't seem to make any difference it's always "Do" for example.

    According to Java documentation (version 8):
    https://docs.oracle.com/javase/8/docs/api/java/time/format/DateTimeFormatter.html (no virus promis)

    it should. (Best your search for "Tuesday" in the page).
    Or is this just one of the endless bugs of java date&Time?

    Have a day!

    Edit: As a noob i could post a link

Sign In or Register to comment.