Issue with date time column.

nn_here · April 15

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

Roland Jones_21245 · April 15

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>

Roland Jones_21245 · April 15

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>

nn_here · April 16

Hi,
Thankyou so much for spending time and helping me out.

This worked as required!
Thanks and regards,
nn_here

Issue with date time column.

Best Answer

Answers

Categories