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/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
-
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=""2017-04-01 00:41:20.999 +0000""/>
</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
0 -
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
0