[Wait node] wait node executing before specific time while using Postgres

[naskio]

Describe the bug
The 'Wait node' is not running at the specific time while using Postgres.

To Reproduce
Steps to reproduce the behavior:

1. Use this docker-compose file to start n8n with Postgres

version: '3.8'

services:
  postgres:
    restart: always
    image: postgres
    environment:
      POSTGRES_DB: postgres
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
    volumes:
      - ./postgres_data:/var/lib/postgresql/data
    expose:
      - 5432

  n8n:
    # image: naskio/n8n-python:latest-debian
    image: n8nio/n8n:latest
    restart: always
    container_name: n8n
    environment:
      NODE_ENV: production
      GENERIC_TIMEZONE: Africa/Algiers
      TZ: Africa/Algiers
      DB_TYPE: postgresdb
      DB_POSTGRESDB_HOST: postgres
      DB_POSTGRESDB_PORT: 5432
      DB_POSTGRESDB_DATABASE: postgres
      DB_POSTGRESDB_USER: postgres
      DB_POSTGRESDB_PASSWORD: postgres
      # N8N_BASIC_AUTH_ACTIVE: true
      # N8N_BASIC_AUTH_USER: admin
      # N8N_BASIC_AUTH_PASSWORD: admin
    ports:
      - 5678:5678
    links:
      - postgres
    depends_on:
      - postgres
    volumes:
      - ./.n8n:/home/node/.n8n

2. Start docker-compose up
3. Add this workflow

{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        500,
        360
      ]
    },
    {
      "parameters": {
        "functionCode": "function randomIntFromInterval(min, max) { // min and max included \n  return Math.floor(Math.random() * (max - min + 1) + min)\n}\n\nfunction add_minutes(dt, minutes) {\n  const tmp = new Date(dt);\n  tmp.setMinutes(tmp.getMinutes()+minutes,0,0);\n  // new Date(dt.getTime() + minutes*60*1000);\n  return tmp;\n}\n\nfunction add_hours(dt, hours) {\n    return new Date(dt.getTime() + hours*60*60*1000);\n}\n\n\nvar date_now = new Date();\n\nconst new_items = [];\nfor (let i =0;i<1;i++) {\n  // const run_at = add_hours(date_now,randomIntFromInterval(1, 5));\n  const run_at = add_minutes(date_now,3);\n  let run_at_str = run_at.toISOString();\n  run_at_str = run_at.toJSON();\n  let item = {json:{run_at:run_at_str}};\n  new_items.push(item);\n}\n// for (item of items) {\n//   item.json.myNewField = randomIntFromInterval(1, 5);\n// }\n\nreturn new_items;\n// return items;"
      },
      "name": "Function",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        960,
        560
      ]
    },
    {
      "parameters": {
        "triggerTimes": {
          "item": [
            {
              "mode": "everyX",
              "value": 5,
              "unit": "minutes"
            }
          ]
        }
      },
      "name": "Cron",
      "type": "n8n-nodes-base.cron",
      "typeVersion": 1,
      "position": [
        740,
        560
      ]
    },
    {
      "parameters": {
        "resume": "specificTime",
        "dateTime": "={{$json[\"run_at\"]}}"
      },
      "name": "Wait",
      "type": "n8n-nodes-base.wait",
      "typeVersion": 1,
      "position": [
        1180,
        560
      ],
      "webhookId": "55a06efd-296e-4d62-90a3-443755195e09"
    },
    {
      "parameters": {
        "values": {
          "string": [
            {
              "name": "finished_at",
              "value": "={{new Date()}}"
            }
          ]
        },
        "options": {}
      },
      "name": "Set",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        1400,
        560
      ]
    }
  ],
  "connections": {
    "Function": {
      "main": [
        [
          {
            "node": "Wait",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Cron": {
      "main": [
        [
          {
            "node": "Function",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Wait": {
      "main": [
        [
          {
            "node": "Set",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

4. Enable save manually triggered executions in settings
5. Execute workflow
6. See results (should have almost same value: run_at and finished_at in Set)

Expected behavior
the Wait node should pause the execution until the specific time specified by run_at, but it is running before the scheduled time.

Environment (please complete the following information):

• OS: macOS 12.2 (using Docker)
• n8n Version: 0.162.0
• Node.js Version: v14.19.0
• Database system: Postgres
• Operation mode: regular

Additional context
I guess that it works properly when using SQLite but the problem occurs when I switched to Postgres.

[janober]

Thanks a lot for reporting!

Could be related to the problem in issue #2178

[naskio]

Hey @janober
Indeed, the issue is related to the datatype of date columns (we should use timestamptz instead of timestamp on Postgres)
To solve the issue, I made two changes:

• I switched all the date fields to timestamptz (on Postgres only). For that we need to use Column instead of UpdateDateColumn and CreateDateColumn in typeorm to be able to set the type to timestamptz.
• I added a new migration file for Postgres to alter all timestamp fields to timestamptz (that way the tables on DB are synchronized with the entities)

I create a PR #2813 that contains the fix for this issue (#2810 , #2178 and potentially #1380 ).

[Joffcom]

Just going to add that internally the underlying issue is something we are tracking internally as N8N-2490

[Joffcom]

Good news, This should now be fixed in 1.9.0. For now I am going to mark this as closed if you are still seeing this let me know and we can open it again.