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

christos_karras

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)

sgenzer

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

sgenzer

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