thingsboard / thingsboard-gateway

Open-source IoT Gateway - integrates devices connected to legacy and third-party systems with ThingsBoard IoT Platform using Modbus, CAN bus, BACnet, BLE, OPC-UA, MQTT, ODBC and REST protocols
https://thingsboard.io/docs/iot-gateway/what-is-iot-gateway/
Apache License 2.0
1.74k stars 844 forks source link

[HELP] Example of using Timestamp (ts) in MQTT.json #1006

Open sambartle opened 1 year ago

sambartle commented 1 year ago

[MQTT Connector]

Please can someone include an example or explain how to use the timestamp (ts) in timeseries data passed via the MQTT.json converter in the gateway? I have tried to add ts to a few places in the example MQTT.json config (and then pass it up, but it does not seem to be respected)

Configuration (Attach your configuration file) Using the standard MQTT.json example supplied with thingsboard-gateway and the following mapping:

  "mapping": [
    {
      "topicFilter": "topic/+/data",
      "converter": {
        "type": "json",
        "deviceNameTopicExpression": "(?<=topic\/)(.*?)(?=\/data)",
        "deviceTypeTopicExpression": "et301",
        "timeout": 60000,
        "timeseries": [
          {
            "type": "double",
            "key": "key1",
            "value": "${p1}"
          },
          {
            "type": "double",
            "key": "key2",
            "value": "${p2}"
          }
        ]
      }
    }
  ]

Passing the MQTT message: {"ts":1669035610000, "values":{"p1":4.5,"p2":45.0}}

If i remove the timestamp the data works as expected. (Example simplified)

Versions (please complete the following information):

samson0v commented 1 year ago

Hi @sambartle, try to send the following data:

{"p1": 4.5, "p2": 45.0, "ts":1669035610000}
sambartle commented 1 year ago

Thank you @samson0v , I have tried this and it doesn't seem to work.. (I also don't see any errors in the gateway log files)

If i send: {"p1": 4.5, "p2": 45.0} the data is successfully logged.

Once I add the ts value, the data is simply ignored. {"p1": 4.5, "p2": 45.0, "ts": 1669043869000}

Do i need to 'map' the ts value as a timestamp in the config perhaps somehow or does it know how to handle ts itself automatically?

samson0v commented 1 year ago

@sambartle, as you can see from the screenshot, data is not ignored:

зображення зображення
sambartle commented 1 year ago

Apologies @samson0v, ignored was a bad choice of word. I meant that once I add the timestamp, thingsboard never logs the data, however with the timestamp removed it works fine.

I have been experimenting overnight and I have found that it works as expected with less parameters in the MQTT connector mapping, but not with all 27.. Perhaps I should include the whole mapping section as I simplified it in my initial post..

There are 27 parameters passed in my JSON MQTT message to the gateway, and this is the mapping config to load those 27 parameters. If I send just the 27 parameters, they pass through the gateway and get sent to thingsboard against the correct device as intended.

When adding the timestamp it will not work if the Connector mapping has 27 datapoints, but it DOES work if it only has 14/15 datapoints.. (and is otherwise identical... )

Here is the complete 27 item MQTT Connector configuration for the message below:

    {
      "topicFilter": "et/+/data",
      "converter": {
        "type": "json",
        "deviceNameTopicExpression": "(?<=et\/)(.*?)(?=\/data)",
        "deviceTypeTopicExpression": "ET",
        "timeout": 60000,
        "timeseries": [
          {
            "type": "double",
            "key": "MeasPercentSmoothed",
            "value": "${p1}"
          },
          {
            "type": "double",
            "key": "MeasMgNm3",
            "value": "${p2}"
          },
          {
            "type": "double",
            "key": "MeasMgM3",
            "value": "${p3}"
          },
          {
            "type": "double",
            "key": "ETSCFm",
            "value": "${p4}"
          },
          {
            "type": "double",
            "key": "ETSCFc",
            "value": "${p5}"
          },
          {
            "type": "double",
            "key": "ETSPresSmpl",
            "value": "${p6}"
          },
          {
            "type": "double",
            "key": "ETSTempSmpl",
            "value": "${p7}"
          },
          {
            "type": "double",
            "key": "InSituSPANCalPIDConSetPoint",
            "value": "${p8}"
          },
          {
            "type": "int",
            "key": "SIG_PRE_AMP_HW_GAIN",
            "value": "${p9}"
          },
          {
            "type": "int",
            "key": "SIG_POST_AMP_HW_GAIN",
            "value": "${p10}"
          },
          {
            "type": "boolean",
            "key": "msPowerUp",
            "value": "${p11}"
          },
          {
            "type": "boolean",
            "key": "msZeroCal",
            "value": "${p12}"
          },
          {
            "type": "boolean",
            "key": "msSpanCal",
            "value": "${p13}"
          },
          {
            "type": "boolean",
            "key": "msInSituSpanCal",
            "value": "${p14}"
          },
          {
            "type": "boolean",
            "key": "msElectronicSpanCal",
            "value": "${p15}"
          },
          {
            "type": "boolean",
            "key": "msMaintenance",
            "value": "${p16}"
          },
          {
            "type": "boolean",
            "key": "psDtBelowThreshold",
            "value": "${p17}"
          },
          {
            "type": "boolean",
            "key": "psDtSaturation",
            "value": "${p18}"
          },
          {
            "type": "boolean",
            "key": "psDrBelowThreshold",
            "value": "${p19}"
          },
          {
            "type": "boolean",
            "key": "psDrSaturation",
            "value": "${p20}"
          },
          {
            "type": "boolean",
            "key": "psContaminationAboveThreshold",
            "value": "${p21}"
          },
          {
            "type": "boolean",
            "key": "psHardwareGainError",
            "value": "${p22}"
          },
          {
            "type": "boolean",
            "key": "csDetectorLevelSimulation",
            "value": "${p23}"
          },
          {
            "type": "boolean",
            "key": "fsProbeDTxSoftwareGainError",
            "value": "${p24}"
          },
          {
            "type": "boolean",
            "key": "fsProbeDTxHardwareGainError",
            "value": "${p25}"
          },
          {
            "type": "boolean",
            "key": "fsProbeDRxSoftwareGainError",
            "value": "${p26}"
          },
          {
            "type": "boolean",
            "key": "fsProbeDRxHardwareGainError",
            "value": "${p27}"
          }
        ]
      }
    }

