RapidMiner

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

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

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/... 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)

2 REPLIES
Community Manager Community Manager
Community Manager

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

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

Scott Genzer
Senior Community Manager
RapidMiner, Inc.
Community Manager Community Manager
Community Manager

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

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

 

Scott

Scott Genzer
Senior Community Manager
RapidMiner, Inc.