MQTT JSON Input with timestamp

Emoncms
,
1

Continuing the discussion from MQTT Strangeness:

So I have come across a real world example. SonoffPow has energy measuring capability. I am using the Sonoff-Tasmota firmware which responds with a JSON topic which has timestamp and then some energy readings. Although I ask for this every 30s, I note it does drift so the timestamp is important.

Looking at the Emoncms input API examples, I see there is an example for a timestamp and CSV. Are these a definitive list of input formats, or could you use a timestamp with a full JSON data set?

Could these input formats be easily used by the MQTT input process?

Finally, is time only accepted in this format (API examples)? IIRC it is milliseconds but I may have remembered that incorrectly. Could any UNIX time format be acceptable?

This is the Sonoff data format. I am not suggesting this is correct for Emoncms input and I anticipate having to do some adjustments in Node-Red.

{
"Time": "2017-12-30T20:50:17",
    "ENERGY": {
        "Total": 0.264,
	    "Yesterday": 0.000,
	    "Today": 0.264,
	    "Period": 1,
	    "Power": 74,
	    "Factor": 0.67,
	    "Voltage": 235,
	    "Current": 0.465
	}
}

I’ll fiddle with using the http API for now but using MQTT will probably be cleaner in the long run.

2 Likes
2

If anyone else is interested in this, I have modified the phpmqtt_input.php file to accept a JSON input and look for a ‘time’ value. The time value can be seconds or a valid time string. I have created a PR if anyone wants to try it out.

Example data below. The MQTT topic should be <base topic>/nodeid. The ‘name’ will become the node key.

At the moment the time value is passed through (so you may get a NULL on the input page if it is a string) but I will change it to remove the time element if all is OK.

I found an issue if you use an ‘old’ time as a string see this issue but I have solved it for these MQTT inputs.

{
	"Total": 77.43,
	"Yesterday": 1.734,
	"Today": 0.803,
	"Period": 1,
	"Power": 73,
	"Factor": 0.65,
	"Voltage": 234,
	"Current": 0.482,
	"time": "2018-01-14T11:05:34.000Z"
}

You can check your data format using jsonlint.com.

3

I guess you are using a Sonoff POW with the Tasmota firmware. How did you get Tasmota to send the above JSON format? Stock Tasmota is sending JSON which has a format like your first post above.

BTW I’ve your phpmqtt_input.php running successfuly here.

4

I’m using Nodered to parse it. I enclose a couple of Nodes here. Bit of a mess as they currently have all sorts of test code in but I think they will work. if you want to just do things simply you can ignore some of the extra stuff I have in.

I had to set up a couple of flows to set the right timezone and adjust the update speed.

Obviously you need an MQTT node to send it out!

HTH

[edit] sorry first attempt was wrong
[edit2] sorry more haste less speed. let me do it properly…
[edit3] Third time lucky! - added in the ability to send time as a string or as seconds.

