Due to recent updates, all users are required to create an Altair One account to login to the RapidMiner community. Click the Register button to create your account using the same email that you have previously used to login to the RapidMiner community. This will ensure that any previously created content will be synced to your Altair One account. Once you login, you will be asked to provide a username that identifies you to other Community users. Email us at Community with questions.

SQL Query and date_now()

MikeMike Member Posts: 2 Contributor I
edited November 2018 in Help
Hi folks,

Following problem: There is a mysql table with a Timestamp column. I want to retrieve all entries of the current day. I was quite optimistic that the following operator would do the trick: 

      <operator activated="true" class="read_database" compatibility="5.3.015" expanded="true" height="60" name="DB Import" width="90" x="45" y="30">
        <parameter key="connection" value="myDBname"/>
        <parameter key="query" value="SELECT `ID`,`Timestamp`&#10;FROM `myTablename` Where`Timestamp` &gt;= ?"/>
        <parameter key="prepare_statement" value="true"/>
        <enumeration key="parameters">
          <parameter key="parameter" value="VARCHAR.%{today}"/>
        </enumeration>
      </operator>
Actually it works fine if I use a static value in the macro definition of %{today}: 

    <macros>
      <macro>
        <key>today</key>
        <value>2015-01-13</value>
      </macro>
    </macros>
But it fails when I try to dynamically calculate the current date e.g. as follows: 

    <macros>
      <macro>
        <key>today</key>
        <value>date_str_custom(date_now(),"yyyy-MM-dd")</value>
      </macro>
    </macros>
As a result I get all entries of the table instead.

Anyone got an idea?

Cheers, Mike.

Answers

  • Marco_BoeckMarco_Boeck Administrator, Moderator, Employee, Member, University Professor Posts: 1,996 RM Engineering
    Hi,

    you cannot use expressions in macros directly. Instead you have to add a "Generate Macro" operator. Example process which logs the current date macro to the console: 

    <?xml version="1.0" encoding="UTF-8" standalone="no"?>
    <process version="6.3.000">
      <context>
        <input/>
        <output/>
        <macros/>
      </context>
      <operator activated="true" class="process" compatibility="6.3.000" expanded="true" name="Process">
        <process expanded="true">
          <operator activated="true" class="retrieve" compatibility="6.3.000" expanded="true" height="60" name="Retrieve Iris" width="90" x="45" y="30">
            <parameter key="repository_entry" value="//Samples/data/Iris"/>
          </operator>
          <operator activated="true" class="generate_macro" compatibility="6.3.000" expanded="true" height="76" name="Generate Macro" width="90" x="179" y="30">
            <list key="function_descriptions">
              <parameter key="today" value="date_str_custom(date_now(),&quot;yyyy-MM-dd&quot;)"/>
            </list>
          </operator>
          <operator activated="true" class="print_to_console" compatibility="6.3.000" expanded="true" height="76" name="Print to Console" width="90" x="313" y="30">
            <parameter key="log_value" value="%{today}"/>
          </operator>
          <connect from_op="Retrieve Iris" from_port="output" to_op="Generate Macro" to_port="through 1"/>
          <connect from_op="Generate Macro" from_port="through 1" to_op="Print to Console" to_port="through 1"/>
          <connect from_op="Print to Console" from_port="through 1" to_port="result 1"/>
          <portSpacing port="source_input 1" spacing="0"/>
          <portSpacing port="sink_result 1" spacing="0"/>
          <portSpacing port="sink_result 2" spacing="0"/>
        </process>
      </operator>
    </process>
    Regards,
    Marco
  • MikeMike Member Posts: 2 Contributor I
    Ah, ok, thanks for the quick reply.
    So if there is a datatable, with very many timestamped entries, and I want to query the data for a specific day, which can be chosen dynamically, what would be the best way to do so? I'm a bit reluctant to load the whole table.
    Cheers, Mike.
  • Marco_BoeckMarco_Boeck Administrator, Moderator, Employee, Member, University Professor Posts: 1,996 RM Engineering
    Hi,

    just as you did in your original post, use the macro you define somewhere before executing the "Read Database". You can set the macro with any of the macro operators. As long as it is set before the actual Database operator, you should be fine.


    Regards,
    Marco
Sign In or Register to comment.