State trail problem

[olab]

I'm trying to make the state trail work based on planned task data from my MSSQL server, but it won't.

I cant find any error in my code. Can someone help?

[{"id":"3b0e58f2.0a12b8","type":"MSSQL","z":"9a177fb4.e8284","mssqlCN":"c19a071f.c312d8","name":"","query":"SELECT\nDATEPART(YEAR, dtPlannedStart) As sYear,\nDATEPART(MONTH, dtPlannedStart)-1 As sMonth,\nDATEPART(DAY, dtPlannedStart) As sDate,\nDATEPART(HOUR, dtPlannedStart) As sHour,\nDATEPART(MINUTE, dtPlannedStart) As sMinute,\nDATEPART(SECOND, dtPlannedStart) As sSecond,\n\nDATEPART(YEAR, dtPlannedEnd) As eYear,\nDATEPART(MONTH, dtPlannedEnd)-1 As eMonth,\nDATEPART(DAY, dtPlannedEnd) As eDate,\nDATEPART(HOUR, dtPlannedEnd) As eHour,\nDATEPART(MINUTE, dtPlannedEnd) As eMinute,\nDATEPART(SECOND, dtPlannedEnd) As eSecond,\nplannedstart,\nplannedend\n\n\n\nFROM [TMBrumunddal].[dbo].[v_ProductionProgress] where ActualStart is null and Line in('PS 120')\norder by dtPlannedStart","outField":"payload","returnType":0,"throwErrors":1,"x":360,"y":180,"wires":[["4733b304.1c0f7c"]]},{"id":"29192e0e.bc05e2","type":"inject","z":"9a177fb4.e8284","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":180,"y":180,"wires":[["3b0e58f2.0a12b8"]]},{"id":"5c7d0e72.fa3c1","type":"debug","z":"9a177fb4.e8284","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1010,"y":220,"wires":[]},{"id":"4733b304.1c0f7c","type":"function","z":"9a177fb4.e8284","name":"","func":"var data=[];\nvar sdate;\nvar edate;\n\n//loop through rows in SQL data\nfor(var i = 0; i < msg.payload.length; i++){\n    \n  sdate = new Date(\n    msg.payload[i].sYear,\n    msg.payload[i].sMonth,\n    msg.payload[i].sDate,\n    msg.payload[i].sHour,\n    msg.payload[i].sMinute,\n    0,\n    0);\n  \n \n        \n    edate =  new Date(\n    msg.payload[i].eYear,\n    msg.payload[i].eMonth,\n    msg.payload[i].eDate,\n    msg.payload[i].eHour,\n    msg.payload[i].eMinute,\n    0,\n    0);\n    \n  data.push({state:true,timestamp:sdate.getTime()});\n  data.push({state:false,timestamp:edate.getTime()});\n}\n\n\n\nmsg.payload = JSON.stringify(data);\nreturn msg;","outputs":1,"noerr":0,"x":550,"y":180,"wires":[["11964daa.091382"]]},{"id":"11964daa.091382","type":"change","z":"9a177fb4.e8284","name":"","rules":[{"t":"delete","p":"query","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":730,"y":180,"wires":[["c4062f7a.ebf38","5c7d0e72.fa3c1"]]},{"id":"c4062f7a.ebf38","type":"ui_statetrail","z":"9a177fb4.e8284","group":"cee6ab9.ba30558","order":0,"width":0,"height":0,"name":"","label":"","states":[{"state":true,"col":"#009933","t":"bool","label":""},{"state":false,"col":"#999999","t":"bool","label":""}],"periodLimit":1,"periodLimitUnit":"3600","timeformat":"HH:mm:ss","tickmarks":4,"persist":false,"legend":1,"combine":true,"blanklabel":"","x":1020,"y":180,"wires":[[]]},{"id":"c19a071f.c312d8","type":"MSSQL-CN","z":"","tdsVersion":"7_4","name":"DB141004","server":"192.168.150.17","port":"1433","encyption":false,"database":"TMBrumunddal","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5"},{"id":"cee6ab9.ba30558","type":"ui_group","z":"","name":"Group 1","tab":"618a128a.ebdb4c","order":1,"disp":false,"width":"23","collapse":false},{"id":"618a128a.ebdb4c","type":"ui_tab","z":"","name":"Home","icon":"dashboard","disabled":false,"hidden":false}]

[hotNipi]

Devide timestamps with 1000

[olab]

Sorry, that didn't help.

Could it be someting about timestamps is in the future?

[Paul-Reed]

Your timestamp format looks the same as mine;

{
"state": 1,
"timestamp": 1583651894208
},
{
"state": 2,
"timestamp": 1583653409178
},
{
"state": 1,
"timestamp": 1583653414181
},

@olab

Could it be someting about timestamps is in the future?

If you are inputting timestamps for some time in the future, the node config makes it clear that it only handles data from present time going backwards.
i.e. Period last % seconds/minutes/hours
So future timestamps would not be processed.

[hotNipi]

This is something I haven't tried so can't even comment. If it's not working with future timestamps, it's not intentional restriction.

I'm still on the no computers weekend so can't check immediately

[hotNipi]

Phones are powerful. Reading the code on github tells me that yes, future timestamp not supported. Will be supported tomorrow.

[olab]

That is awsome!!

[hotNipi]

Tomorrow is today and future is supported since now
Version 0.2.12 available

[olab]

Tank you, but it will still not work. It looks to me that the data fed into the node is correct. Timestamps has dates through the next week.

![Capture1|329x168]

[hotNipi]

Your msg.payload is string

It needs to be an array of objects.

[olab]

Yess!

That solved my display problem bout would it be possible to get the time format as YYYY-MM-DD hh:mm or local time. Since my data stretches over a whole week it get a bit unreadable....

[hotNipi]

It is possible, but as you can see, those strings will be pretty long so you can use less tickmarks and the left and right fading areas will be wider and active area therefore smaller. There must be some strategy figured out how to manage layout for long time marks. I don't have it right now and it may take some time.