🎉Community Raffle - Win $25

An exclusive raffle opportunity for active members like you! Complete your profile, answer questions and get your first accepted badge to enter the raffle.
Join and Win

SQL Query and date_now()

MikeUser: "Mike"
Mike
New Altair Community Member
Updated by Jocelyn
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.

Find more posts tagged with

AI Studio
Sort by:
1 - 3 of 31
    Marco_BoeckUser: "Marco_Boeck"
    Marco_Boeck
    New Altair Community Member
    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
    MikeUser: "Mike"
    Mike
    New Altair Community Member
    OP
    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_BoeckUser: "Marco_Boeck"
    Marco_Boeck
    New Altair Community Member
    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
Quick Links