And the complete message with no timestamp (which i send at 08:48 this morning): {"p1":"1.100","p2":"11.000","p3":"11.000","p4":"0.110","p5":"11.000","p6":"1.100","p7":"11.000","p8":"1.100","p9":"110","p10":"110","p11":"0","p12":"0","p13":"0","p14":"0","p15":"0","p16":"0","p17":"0","p18":"0","p19":"0","p20":"1","p21":"1","p22":"0","p23":"0","p24":"0","p25":"1","p26":"1","p27":"0"} et/CD_inside/data

This data gets through to thingsboard exactly as expected via the gateway: image

If I send the same message, with a ts value added (sent at 08:54): {"p1":"1.100","p2":"11.000","p3":"11.000","p4":"0.110","p5":"11.000","p6":"1.100","p7":"11.000","p8":"1.100","p9":"110","p10":"110","p11":"0","p12":"0","p13":"0","p14":"0","p15":"0","p16":"0","p17":"0","p18":"0","p19":"0","p20":"1","p21":"1","p22":"0","p23":"0","p24":"0","p25":"1","p26":"1","p27":"0", "ts": 1669107254463} et/CD_inside/data

The data does not get to thingsboard.

If I then change the MQTT Connector to this:

    {
      "topicFilter": "et/+/data",
      "converter": {
        "type": "json",
        "deviceNameTopicExpression": "(?<=et\/)(.*?)(?=\/data)",
        "deviceTypeTopicExpression": "ET",
        "timeout": 60000,
        "timeseries": [
          {
            "type": "double",
            "key": "MeasPercentSmoothed",
            "value": "${p1}"
          },
          {
            "type": "double",
            "key": "MeasMgNm3",
            "value": "${p2}"
          },
          {
            "type": "double",
            "key": "MeasMgM3",
            "value": "${p3}"
          },
          {
            "type": "double",
            "key": "ETSCFm",
            "value": "${p4}"
          },
          {
            "type": "double",
            "key": "ETSCFc",
            "value": "${p5}"
          },
          {
            "type": "double",
            "key": "ETSPresSmpl",
            "value": "${p6}"
          },
          {
            "type": "double",
            "key": "ETSTempSmpl",
            "value": "${p7}"
          },
          {
            "type": "double",
            "key": "InSituSPANCalPIDConSetPoint",
            "value": "${p8}"
          },
          {
            "type": "int",
            "key": "SIG_PRE_AMP_HW_GAIN",
            "value": "${p9}"
          },
          {
            "type": "int",
            "key": "SIG_POST_AMP_HW_GAIN",
            "value": "${p10}"
          },
          {
            "type": "boolean",
            "key": "msPowerUp",
            "value": "${p11}"
          },
          {
            "type": "boolean",
            "key": "msZeroCal",
            "value": "${p12}"
          },
          {
            "type": "boolean",
            "key": "msSpanCal",
            "value": "${p13}"
          },
          {
            "type": "boolean",
            "key": "msInSituSpanCal",
            "value": "${p14}"
          }
        ]
      }
    }

Both messages work including the timestamp as expected.

samson0v commented 1 year ago

@sambartle, ok, will investigate

john2151 commented 1 year ago

Similar issue here with TB-Gw 3.3 and UTC timestamps The strange thing for me is, that it also breaks mappings for other topics. As soon as i change the topic filter for the MQTT-Stream with timestamps to something without traffic, the other topic comes back to life. Also noteworthy is, that the data passes through to Thingsboard if it is configured under attributes instead of timeseries.

Any ideas?

Makodan commented 6 months ago

I faced with the similar issue. Thingsboard doesn't process telemetry with timestamps. If I remove the ts from the telemetry message, it works. Any news on this?