[
    {
        "id": "f993c14d.d68aa8",
        "type": "mqtt out",
        "z": "25471496.37cf34",
        "name": "",
        "topic": "",
        "qos": "",
        "retain": "",
        "broker": "a1885387.15bd3",
        "x": 450,
        "y": 660,
        "wires": []
    },
    {
        "id": "5fec2b92.1fb9e4",
        "type": "inject",
        "z": "25471496.37cf34",
        "name": "",
        "topic": "cmnd/SonoffPow1/teleperiod",
        "payload": "30",
        "payloadType": "num",
        "repeat": "",
        "crontab": "",
        "once": false,
        "x": 210,
        "y": 640,
        "wires": [
            [
                "f993c14d.d68aa8"
            ]
        ]
    },
    {
        "id": "9f9dbd1b.220c2",
        "type": "inject",
        "z": "25471496.37cf34",
        "name": "",
        "topic": "cmnd/SonoffPow1/Timezone",
        "payload": "0",
        "payloadType": "num",
        "repeat": "",
        "crontab": "",
        "once": true,
        "x": 210,
        "y": 700,
        "wires": [
            [
                "f993c14d.d68aa8"
            ]
        ]
    },
    {
        "id": "9d56c868.e2f568",
        "type": "debug",
        "z": "25471496.37cf34",
        "name": "",
        "active": true,
        "console": "false",
        "complete": "true",
        "x": 910,
        "y": 560,
        "wires": []
    },
    {
        "id": "71019180.7cf328",
        "type": "mqtt in",
        "z": "25471496.37cf34",
        "name": "SonoffPow1 tele",
        "topic": "tele/SonoffPow1/#",
        "qos": "2",
        "broker": "a1885387.15bd3",
        "x": 140,
        "y": 560,
        "wires": [
            [
                "948e95b6.8be568"
            ]
        ]
    },
    {
        "id": "948e95b6.8be568",
        "type": "function",
        "z": "25471496.37cf34",
        "name": "SonoffPow Message output",
        "func": "var topic = [];\nvar msg0 = null;//{};\nvar msg1 = null;//{};\n\ntry{\n    payload = JSON.parse(msg.payload);\n    topic = msg.topic.split(\"/\").pop();\n    if (topic == \"SENSOR\"){\n        msg1 = {};\n        msg1.topic = topic;\n        msg1.payload = payload;\n    }\n    else {\n        msg0 = {};\n        msg0.topic = topic;\n        msg0.payload = payload;\n    }\n}\n\ncatch(err){\n}\n\nfinally{\n    return [msg0, msg1];\n}\n",
        "outputs": "2",
        "noerr": 0,
        "x": 400,
        "y": 560,
        "wires": [
            [],
            [
                "119a72d9.2fc995"
            ]
        ],
        "outputLabels": [
            "Other",
            "Sensor"
        ]
    },
    {
        "id": "119a72d9.2fc995",
        "type": "function",
        "z": "25471496.37cf34",
        "name": "Test for Emoncms Node",
        "func": "var newmsg ={};\nvar newDateObj = new Date(msg.payload.Time);\n\nnewmsg.payload = msg.payload.ENERGY;\n//newmsg.payload.time = msg.payload.Time; // as string\nnewmsg.payload.time = newDateObj.getTime()/1000; // Seconds since Epoch\n\nreturn newmsg;",
        "outputs": 1,
        "noerr": 0,
        "x": 710,
        "y": 560,
        "wires": [
            [
                "9d56c868.e2f568"
            ]
        ]
    },
    {
        "id": "a1885387.15bd3",
        "type": "mqtt-broker",
        "z": "",
        "broker": "localhost",
        "port": "1883",
        "clientid": "",
        "usetls": false,
        "compatmode": true,
        "keepalive": "60",
        "cleansession": true,
        "willTopic": "",
        "willQos": "0",
        "willPayload": "",
        "birthTopic": "",
        "birthQos": "0",
        "birthPayload": ""
    }
]
1 Like
5

Thank you very much for your answer. I’m running a MQTT broker and wouldn’t want to deploy a third instance parsing and republishing MQTT messages.
The best would be to have the Sonoff-Tasmota firmware send JSON-format that your phpmqtt_input.php can interpret properly.

6

You don’t need another broker. Just get the Sonoff to publish to a ‘sonoff’ topic which comes into Nodered and send the output to an ‘emon’ topic which Emoncms picks up. Can all be done on the same broker.

If all you want to do is read the data sure. if you want to also control the switch then passing the initial output to Nodered is the logical solution.

Of course you could use the Emoncms node which uses the http input API.
[Edit] If you did do this just change the output to msg.time instead of msg.payload.time.

7

I’m pretty new to emoncms and I do have an emoncms with configured MQTT installed. My Sonoffs are pointed to my own MQTT broker. I was not planning to control the sonoffs.
I installed the above tools onto an existing server of mine, I did not use emonpi or anything similar.

8

So do you have Nodered instance somewhere? If it can listen to the Broker and therefore get the Sonoff data, use the emoncms Nodered node and push the data via the http API.

9

No, I don’t. But I’m just writing a sort of gateway that reformats the JSON and republishes it for emoncms. For tests this should be enough. I’ll have a look into Sonoff and have it send propper JSON.

10

For every message sent I get the below error in emoncms.log:

2018-02-07 13:02:44.198|ERROR|phpmqtt_input.php|MQTT JSON Input result: 1

The JSON I’m sending with MQTT:
{ "Power": 0, "Time": "2018-02-07T14:10:33.000Z", "Yesterday": 2.048, "Period": 0, "Current": 0.0, "Voltage": 227, "Factor": 0.0, "Total": 3.829, "Today": 0.001 }

11

time is lowercase :smile:

Not sure why you get that error though :frowning:

