Parse date time with fractional seconds (less than milliseconds) and time zone offset

christos_karraschristos_karras Member Posts: 50 Guru
edited December 2018 in Help

I'm trying to parse dates from a CSV file but can't make it work (either directly in the Read CSV operator, or in a Nominal to Date operator).

 

A sample date is 2017-04-01 00:41:20.999 +00:00

I tried the following format, as well as variations removing the quotes:

'yyyy-MM-dd' 'HH:mm:ss.SSS' 'Z'

 

I looked at https://docs.rapidminer.com/studio/operators/blending/attributes/types/nominal_to_date.html and http://community.rapidminer.com/t5/RapidMiner-Studio-Knowledge-Base/Sample-formats-for-Date-Parsing/ta-p/37031 and this seems to be the correct format according to these references.

 

I get an error saying "Unparseable date: '2017-04-01 00:41:20.999 +00:00'".

Where is my error?

 

Also, the dates I need to parse have in fact 7 fractional second digits (2017-04-01 00:41:20.9992222 +00:00). I worked around this by using a regex to keep only the first 3 digits, but would there be a format I can use for this? (similar to using "HH:mm:ss.fffffff" in .Net)

Answers

  • sgenzersgenzer Administrator, Moderator, Employee, RapidMiner Certified Analyst, Community Manager, Member, University Professor, PM Moderator Posts: 2,959 Community Manager

    Hi Christos -

     

    The quick solution is to remove the colon ":" from the timezone nominal date.  Here is a process to look at.  The top part is your nominal date converted (without colon); the bottom part is just showing you a timestamp going back and forth between nominal and date formats.

     

    <?xml version="1.0" encoding="UTF-8"?><process version="7.5.003">
    <context>
    <input/>
    <output/>
    <macros/>
    </context>
    <operator activated="true" class="process" compatibility="7.5.003" expanded="true" name="Process">
    <process expanded="true">
    <operator activated="true" class="generate_data_user_specification" compatibility="7.5.003" expanded="true" height="68" name="Generate Data by User Specification" width="90" x="45" y="34">
    <list key="attribute_values">
    <parameter key="date" value="&quot;2017-04-01 00:41:20.999 +0000&quot;"/>
    </list>
    <list key="set_additional_roles"/>
    </operator>
    <operator activated="true" class="nominal_to_date" compatibility="7.5.003" expanded="true" height="82" name="Nominal to Date (3)" width="90" x="179" y="34">
    <parameter key="attribute_name" value="date"/>
    <parameter key="date_type" value="date_time"/>
    <parameter key="date_format" value="yyyy-MM-dd HH:mm:ss.SSS Z"/>
    </operator>
    <operator activated="true" class="generate_data_user_specification" compatibility="7.5.003" expanded="true" height="68" name="Generate Data by User Specification (2)" width="90" x="45" y="187">
    <list key="attribute_values"/>
    <list key="set_additional_roles"/>
    </operator>
    <operator activated="true" class="generate_attributes" compatibility="7.5.003" expanded="true" height="82" name="Generate Attributes" width="90" x="179" y="187">
    <list key="function_descriptions">
    <parameter key="timestamp" value="date_now()"/>
    </list>
    </operator>
    <operator activated="true" breakpoints="after" class="date_to_nominal" compatibility="7.5.003" expanded="true" height="82" name="Date to Nominal" width="90" x="313" y="187">
    <parameter key="attribute_name" value="timestamp"/>
    <parameter key="date_format" value="yyyy-MM-dd HH:mm:ss.SSS Z"/>
    </operator>
    <operator activated="true" class="nominal_to_date" compatibility="7.5.003" expanded="true" height="82" name="Nominal to Date" width="90" x="447" y="187">
    <parameter key="attribute_name" value="timestamp"/>
    <parameter key="date_type" value="date_time"/>
    <parameter key="date_format" value="yyyy-MM-dd HH:mm:ss.SSS Z"/>
    </operator>
    <connect from_op="Generate Data by User Specification" from_port="output" to_op="Nominal to Date (3)" to_port="example set input"/>
    <connect from_op="Nominal to Date (3)" from_port="example set output" to_port="result 1"/>
    <connect from_op="Generate Data by User Specification (2)" from_port="output" to_op="Generate Attributes" to_port="example set input"/>
    <connect from_op="Generate Attributes" from_port="example set output" to_op="Date to Nominal" to_port="example set input"/>
    <connect from_op="Date to Nominal" from_port="example set output" to_op="Nominal to Date" to_port="example set input"/>
    <connect from_op="Nominal to Date" from_port="example set output" to_port="result 2"/>
    <portSpacing port="source_input 1" spacing="0"/>
    <portSpacing port="sink_result 1" spacing="0"/>
    <portSpacing port="sink_result 2" spacing="0"/>
    <portSpacing port="sink_result 3" spacing="0"/>
    </process>
    </operator>
    </process>

    Scott

  • sgenzersgenzer Administrator, Moderator, Employee, RapidMiner Certified Analyst, Community Manager, Member, University Professor, PM Moderator Posts: 2,959 Community Manager

    seems like a valid point to me.  :)  I'm moving your message to the "Ideas" section so that the dev team can follow up.  Thanks for the feedback!

     

    Scott

Sign In or Register to comment.