Every 30 minutes I grab my fuel usage from Octopus energy; and shove it on a chart.
Chrome kept crashing with memory errors; which I realised was because Octopus currently only publish new data around midnight, but every 30 mins I was downloading the previous weeks data; so the chart ended up with loads of invisible, duplicate records.
Now I shove the data into mySQL (which silently ignores duplicate data); then select the last 7 days of data from mySQL to chart. I send the whole chart worth in one hit. Alas, the time axis shows all data as happening in the last 10 minutes. I can dump the input and output from the chart, and both look good, with a week of dates (in milliseconds) as x values. Below flow reproduces the problem:
[{"id":"bf3262a1.0d588","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"6ba626c.2edf2d8","type":"function","z":"bf3262a1.0d588","name":"Data","func":"msg = {\n\t\"topic\": \"E\",\n\t\"payload\": [\n\t\t{\n\t\t\t\"data\": [\n\t\t\t\t[\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625646600,\n\t\t\t\t\t\t\"y\": 0.126\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625648400,\n\t\t\t\t\t\t\"y\": 0.094\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625650200,\n\t\t\t\t\t\t\"y\": 0.035\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625652000,\n\t\t\t\t\t\t\"y\": 0.031\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625653800,\n\t\t\t\t\t\t\"y\": 0.019\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625655600,\n\t\t\t\t\t\t\"y\": 0.054\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625657400,\n\t\t\t\t\t\t\"y\": 0.2\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625659200,\n\t\t\t\t\t\t\"y\": 0.076\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625661000,\n\t\t\t\t\t\t\"y\": 0.166\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625662800,\n\t\t\t\t\t\t\"y\": 0.004\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625664600,\n\t\t\t\t\t\t\"y\": 0.053\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625666400,\n\t\t\t\t\t\t\"y\": 0.025\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625668200,\n\t\t\t\t\t\t\"y\": 0.024\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625670000,\n\t\t\t\t\t\t\"y\": 0\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625671800,\n\t\t\t\t\t\t\"y\": 0.002\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625673600,\n\t\t\t\t\t\t\"y\": 0\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625675400,\n\t\t\t\t\t\t\"y\": 0.591\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625677200,\n\t\t\t\t\t\t\"y\": 0.679\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625679000,\n\t\t\t\t\t\t\"y\": 0.638\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625680800,\n\t\t\t\t\t\t\"y\": 0.617\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625682600,\n\t\t\t\t\t\t\"y\": 1.035\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625684400,\n\t\t\t\t\t\t\"y\": 0.727\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625686200,\n\t\t\t\t\t\t\"y\": 0.357\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625688000,\n\t\t\t\t\t\t\"y\": 0.411\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625689800,\n\t\t\t\t\t\t\"y\": 0.412\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625691600,\n\t\t\t\t\t\t\"y\": 0.462\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625693400,\n\t\t\t\t\t\t\"y\": 0.471\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625695200,\n\t\t\t\t\t\t\"y\": 0.496\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625697000,\n\t\t\t\t\t\t\"y\": 0.467\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625698800,\n\t\t\t\t\t\t\"y\": 0.407\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625700600,\n\t\t\t\t\t\t\"y\": 0.27\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625702400,\n\t\t\t\t\t\t\"y\": 0.272\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625704200,\n\t\t\t\t\t\t\"y\": 1.262\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625706000,\n\t\t\t\t\t\t\"y\": 4.41\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625707800,\n\t\t\t\t\t\t\"y\": 4.246\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625709600,\n\t\t\t\t\t\t\"y\": 4.046\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625711400,\n\t\t\t\t\t\t\"y\": 1.244\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625713200,\n\t\t\t\t\t\t\"y\": 0.666\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625715000,\n\t\t\t\t\t\t\"y\": 0.351\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625716800,\n\t\t\t\t\t\t\"y\": 0.237\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625718600,\n\t\t\t\t\t\t\"y\": 0.215\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625720400,\n\t\t\t\t\t\t\"y\": 0.194\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625722200,\n\t\t\t\t\t\t\"y\": 0.193\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625724000,\n\t\t\t\t\t\t\"y\": 0.179\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625725800,\n\t\t\t\t\t\t\"y\": 0.17\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625727600,\n\t\t\t\t\t\t\"y\": 0.127\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625729400,\n\t\t\t\t\t\t\"y\": 0.153\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625731200,\n\t\t\t\t\t\t\"y\": 0.167\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625733000,\n\t\t\t\t\t\t\"y\": 0.111\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625734800,\n\t\t\t\t\t\t\"y\": 0.043\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625736600,\n\t\t\t\t\t\t\"y\": 0.009\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625738400,\n\t\t\t\t\t\t\"y\": 0.099\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625740200,\n\t\t\t\t\t\t\"y\": 0.081\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625742000,\n\t\t\t\t\t\t\"y\": 0.123\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625743800,\n\t\t\t\t\t\t\"y\": 0.112\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625745600,\n\t\t\t\t\t\t\"y\": 0.063\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625747400,\n\t\t\t\t\t\t\"y\": 0.214\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625749200,\n\t\t\t\t\t\t\"y\": 0.145\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625751000,\n\t\t\t\t\t\t\"y\": 0.124\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625752800,\n\t\t\t\t\t\t\"y\": 0.013\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625754600,\n\t\t\t\t\t\t\"y\": 0.009\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625756400,\n\t\t\t\t\t\t\"y\": 0.011\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625758200,\n\t\t\t\t\t\t\"y\": 0.023\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625760000,\n\t\t\t\t\t\t\"y\": 0.144\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625761800,\n\t\t\t\t\t\t\"y\": 0.123\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625763600,\n\t\t\t\t\t\t\"y\": 0.244\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625765400,\n\t\t\t\t\t\t\"y\": 0.214\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625767200,\n\t\t\t\t\t\t\"y\": 0.179\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625769000,\n\t\t\t\t\t\t\"y\": 0.209\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625770800,\n\t\t\t\t\t\t\"y\": 0.3\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625772600,\n\t\t\t\t\t\t\"y\": 0.389\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625774400,\n\t\t\t\t\t\t\"y\": 0.417\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625776200,\n\t\t\t\t\t\t\"y\": 0.618\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625778000,\n\t\t\t\t\t\t\"y\": 0.459\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625779800,\n\t\t\t\t\t\t\"y\": 0.427\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625781600,\n\t\t\t\t\t\t\"y\": 0.535\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625783400,\n\t\t\t\t\t\t\"y\": 0.614\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625785200,\n\t\t\t\t\t\t\"y\": 0.485\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625787000,\n\t\t\t\t\t\t\"y\": 0.348\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625788800,\n\t\t\t\t\t\t\"y\": 0.362\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625790600,\n\t\t\t\t\t\t\"y\": 1.33\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625792400,\n\t\t\t\t\t\t\"y\": 4.538\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625794200,\n\t\t\t\t\t\t\"y\": 1.406\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625796000,\n\t\t\t\t\t\t\"y\": 0.3\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625797800,\n\t\t\t\t\t\t\"y\": 0.426\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625799600,\n\t\t\t\t\t\t\"y\": 0.596\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625801400,\n\t\t\t\t\t\t\"y\": 0.207\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625803200,\n\t\t\t\t\t\t\"y\": 0.213\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625805000,\n\t\t\t\t\t\t\"y\": 0.237\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625806800,\n\t\t\t\t\t\t\"y\": 0.237\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625808600,\n\t\t\t\t\t\t\"y\": 0.23\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625810400,\n\t\t\t\t\t\t\"y\": 0.196\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625812200,\n\t\t\t\t\t\t\"y\": 0.15\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625814000,\n\t\t\t\t\t\t\"y\": 0.082\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625815800,\n\t\t\t\t\t\t\"y\": 0.017\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625817600,\n\t\t\t\t\t\t\"y\": 0.029\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625819400,\n\t\t\t\t\t\t\"y\": 0.039\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625821200,\n\t\t\t\t\t\t\"y\": 0.066\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625823000,\n\t\t\t\t\t\t\"y\": 0.039\n\t\t\t\t\t},\n\t\t\t\t\t{\n\t\t\t\t\t\t\"x\": 1625824800,\n\t\t\t\t\t\t\"y\": 0.012\n\t\t\t\t\t}\n\t\t\t\t]\n\t\t\t],\n\t\t\t\"series\": [\n\t\t\t\t\"E\"\n\t\t\t],\n\t\t\t\"labels\": [\n\t\t\t\t\"\"\n\t\t\t]\n\n\t\t}\n\t],\n\t\"_msgid\": \"c728d5cf.813748\"\n}\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":270,"y":100,"wires":[["afcc73d8.ce718"]]},{"id":"4f791804.180738","type":"inject","z":"bf3262a1.0d588","name":"Tickle","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":110,"y":100,"wires":[["6ba626c.2edf2d8"]]},{"id":"afcc73d8.ce718","type":"ui_chart","z":"bf3262a1.0d588","name":"Forgetful Octopus","group":"61d18f84.7e0c","order":10,"width":0,"height":0,"label":"Forgetful Octopus","chartType":"line","legend":"false","xformat":"auto","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"7","removeOlderPoints":"","removeOlderUnit":"86400","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"x":490,"y":100,"wires":[[]]},{"id":"61d18f84.7e0c","type":"ui_group","name":"Small Graphs","tab":"21579b6f.248774","order":3,"disp":false,"width":"6","collapse":false},{"id":"21579b6f.248774","type":"ui_tab","name":"Home Power","icon":"dashboard","order":1,"disabled":false,"hidden":false}]
My actual flow, (having stored the data in mysql) has function node which gets msg.service of either "G" or "E" (Gas or Elec) of:
msg.topic = "select unix_timestamp(interval_start) as x, consumption as y from Octopus where service = '" +
msg.service + "' and interval_start > CURRENT_TIMESTAMP - INTERVAL '7' day order by interval_start limit 100; "
return msg;
This pops into mysql; then the result goes through another function node containing:
var msg1 = {topic:msg.service, payload:[{data:[msg.payload],series:[msg.service],labels:[""]}]};
if (msg.service==="G"){
return [msg1,null];
}else{
return [null,msg1];
}
I know I could use a switch node, but I love a bit of hard-coding.
So, please can someone explain why my Octopus X axis is only 10 minutes long!
The data squiggly line looks fine, it's just the axis is wrong. Memory of a goldfish!!