[Edit]That message simply means the input worked fine. Did the ‘time’ get set correctly? You may not notice it and if it was not lower case, I doubt it did.

12

This has made me think.

I was considering deleting the time element out of the input if it was used. I think a better way is to log whether the time is set (and to what) via the data element or else simply by system time.

That ‘error’ should also be a ‘log’ event rather than an error I feel (not code I changed).

I’ll add these to the PR and hopefully @TrystanLea will get a chance to look at it :smile:

[edit] I think I’ll also make the key not case sensitive.

13

I changed it to lowercase, but it didn’t help. What I can see is that the error message has an offset of 1 hour. Altough I’ve checked timezone entries all over, php, emoncms etc.

14

Which of the 2 times are correct? Silly question, but is your system time correct? Is the local TZ correct?

Do you mean the time of the data is not correct? I tested it locally with 2 inputs to the same Emoncms instance, one with the date/time offset by 10s and the INPUT page showed the 2 sets of inputs being 10s apart (in update time).

I would expect Emoncms to be unhappy if you were trying to input data with a future date/time set.

If you set debug level to INFO, you will see some additional info including the actual data Emoncms is receiving.

15

The time shown in the MQTT payload is correct. Below is a small output of emoncms.log and you can see that the time show in the log differs by one hour. I’ve checked system time and TZ, and those are fine.

I’ve highlighted the time that is wrong below.

2018-02-07 14:50:04.274|INFO|phpmqtt_input.php|MQTT Valid JSON found {“Power”: 0, “time”: “2018-02-07T15:50:02.000Z”, “Yesterday”: 2.048, “Period”: 0, “Current”: 0.0, “Voltage”: 228, “Factor”: 0.0, “Total”: 3.829, “Today”: 0.001}
2018-02-07 14:50:04.274|INFO|phpmqtt_input.php|basement/washer {“Power”: 0, “time”: “2018-02-07T15:50:02.000Z”, “Yesterday”: 2.048, “Period”: 0, “Current”: 0.0, “Voltage”: 228, “Factor”: 0.0, “Total”: 3.829, “Today”: 0.001}
2018-02-07 14:50:04.278|INFO|feed_model.php|insert_data() feedid=6 updatetime=1518018602 feedtime=1518018602 value=0 arg=
2018-02-07 14:50:04.278|INFO|PHPFina.php|post() id=6 timestamp=1518018602 value=0 padding=
2018-02-07 14:50:04.278|INFO|feed_model.php|insert_data() feedid=5 updatetime=1518018602 feedtime=1518018602 value=228 arg=
2018-02-07 14:50:04.279|INFO|PHPFina.php|post() id=5 timestamp=1518018602 value=228 padding=
2018-02-07 14:50:04.279|ERROR|phpmqtt_input.php|MQTT JSON Input result: 1

16

So the Log time is an hour out? This time will (AFAIK) be UTC as that is what Emoncms uses internally.

Are you in Europe on CET? The SONOFF time is reported as Zulu which is == UTC. If you are in Europe, you are actually on +1 Alpha so it is your timezone in the SONOFF that is incorrect.

17

Exactly, the Log time is an hour off. Maybe that has nothing todo with the problem at all. I’m in Europe/Berlin TZ.
I’ll ignore the ERROR in the logs for the moment. The values are shown in Emoncms.

Is your pull request accepted and is it going to be included in emoncms master?

What I do for the moment is using a small python program I’ve written that subscribes to the sensor data of the sonoff, reformats the json and republishes the data that emoncms mqtt can accept. I would love to remove that small tool but I’ll have to get sonoff to send proper formated json that emoncms can accept.

18

Looking at this instance in time - 15:40 local CET :smile:

The log time is correct as that time is UTC time (CET -1).

The SONOFF time is off as it is stated as 15:40Z when it should be 15:40+1 which is where you are an hour out. The Timezone on the Sonoff is incorrect.

Waiting on @TrystanLea to review it.

I always find these things easier to do in Nodered but YMMV.

19

You use the tools you know, Python is much easier for me to write and handle, also my particular aim is to get rid of that part doing transforming of the JSON.

I would really like to see your PR be accepted, it looks clean and works great for me.

20

Apologies @borpin and thanks for the pull request, I’ve posted a couple of comments on the pull request here Update to phpmqtt_input to accept JSON object by borpin · Pull Request #776 · emoncms/emoncms · GitHub.