Variables in "MongoDB Reader"

Let me explain my problem:

  1. I obtained the system date using the “Date&Time Widget” node, which takes the current date and time as its variable. This node shows me the “date-input” variable with the value: 2023-11-08T09:51:30.506 ;
  2. With the “Variable to Table Row” node, I create a table with a field called “date-input” (as shown, it is of string type) and a single record containing the value 2023-11-08T09:51:30.506 ;
  3. With the “String to Date&Time” node, I convert this field to date and time with the format yyyy-MM-dd’T’HH:mm[:ss[.SSS]] and name the resulting field “date_inputActualDate” (a date type field);
  4. Then I use the “Date&Time Shift” node to subtract one day from the date to get the date of the previous day, in this node I add the field “date_inputActualDate(shifted)”, a date type field with the value: 2023-11-07T09:51:30.506 ;
  5. With the “Column Rename” node, I change the names of the variables as follows: I rename the “date-input” field to “SystemDate”, I rename the “date_inputActualDate” field to “TodayDate”, and I rename the “date_inputActualDate(shifted)” field to “YesterdayDate” (If you notice, this field is of type Date);
  6. Then, with the “String Manipulation” node, the “Z” is added as follows: join(string($YesterdayDate$),“Z”) ; obtaining the field (string type) “YesterdayDate” with the value: 2023-11-07T09:51:30.506Z ;
  7. Then, with the “String to Date$Time” node, I convert the (string type) “YesterdayDate” field to date and time with zone, giving it the format “yyyy-MM-dd’T’HH:mm[:ss[.SSS]]VV[‘[‘zzzz’]’]” ; so now the “YesterdayDate” field is of type Date&Time With zone ;
  8. Then, with the “Table Row to Variable” node, I pass the “YesterdayDate” field to a variable; and I connect this node, through the variable ports, to the “MongoDB Connector” node;
  9. And I connect it to the “MongoDB Reader” node, with the query:
    {
    “timeStamp”: {
    “$gte”: { “$date”: “$YesterdayDate” },
    “$lte”: { “$date”: “$YesterdayDate” }
    },
    “printingAuthorizationKey”: { “$exists”: true, “$ne”: null }
    }

and when it executes, it presents the error: “Execute failed: Failed to parse string as a date”. Any proposed solution?

  1. To perform another test, I used the “String Manipulation” node and wrote substr($YesterdayDate$,0,10) to obtain the field “YesterdayDateWithNoTime”, with the value: 2023-11-07 ;
  2. I used the “String Manipulation” node and wrote join($YesterdayDateWithNoTime$, “T00:00:00.001Z”) to obtain the field “YesterdayDateStart”, with the value: 2023-11-07T00:00:00.001Z;
  3. I used the “String Manipulation” node and wrote join($YesterdayDateWithNoTime$, “T23:59:59.999Z”) to obtain the field “YesterdayDateEnd”, with the value: 2023-11-07T23:59:59.999Z;
  4. Then, with the “String to Date$Time” node, I convert these fields (“YesterdayDateStart” and “YesterdayDateEnd”) from string type to date type;
  5. And with the “Table Row to Variable” node, I convert both fields to variables to use them in the “MongoDB Reader” node with the following query:
    {
    “timeStamp”: {
    “$gte”: { “$dateFromString”: {
    “dateString”: “$YesterdayDateStart”,
    “format”: “yyyy-MM-dd’T’HH:mm:ss.SSS’Z’” } },
    “$lte”: { “$dateFromString”: {
    “dateString”: “$YesterdayDateEnd”,
    “format”: “yyyy-MM-dd’T’HH:mm:ss.SSS’Z’” } }
    },
    “printingAuthorizationKey”: { “$exists”: true, “$ne”: null }
    }
    But it generates an empty data table and displays the message: “Node created an empty data table”

When I use the Query:
{
“timeStamp”: {
“$gte”: {“$date”: “$YesterdayDateStart”},
“$lte”: {“$date”: “$YesterdayDateEnd”}
},
“printingAuthorizationKey”: { “$exists”: true, “$ne”: null }
}

It displays the message: “Execute failed: Failed to parse string as a date”

but if I use the query:
{
“timeStamp”: {
“$gte”: {“$date”: “2023-11-07T00:00:00.001Z”},
“$lte”: {“$date”: “2023-11-07T23:59:59.999Z”}
},
“printingAuthorizationKey”: { “$exists”: true, “$ne”: null }
}

It generates the table with 3092 records. Can you help me, please?

The objective is to obtain the invoices from the day prior to the execution of the Knime workflow, without the need to manually input the dates every time the workflow is executed.

Hi @ojmartinez -

Could you also please post your workflow in progress (and dataset) in addition to the description above? That will motivate more people to help you troubleshoot.

Thanks ScottF,

I have already solved the problem. What I did was add a variable that contains all the query I require. In this query, I included the names of the variables that represent the start date and the end date. The “MongoDB Reader” node is now controlled by the variable that contains the query. Now I can run the workflow without needing to manually change the dates.

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.