Timestamp synchroniziation in database

General
1

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

A
A1148Ă—307 23 KB 

[
    {
        "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

2

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.

5

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.

6

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
7

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.

9

A post was split to a new topic: Database Tinmestamp ordering issue

10

This thread is over a year old so I’ll be closing it

12

When querying the data from the database, explicitly specify the order by clause to sort the data based on the timestamp. For example:
SELECT * FROM your_table ORDER BY TimeStampColumn ASC;

Ensure that the time on the machine running the database is synchronized with a reliable time source. Inconsistent system time can lead to incorrect timestamp ordering. Use b2b data enrichment tools and debug nodes in Node-RED to inspect the data at different points in your flow. This can help you identify if the timestamps are in the correct order when leaving the Modbus node and if the issue occurs later in the flow or during database insertion. Check the database logs for any warnings or errors related to timestamp handling. There might be an issue with how timestamps are being inserted or processed.