Workflow error in QuestDB node

Hi,
I have created a workflow to calculate OEE by fetching data from QuestDB. And this is my workflow:

{
  "name": "OEE_realtime",
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        460,
        310
      ]
    },
    {
      "parameters": {
        "conditions": {
          "number": [],
          "string": [
            {
              "value1": "={{$node[\"Check if timestamp exists\"].json[\"count\"]}}",
              "value2": "0"
            }
          ]
        }
      },
      "name": "IF",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        1030,
        310
      ]
    },
    {
      "parameters": {},
      "name": "NoOp",
      "type": "n8n-nodes-base.noOp",
      "typeVersion": 1,
      "position": [
        1240,
        240
      ]
    },
    {
      "parameters": {
        "keepOnlySet": true,
        "values": {
          "string": [
            {
              "name": "Result",
              "value": "Data not available"
            }
          ]
        },
        "options": {}
      },
      "name": "Set",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        1440,
        240
      ]
    },
    {
      "parameters": {
        "keepOnlySet": true,
        "values": {
          "string": [
            {
              "name": "Date",
              "value": "={{$json[\"date\"]}}"
            },
            {
              "name": "Availability",
              "value": "={{$json[\"Availability\"]}}"
            },
            {
              "name": "Performance",
              "value": "={{$json[\"Performance\"]}}"
            },
            {
              "name": "Quality",
              "value": "={{$json[\"Quality\"]}}"
            },
            {
              "name": "Overall OEE",
              "value": "={{$json[\"OEE\"]}}"
            }
          ]
        },
        "options": {}
      },
      "name": "Set1",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        1650,
        390
      ]
    },
    {
      "parameters": {
        "functionCode": "// Code here will run once per input item.\n// More info and help: https://docs.n8n.io/nodes/n8n-nodes-base.functionItem\n\n// To Calculate Availability\nvar operatingTime = $node[\"Get Production Metrics\"].json[\"operatingTimeMIN\"];\nvar plannedProductionTime = $node[\"Get Production Metrics\"].json[\"PlannedProductionTimeMIN\"];\nitem.Availability = (operatingTime / plannedProductionTime) * 100;\n\n\n//To Calculate Performance\nvar totalPieces = $node[\"Get Production Metrics\"].json[\"TotalPieces\"];\nvar idealRunRate =  $node[\"Get Production Metrics\"].json[\"IdealRunTimePPM\"];\nitem.Performance = ((totalPieces / operatingTime) / idealRunRate) * 100;\n\n//To Calculate Quality\nvar goodPieces = $node[\"Get Production Metrics\"].json[\"GoodPieces\"];\nitem.Quality = (goodPieces / totalPieces) * 100;\n\n//To Calculate OEE;\nitem.OEE = (item.Availability/100 * item.Performance/100 * item.Quality/100) * 100;\n\n// You can write logs to the browser console\nconsole.log('Done!');\n\nreturn item;\n"
      },
      "name": "FunctionItem1",
      "type": "n8n-nodes-base.functionItem",
      "typeVersion": 1,
      "position": [
        1460,
        390
      ]
    },
    {
      "parameters": {
        "functionCode": "var currentdate = new Date();\ncurrentdate.setUTCHours(0,0,0,0);\ncurrentdate.toISOString();\nitem.date = currentdate;\nreturn item;\n"
      },
      "name": "Get Date",
      "type": "n8n-nodes-base.functionItem",
      "typeVersion": 1,
      "position": [
        660,
        310
      ]
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "=SELECT COUNT(*) FROM OEE1\nWHERE date ='{{$node[\"Get Date\"].json[\"date\"]}}';",
        "additionalFields": {}
      },
      "name": "Check if timestamp exists",
      "type": "n8n-nodes-base.questDb",
      "typeVersion": 1,
      "position": [
        840,
        310
      ],
      "credentials": {
        "questDb": "QuestDB account"
      }
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "=SELECT * FROM OEE1\nWHERE date ='{{$node[\"Get Date\"].json[\"date\"]}}';",
        "additionalFields": {}
      },
      "name": "Get Production Metrics",
      "type": "n8n-nodes-base.questDb",
      "typeVersion": 1,
      "position": [
        1250,
        390
      ],
      "credentials": {
        "questDb": "QuestDB account"
      }
    }
  ],
  "connections": {
    "IF": {
      "main": [
        [
          {
            "node": "NoOp",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Get Production Metrics",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "NoOp": {
      "main": [
        [
          {
            "node": "Set",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "FunctionItem1": {
      "main": [
        [
          {
            "node": "Set1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Start": {
      "main": [
        [
          {
            "node": "Get Date",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get Date": {
      "main": [
        [
          {
            "node": "Check if timestamp exists",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Check if timestamp exists": {
      "main": [
        [
          {
            "node": "IF",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get Production Metrics": {
      "main": [
        [
          {
            "node": "FunctionItem1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  },
  "active": false,
  "settings": {},
  "id": 7
}

The problem what I’m facing is, while clicking on “Execute Workflow”, it stops at QuestDB node and gives an error meassge as “ERROR: Invalid date”.

And once again when I execute the QuestDB node alone, where the error occurred, it executes and gives output.

Is it the problem with fetching realtime data? Because, I have already created another workflow to calculate OEE for historical data in QuestDB.

Hi @Meghna_jose, welcome to our community!

I’m so sorry you are running into this behaviour. I did manage to reproduce this on my end. A simple workaround that did the trick was using parameters instead of injecting an expression into the query string like so (I have slightly adjusted the query to match the data in my QuestDB test instance):

image

Example Workflow
{
  "nodes": [
    {
      "parameters": {
        "functionCode": "var currentdate = new Date();\ncurrentdate.setUTCHours(0,0,0,0);\ncurrentdate.toISOString();\nitem.date = currentdate;\nreturn item;\n"
      },
      "name": "Get Date",
      "type": "n8n-nodes-base.functionItem",
      "typeVersion": 1,
      "position": [
        450,
        300
      ]
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "SELECT COUNT(*) FROM telemetry\nWHERE created = $1;",
        "additionalFields": {
          "queryParams": "date"
        }
      },
      "name": "Check if timestamp exists",
      "type": "n8n-nodes-base.questDb",
      "typeVersion": 1,
      "position": [
        630,
        300
      ],
      "credentials": {
        "questDb": {
          "id": "4",
          "name": "QuestDB account"
        }
      }
    }
  ],
  "connections": {
    "Get Date": {
      "main": [
        [
          {
            "node": "Check if timestamp exists",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Would this work for you?

1 Like

Hi @MutedJam ,
It works for me. Thanks a lot for the help.