Timestamp synchroniziation in database

Dear Friends,
Hi, recently I am trying to develop a data acquisition platform and its already done but I have an issue with database! The problem is despite the fact that Modbus reader fetching data every 2 second and then
stores in database but when query data in database timestamps are not ordred correctly! I would appreciate it if you share your experince or help me to find my error.

Thanks

[
    {
        "id": "ae72b941.c54948",
        "type": "tab",
        "label": "test",
        "disabled": false,
        "info": ""
    },
    {
        "id": "dee10d3d7a44d6c4",
        "type": "debug",
        "z": "ae72b941.c54948",
        "name": "",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 670,
        "y": 1800,
        "wires": []
    },
    {
        "id": "b709d8c110c2bb63",
        "type": "debug",
        "z": "ae72b941.c54948",
        "name": "",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 430,
        "y": 1800,
        "wires": []
    },
    {
        "id": "c7a8e8fc25479d9e",
        "type": "modbus-read",
        "z": "ae72b941.c54948",
        "name": "",
        "topic": "",
        "showStatusActivities": false,
        "logIOActivities": false,
        "showErrors": false,
        "unitid": "1",
        "dataType": "HoldingRegister",
        "adr": "8192",
        "quantity": "32",
        "rate": "2",
        "rateUnit": "s",
        "delayOnStart": false,
        "startDelayTime": "",
        "server": "d631304d.f42a4",
        "useIOFile": false,
        "ioFile": "",
        "useIOForPayload": false,
        "emptyMsgOnFail": false,
        "x": 230,
        "y": 1740,
        "wires": [
            [],
            [
                "b709d8c110c2bb63",
                "27adae31ad03913a"
            ]
        ]
    },
    {
        "id": "6c5c8a6ddaf3c0da",
        "type": "MSSQL",
        "z": "ae72b941.c54948",
        "mssqlCN": "491533d6adb397ad",
        "name": "",
        "outField": "payload",
        "returnType": "1",
        "throwErrors": 1,
        "query": "INSERT INTO BMU1 \r\n    (TimeStamp, Cell_V1, Cell_V2, Cell_V3, Cell_V4,Cell_V5,\r\n    Cell_V6, Cell_V7, Cell_V8, Cell_V9, Cell_V10, Cell_V11,\r\n    Cell_V12, Cell_V13, Cell_V14, Cell_V15, Cell_V16,Cell_T1,\r\n    Cell_T2, Cell_T3, Cell_T4, Cell_T5, Cell_T6, Cell_T7, Cell_T8) \r\nVALUES\r\n    (@ts, @v1, @v2, @v3,@v4,@v5,@v6,@v7,@v8,@v9,@v10,@v11,@v12,@v13\r\n    ,@v14,@v15,@v16,@t1,@t2,@t3,@t4,@t5,@t6,@t7,@t8)\r\n",
        "modeOpt": "queryMode",
        "modeOptType": "query",
        "queryOpt": "payload",
        "queryOptType": "editor",
        "paramsOpt": "",
        "paramsOptType": "editor",
        "rows": "rows",
        "rowsType": "msg",
        "params": [
            {
                "output": false,
                "name": "ts",
                "type": "DateTime",
                "valueType": "datetime",
                "value": "0",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "v1",
                "type": "Float",
                "valueType": "msg",
                "value": "payload[0]",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "v2",
                "type": "Float",
                "valueType": "msg",
                "value": "payload[1]",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "v3",
                "type": "Float",
                "valueType": "msg",
                "value": "payload[2]",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "v4",
                "type": "Float",
                "valueType": "msg",
                "value": "payload[3]",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "v5",
                "type": "Float",
                "valueType": "msg",
                "value": "payload[4]",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "v6",
                "type": "Float",
                "valueType": "msg",
                "value": "payload[5]",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "v7",
                "type": "Float",
                "valueType": "msg",
                "value": "payload[6]",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "v8",
                "type": "Float",
                "valueType": "msg",
                "value": "payload[7]",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "v9",
                "type": "Float",
                "valueType": "msg",
                "value": "payload[8]",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "v10",
                "type": "Float",
                "valueType": "msg",
                "value": "payload[9]",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "v11",
                "type": "Float",
                "valueType": "msg",
                "value": "payload[10]",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "v12",
                "type": "Float",
                "valueType": "msg",
                "value": "payload[11]",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "v13",
                "type": "Float",
                "valueType": "msg",
                "value": "payload[12]",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "v14",
                "type": "Float",
                "valueType": "msg",
                "value": "payload[13]",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "v15",
                "type": "Float",
                "valueType": "msg",
                "value": "payload[14]",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "v16",
                "type": "Float",
                "valueType": "msg",
                "value": "payload[15]",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "t1",
                "type": "Float",
                "valueType": "msg",
                "value": "payload[24]",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "t2",
                "type": "Float",
                "valueType": "msg",
                "value": "payload[25]",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "t3",
                "type": "Float",
                "valueType": "msg",
                "value": "payload[26]",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "t4",
                "type": "Float",
                "valueType": "msg",
                "value": "payload[27]",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "t5",
                "type": "Float",
                "valueType": "msg",
                "value": "payload[28]",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "t6",
                "type": "Float",
                "valueType": "msg",
                "value": "payload[29]",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "t7",
                "type": "Float",
                "valueType": "msg",
                "value": "payload[30]",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            },
            {
                "output": false,
                "name": "t8",
                "type": "Float",
                "valueType": "msg",
                "value": "payload[31]",
                "options": {
                    "nullable": true,
                    "primary": false,
                    "identity": false,
                    "readOnly": false
                }
            }
        ],
        "x": 860,
        "y": 1760,
        "wires": [
            [
                "2f62b223598d358f"
            ]
        ]
    },
    {
        "id": "2f62b223598d358f",
        "type": "debug",
        "z": "ae72b941.c54948",
        "name": "",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 1070,
        "y": 1760,
        "wires": []
    },
    {
        "id": "734b42ea206faa31",
        "type": "debug",
        "z": "ae72b941.c54948",
        "name": "",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 590,
        "y": 1700,
        "wires": []
    },
    {
        "id": "27adae31ad03913a",
        "type": "buffer-parser",
        "z": "ae72b941.c54948",
        "name": "",
        "data": "payload.data",
        "dataType": "msg",
        "specification": "spec",
        "specificationType": "ui",
        "items": [
            {
                "type": "int16be",
                "name": "Array[32] of 16bits",
                "offset": 0,
                "length": 32,
                "offsetbit": 2,
                "scale": "1",
                "mask": ""
            }
        ],
        "swap1": "",
        "swap2": "",
        "swap3": "",
        "swap1Type": "swap",
        "swap2Type": "swap",
        "swap3Type": "swap",
        "msgProperty": "payload",
        "msgPropertyType": "",
        "resultType": "value",
        "resultTypeType": "output",
        "multipleResult": true,
        "fanOutMultipleResult": false,
        "setTopic": true,
        "outputs": 1,
        "x": 430,
        "y": 1700,
        "wires": [
            [
                "734b42ea206faa31",
                "d74995adabfd1eb2"
            ]
        ]
    },
    {
        "id": "d74995adabfd1eb2",
        "type": "function",
        "z": "ae72b941.c54948",
        "name": "Data scaling",
        "func": "//msg.topic = \"Varible 1\";\n//msg.payload = msg.payload[0];\n//return msg;\n\nfor (var i = 0; i < 32; i++) {\n    if (i == 16 ){\n        break;\n    }\n    msg.payload[i] = msg.payload[i]/1000;\n    \n}\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 430,
        "y": 1760,
        "wires": [
            [
                "dee10d3d7a44d6c4",
                "6c5c8a6ddaf3c0da",
                "a082c82a987c35cd"
            ]
        ]
    },
    {
        "id": "a082c82a987c35cd",
        "type": "function",
        "z": "ae72b941.c54948",
        "name": "Set global variable",
        "func": "\nglobal.set(\"Module_1\",msg.payload);\n\n\nreturn msg",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 790,
        "y": 1720,
        "wires": [
            [
                "7213e0ef990a3e5c"
            ]
        ]
    },
    {
        "id": "7213e0ef990a3e5c",
        "type": "debug",
        "z": "ae72b941.c54948",
        "name": "Show function output",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 1060,
        "y": 1720,
        "wires": []
    },
    {
        "id": "e5b25143ceb05720",
        "type": "comment",
        "z": "ae72b941.c54948",
        "name": "Module 1",
        "info": "",
        "x": 240,
        "y": 1700,
        "wires": []
    },
    {
        "id": "d631304d.f42a4",
        "type": "modbus-client",
        "name": "",
        "clienttype": "tcp",
        "bufferCommands": true,
        "stateLogEnabled": false,
        "queueLogEnabled": false,
        "tcpHost": "192.168.0.21",
        "tcpPort": "502",
        "tcpType": "DEFAULT",
        "serialPort": "/dev/ttyUSB",
        "serialType": "RTU-BUFFERD",
        "serialBaudrate": "9600",
        "serialDatabits": "8",
        "serialStopbits": "1",
        "serialParity": "none",
        "serialConnectionDelay": "100",
        "serialAsciiResponseStartDelimiter": "",
        "unit_id": "1",
        "commandDelay": "1",
        "clientTimeout": "1000",
        "reconnectOnTimeout": false,
        "reconnectTimeout": "2000",
        "parallelUnitIdsAllowed": false
    },
    {
        "id": "491533d6adb397ad",
        "type": "MSSQL-CN",
        "name": "",
        "server": "localhost",
        "port": "",
        "encyption": false,
        "trustServerCertificate": false,
        "database": "MAXBESS_1",
        "useUTC": false,
        "connectTimeout": "",
        "requestTimeout": "",
        "cancelTimeout": "",
        "pool": "",
        "parseJSON": false,
        "enableArithAbort": true
    }
]

B

How should they be ordered? The snippet you showed all appear to be in the correct order in that they are in ascending time order.

How did you retrieve the records that you have shown us - an SQL query?
Do you define the Timestamp field when you insert a record, or does it default (in the database) to the current time?

I am not familiar with Microsoft's implementation of SQL but generally speaking, if you don't specify an ORDER BY clause, you cannot rely on the records being returned in any particular order.
It looks like your records are not (reliably) returned in Timestamp order.

are you sure its not the Modbus read that is dropping for some reason, leaving that gap of 5 min. ?
Check the options to Show Errors in the Modbus node to see in the Debug window if a read error happens.

image

1 Like

Well, to start, you need to output the raw data so that you can identify whether the issue is in MSSQL or in your IoT devices.