issue with date conversion

kaymankayman Member Posts: 662 Unicorn
edited December 2018 in Help

I'm trying to get the last 30 days of data for an imported csv, but the system tells me that my dateformats are incompatible. However, i can's see any real reason why or workaround, so any advice is welcome.

 

My process is as follows :

 

-> read csv, date is imported as nominal and is formatted as 'YYYY-MM-DD HH:mm:ss'

 

-> Nominal to date, date type = date_time, this gives me my date (entryTime) in following format on screen: Jan 7, 2017, 4:15:33 AM CET, but when I copy it it looks like this : Sat Jan 07 16:15:33 CET 2017

 

-> Next I create a macro called startdate, using following function : date_add(date_now(),-30,DATE_UNIT_DAY), which results in something like Jun 30, 2017 7:13:46 PM CEST

 

-> next I create a filter stating that my datefield needs to be bigger or equal than %{startdate} but it gives me an error stating :

'cannot instantiate custom filters: the filter value 'Jun 30, 7:20:30 PM CEST for the attribute  entryTime is not in a valid format. Formatting example: 12/31/2014 3:00:00 PM.

So I assume I need to change my dateformat somehow, but if I use date to nominal in the MM/dd/yyyy format and then back nominal to date I get the same problem. So what do I need to do, or are there other ways to filter dateranges?

 

 

 

Answers

  • BalazsBaranyBalazsBarany Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert Posts: 955 Unicorn

    Hi,

    check the SimpleDateFormat documentation:

    https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html

     

    The DD is the number of days in the year, not the month.

    HH is the actual number of hours (0-23), you should use that if you don't have AM/PM indicators.

     

    Regards,

    Balázs

  • Edin_KlapicEdin_Klapic Moderator, Employee, RMResearcher, Member Posts: 299 RM Data Scientist

    Hi,

     

    The following XML codes provides 2 other possible solutions.

    Both make use of Generate Attribute to generate a helper Attribute which is further used for filtering your dataset.

    As a general remark: When working with dates I tend to use the conversion to milliseconds most of the time, since they are easier to follow.

     

    The first example goes step-by-step converting to millliseconds, generating the filter macro & filter the relevant examples.

    The second example checks if the date fulfills a certain condition (here:

    if(date_after(date,date_add(date_now(),-30,DATE_UNIT_DAY)),true,false)

    and returns a specified value which saves one Operator.

     

    Best,

    Edin

     

    <?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="112" y="34">
    <list key="attribute_values">
    <parameter key="date" value="date_now()"/>
    </list>
    <list key="set_additional_roles"/>
    </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="112" y="136">
    <list key="attribute_values">
    <parameter key="date" value="date_add(date_now(),-40,DATE_UNIT_DAY)"/>
    </list>
    <list key="set_additional_roles"/>
    </operator>
    <operator activated="true" class="append" compatibility="7.5.003" expanded="true" height="103" name="Append" width="90" x="246" y="34"/>
    <operator activated="true" class="generate_attributes" compatibility="7.5.003" expanded="true" height="82" name="Generate Attributes (2)" width="90" x="380" y="34">
    <list key="function_descriptions">
    <parameter key="last30Days" value="date_millis(date)"/>
    </list>
    </operator>
    <operator activated="true" class="generate_macro" compatibility="7.5.003" expanded="true" height="82" name="Generate Macro" width="90" x="514" y="34">
    <list key="function_descriptions">
    <parameter key="todayMinus30" value="date_millis(date_add(date_now(),-30,DATE_UNIT_DAY))"/>
    </list>
    </operator>
    <operator activated="true" class="filter_examples" compatibility="7.5.003" expanded="true" height="103" name="Filter Examples" width="90" x="648" y="34">
    <list key="filters_list">
    <parameter key="filters_entry_key" value="last30Days.ge.%{todayMinus30}"/>
    </list>
    </operator>
    <operator activated="true" class="generate_data_user_specification" compatibility="7.5.003" expanded="true" height="68" name="Generate Data by User Specification (3)" width="90" x="112" y="289">
    <list key="attribute_values">
    <parameter key="date" value="date_now()"/>
    </list>
    <list key="set_additional_roles"/>
    </operator>
    <operator activated="true" class="generate_data_user_specification" compatibility="7.5.003" expanded="true" height="68" name="Generate Data by User Specification (4)" width="90" x="112" y="391">
    <list key="attribute_values">
    <parameter key="date" value="date_add(date_now(),-40,DATE_UNIT_DAY)"/>
    </list>
    <list key="set_additional_roles"/>
    </operator>
    <operator activated="true" class="append" compatibility="7.5.003" expanded="true" height="103" name="Append (2)" width="90" x="246" y="289"/>
    <operator activated="true" class="generate_attributes" compatibility="7.5.003" expanded="true" height="82" name="Generate Attributes (4)" width="90" x="380" y="289">
    <list key="function_descriptions">
    <parameter key="withinLast30Days" value="if(date_after(date,date_add(date_now(),-30,DATE_UNIT_DAY)),true,false)"/>
    </list>
    </operator>
    <operator activated="true" class="filter_examples" compatibility="7.5.003" expanded="true" height="103" name="Filter Examples (2)" width="90" x="648" y="289">
    <list key="filters_list">
    <parameter key="filters_entry_key" value="withinLast30Days.equals.true"/>
    </list>
    </operator>
    <connect from_op="Generate Data by User Specification" from_port="output" to_op="Append" to_port="example set 1"/>
    <connect from_op="Generate Data by User Specification (2)" from_port="output" to_op="Append" to_port="example set 2"/>
    <connect from_op="Append" from_port="merged set" to_op="Generate Attributes (2)" to_port="example set input"/>
    <connect from_op="Generate Attributes (2)" from_port="example set output" to_op="Generate Macro" to_port="through 1"/>
    <connect from_op="Generate Macro" from_port="through 1" to_op="Filter Examples" to_port="example set input"/>
    <connect from_op="Filter Examples" from_port="example set output" to_port="result 1"/>
    <connect from_op="Generate Data by User Specification (3)" from_port="output" to_op="Append (2)" to_port="example set 1"/>
    <connect from_op="Generate Data by User Specification (4)" from_port="output" to_op="Append (2)" to_port="example set 2"/>
    <connect from_op="Append (2)" from_port="merged set" to_op="Generate Attributes (4)" to_port="example set input"/>
    <connect from_op="Generate Attributes (4)" from_port="example set output" to_op="Filter Examples (2)" to_port="example set input"/>
    <connect from_op="Filter Examples (2)" 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="231"/>
    <portSpacing port="sink_result 3" spacing="21"/>
    </process>
    </operator>
    </process>
Sign In or Register to comment.