Options

Issue with date time column.

nn_herenn_here Member Posts: 31 Contributor I
Hi,
Kindly help in rectifying an issue being faced with a date column. The value of my Date column is in the format 8/14/2018 9:34. Every minute data is available. I need to segregate to 3 new columns, Day,Month, Minute or hour.
In generate attribute I have given expressions like  MONTHS= date_get([Date], DATE_UNIT_MONTH, "GMT"),DAYS= date_get([Date], DATE_UNIT_DAY, "GMT"),MINUTE=date_get([Date], DATE_UNIT_MINUTE, "GMT").But the columns created are not producing the required results .For July month it shows as 5 for some rows and as 6 for others .Similarly for days  and minutes also the values produced are wrong. Tried changing the time zones to CET,IST .But sadly those too did not help. Kindly let me know where have I gone wrong. Awaiting response.

Thanks and regards,
nn_here

Best Answer

  • Options
    rjones13rjones13 Member Posts: 176 Unicorn
    Solution Accepted
    Hi @nn_here,

    If you're staying in the same timezone, you could use Date to Nominal or Date to Numerical. I've attached an example of Date to Nominal below.

    Best,

    Roland
    <?xml version="1.0" encoding="UTF-8"?><process version="10.3.001">
    
    <context>
    <input/>
    <output/>
    <macros/>
    </context>
    <operator activated="true" class="process" compatibility="10.3.001" expanded="true" name="Process">
    <parameter key="logverbosity" value="init"/>
    <parameter key="random_seed" value="2001"/>
    <parameter key="send_mail" value="never"/>
    <parameter key="notification_email" value=""/>
    <parameter key="process_duration_for_mail" value="30"/>
    <parameter key="encoding" value="UTF-8"/>
    <process expanded="true">
    <operator activated="true" class="utility:create_exampleset" compatibility="10.3.001" expanded="true" height="68" name="Create ExampleSet" width="90" x="179" y="85">
    <parameter key="generator_type" value="comma separated text"/>
    <parameter key="number_of_examples" value="100"/>
    <parameter key="use_stepsize" value="false"/>
    <list key="function_descriptions"/>
    <parameter key="add_id_attribute" value="false"/>
    <list key="numeric_series_configuration"/>
    <list key="date_series_configuration"/>
    <list key="date_series_configuration (interval)"/>
    <parameter key="date_format" value="yyyy-MM-dd HH:mm:ss"/>
    <parameter key="time_zone" value="SYSTEM"/>
    <parameter key="input_csv_text" value="Date&#10;8/14/2018 9:34"/>
    <parameter key="column_separator" value=","/>
    <parameter key="parse_all_as_nominal" value="false"/>
    <parameter key="decimal_point_character" value="."/>
    <parameter key="trim_attribute_names" value="true"/>
    </operator>
    <operator activated="true" class="nominal_to_date" compatibility="10.3.001" expanded="true" height="82" name="Nominal to Date" width="90" x="313" y="85">
    <parameter key="attribute_name" value="Date"/>
    <parameter key="date_type" value="date_time"/>
    <parameter key="date_format" value="MM/dd/yyyy HH:mm"/>
    <parameter key="time_zone" value="SYSTEM"/>
    <parameter key="locale" value="English (United States)"/>
    <parameter key="keep_old_attribute" value="false"/>
    </operator>
    <operator activated="true" class="date_to_nominal" compatibility="10.3.001" expanded="true" height="82" name="Date to Nominal" width="90" x="447" y="85">
    <parameter key="attribute_name" value="Date"/>
    <parameter key="date_format" value="MMMMMMMMMMMM"/>
    <parameter key="time_zone" value="SYSTEM"/>
    <parameter key="locale" value="English (United States)"/>
    <parameter key="keep_old_attribute" value="false"/>
    </operator>
    <operator activated="true" class="date_to_nominal" compatibility="10.3.001" expanded="true" height="82" name="Date to Nominal (2)" width="90" x="581" y="136">
    <parameter key="attribute_name" value="Date"/>
    <parameter key="date_format" value="dd"/>
    <parameter key="time_zone" value="SYSTEM"/>
    <parameter key="locale" value="English (United States)"/>
    <parameter key="keep_old_attribute" value="false"/>
    </operator>
    <operator activated="true" class="date_to_nominal" compatibility="10.3.001" expanded="true" height="82" name="Date to Nominal (3)" width="90" x="715" y="187">
    <parameter key="attribute_name" value="Date"/>
    <parameter key="date_format" value="HH"/>
    <parameter key="time_zone" value="SYSTEM"/>
    <parameter key="locale" value="English (United States)"/>
    <parameter key="keep_old_attribute" value="false"/>
    </operator>
    <connect from_op="Create ExampleSet" from_port="output" to_op="Nominal to Date" to_port="example set input"/>
    <connect from_op="Nominal to Date" 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_port="result 1"/>
    <connect from_op="Date to Nominal" from_port="original" to_op="Date to Nominal (2)" to_port="example set input"/>
    <connect from_op="Date to Nominal (2)" from_port="example set output" to_port="result 2"/>
    <connect from_op="Date to Nominal (2)" from_port="original" to_op="Date to Nominal (3)" to_port="example set input"/>
    <connect from_op="Date to Nominal (3)" from_port="example set output" to_port="result 3"/>
    <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"/>
    <portSpacing port="sink_result 4" spacing="0"/>
    </process>
    </operator>
    </process>

Answers

  • Options
    nn_herenn_here Member Posts: 31 Contributor I
    Hi,
    Thankyou so much for spending time and helping me out. :) This worked as required!
    Thanks and regards,
    nn_here
Sign In or Register to comment.