How to convert MESZ time into UTC?

BlueSky

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>

MartinLiebig

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

ollyclark

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.

BlueSky

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

MartinLiebig

Hi @BlueSky ,

let me jump in here to explain a bit on what a date actually is.

If you store a date as a date or date_time column type we store UNIX timestamp in the underlying data frame. So this is ms since 1970. Depending on your system settings you will see this date then in your local time zone (German in this case). If you send the data set over to @ollyclark his RapidMiner will show it to him in British Standard Time, since he is based in the UK. This is purely a visual thing.

If you convert a date_time into a string can to tell RapidMiner the time zone (locale) you want to use. I think the default is again your system setting. But you may want to use UTC here. This becomes important if you use a RM AIHub, which might be in a different time zone.

On System in Write CSV: Linux and Windows use different character conversions to write into a text file. By default we use windows encoding if you are on windows and I think utf-8 if you are on Mac or Linux. If you want to use another one than your system's one you can choose it there. To be honest this only becomes important if you want to move it to another system. And even then you can choose the encoding when importing.

Cheers,

Martin

BlueSky

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