Date time input from Dashboard - how to?

[hazymat]

I need to be able to input a date and time in the Dashboard, and output the result in a message. So I have added two dashboard nodes (Date and Text, set to time picker), which output to flow context, then I can read them from a function node, when I press a submit button:

I understand that Date returns the date in milliseconds since 1/1/1970. And I understand that the time picker returns the time in milliseconds since midnight.

So I thought I could just add them together then output this to InfluxDB:

datetime = new Date(msg.time+msg.date)
msg.payload.time = datetime

However in Grafana, the time shows as one hour later than it should. I guess this may be related to daylight saving as we are currently in BST (British Summer Time)

What have I done wrong? How do I get the correct date / time to show?

[TotallyInformation]

You have probably entered the data in BST but InfluxDB will assume that you've used UTC (AKA GMT or Zulu time). And InfluxDB would be right to assume that. since you should always store date/time values in UTC to prevent timezone and daylight savings issues.

Either use the moment node or the MomentJS feature now included in JSONata to do the conversion if it needs to be converted to UTC or do the correction in Grafana otherwise.

[hazymat]

Thanks, this looks like a good solution. I have installed the moment node, might you be able to help me understand what to enter into the various boxes on here, although I've read the sidebar help I still can't work it out.

Based on what you have explained, I should be setting the output Tz to UTC, and when I try doing this, it changes immediately to ETC/GMT. But the time that is spewed out is the same as the input.

[Colin]

@hazymat

But the time that is spewed out is the same as the input.

That may be to do with how you are displaying it. Remember that changing the time zone does not change the time, 12:00 UTC is the same time as 13:00 UTC+1

However, for influx, you would be better just to feed it the value in milliseconds rather than have the possible confusion of converting it to a Date and back again to ticks. If you are using the usual Influx nodes then in the Advanced settings if I remember correctly) there is a setting to say that the value is in milliseconds.

A further thing to check, in Grafana, when you ask for Last 3 Hours, or similar, does it show the correct Now time at the right hand side of the time axis?

Show us what you are feeding into the node and what is coming out.

[hazymat]

Thanks Colin, quick follow-up question

@Colin

However, for influx, you would be better just to feed it the value in milliseconds rather than have the possible confusion of converting it to a Date and back again to ticks

Problem with that idea is that I need to collect the date and time from Dashboard. The time picker (in text input) sends back milliseconds since midnight, which I add to the number of milliseconds provided by the date picker. So let's say I selected a date and time during winter (UTC) then it would need to know not to add the hour, but in summer (UTC+1) it would need to add that hour.

Hence thinking I would need to feed milliseconds into a date-aware thing like momentJS.

@Colin

If I remember correctly there is a setting to say that the value is in milliseconds.

Yes there's the ability to set "Time Precision" in Influx node

@Colin

when you ask for Last 3 Hours, or similar, does it show the correct Now time at the right hand side of the time axis

Yes it does, I feed InfluxDB from a number of other sources in NodeRED and it works well, UTC is inserted to the database and Grafana corrects for BST.

Here's what I'm sending in:

if (msg.time  != 'undefined' && msg.date != 'undefined') {
    datetime = msg.time+msg.date
    msg.payload.time = new Date(datetime)
}

(msg.time and msg.date are fed by a submit button which reads from the Dashboard text node (set to time picker) and the Dashboard date picker node.

Hope that makes sense

Any clues on how to make this work, could be using MomentJS or any other way

[Colin]

Can you post the flow you use to get the date/time and combine them?

[hazymat]

Hi, yes here it is:

[{"id":"44544537.178a5c","type":"ui_dropdown","z":"53541a30.e1abb4","name":"Bromine Reading","label":"","tooltip":"","place":"Bromine","group":"3ac4cc84.a53204","order":1,"width":"3","height":"1","passthru":true,"options":[{"label":"0 (Low)","value":0,"type":"num"},{"label":"1 (Low)","value":1,"type":"num"},{"label":"2 (OK)","value":2,"type":"num"},{"label":"5 (OK)","value":5,"type":"num"},{"label":"10 (High)","value":10,"type":"num"},{"label":"20 (Very High)","value":20,"type":"num"}],"payload":"","topic":"","x":630,"y":1060,"wires":[["4b7f4861.0a1048"]]},{"id":"6e89ccb3.a15a24","type":"ui_dropdown","z":"53541a30.e1abb4","name":"pH Reading","label":"","tooltip":"","place":"pH","group":"3ac4cc84.a53204","order":2,"width":"3","height":"1","passthru":true,"options":[{"label":"6.2 (Low / Acidic)","value":6.2,"type":"num"},{"label":"6.8 (Low / Acidic)","value":6.8,"type":"num"},{"label":"7.2 (OK)","value":7.2,"type":"num"},{"label":"7.6 (OK)","value":7.6,"type":"num"},{"label":"7.8 (High / Alkaline)","value":7.8,"type":"num"},{"label":"8.4 (Very High / Alkaline)","value":8.4,"type":"num"}],"payload":"","topic":"","x":610,"y":1100,"wires":[["ef5f3d93.a9a16"]]},{"id":"c87a87c5.85bbd8","type":"ui_dropdown","z":"53541a30.e1abb4","name":"Alkalinity Reading","label":"","tooltip":"","place":"Alkalinity","group":"3ac4cc84.a53204","order":3,"width":"3","height":"1","passthru":true,"options":[{"label":"0 (Low)","value":0,"type":"num"},{"label":"40 (Low)","value":40,"type":"num"},{"label":"80 (OK)","value":80,"type":"num"},{"label":"120 (OK)","value":120,"type":"num"},{"label":"180 (High)","value":180,"type":"num"},{"label":"240 (High)","value":240,"type":"num"}],"payload":"","topic":"","x":630,"y":1140,"wires":[["c1abdc0.7e57a28"]]},{"id":"190e2d80.e15c53","type":"ui_button","z":"53541a30.e1abb4","name":"","group":"3ac4cc84.a53204","order":6,"width":"3","height":"1","passthru":false,"label":"Submit","tooltip":"","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"","x":100,"y":1220,"wires":[["1e02e39d.4c1a2c"]]},{"id":"4b7f4861.0a1048","type":"change","z":"53541a30.e1abb4","name":"","rules":[{"t":"set","p":"tub.bromine","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":840,"y":1060,"wires":[[]]},{"id":"ef5f3d93.a9a16","type":"change","z":"53541a30.e1abb4","name":"","rules":[{"t":"set","p":"tub.ph","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":820,"y":1100,"wires":[[]]},{"id":"c1abdc0.7e57a28","type":"change","z":"53541a30.e1abb4","name":"","rules":[{"t":"set","p":"tub.alkalinity","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":840,"y":1140,"wires":[[]]},{"id":"1e02e39d.4c1a2c","type":"function","z":"53541a30.e1abb4","name":"New hot tub reading","func":"// Save new hot tub reading entered in Dashboard to global context\n// Includes form validation\n// and supports clearing the form when done correctly\n\n// check form was filled in properly\nif (typeof flow.get(\"tub\") == 'undefined') {\n    msg.payload = \"You didn't enter anything\"\n    msg.error = true\n    return [msg, null]\n}\n\n// assume tub object exists (some part of form was filled)\nvar newTubReading = flow.get(\"tub\")\n\n// if form not complete\nif (typeof newTubReading.bromine == 'undefined' ||\n           newTubReading.bromine === \"\" ||\n    typeof newTubReading.ph == 'undefined' ||\n           newTubReading.ph === \"\" ||\n    typeof newTubReading.alkalinity == 'undefined' ||\n           newTubReading.alkalinity === \"\") {\n    msg.payload = \"Missing info - reading not saved\"\n    msg.error = true\n    return [msg, null]\n}\n\n// if we've got this far,\n\nmsg.payload = \"Success - added a reading\"\nmsg.error = false\nflow.set (\"tub\", undefined)\n\n\nreturn [msg, newTubReading]\n","outputs":2,"noerr":0,"initialize":"","finalize":"","x":300,"y":1220,"wires":[["7e09a7e0.2d7168","d7f1d2a7.04e6"],["51fdc78e.1cbe28","b88d2174.4922e"]]},{"id":"7e09a7e0.2d7168","type":"function","z":"53541a30.e1abb4","name":"Success","func":"// Save new reading: clear form & result if successful\n\nif (!msg.error) {\n    msg.payload = \"\"\n    return msg;\n}\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":400,"y":1160,"wires":[["44544537.178a5c","6e89ccb3.a15a24","c87a87c5.85bbd8","ccf753ed.c9217","2f030e6c.ab41a2","c725ff69.3e60e"]]},{"id":"ccf753ed.c9217","type":"delay","z":"53541a30.e1abb4","name":"","pauseType":"delay","timeout":"5","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":600,"y":1260,"wires":[["d7f1d2a7.04e6"]]},{"id":"d7f1d2a7.04e6","type":"ui_template","z":"53541a30.e1abb4","group":"3ac4cc84.a53204","name":"New tub reading validation","order":7,"width":"6","height":1,"format":"{{msg.payload}}","storeOutMessages":true,"fwdInMessages":false,"templateScope":"local","x":820,"y":1260,"wires":[[]]},{"id":"51fdc78e.1cbe28","type":"link out","z":"53541a30.e1abb4","name":"Output from manual hot tub reading","links":["9962bd21.2f458","bc4212ca.b96f4"],"x":455,"y":1260,"wires":[]},{"id":"9a9746db.423b28","type":"comment","z":"53541a30.e1abb4","name":"Ideal spec for data logging system (hot tub)","info":"\n- Build for generic use\n\n- Ability to input readings into Dashboard, hit submit, and they are saved to InfluxDB or similar (I've already done this in Node-RED)\n\n- Ability to add a date / time to the above, to backdate readings\n\n- Ability to show existing records in InfluxDB, with the option of deleting individual records if they were incorrect, or for tidying up data\n\n- Ability to show existing records within a given timeframe\n\n- Ability to delete data from within a given timeframe (e.g. sensor was malfunctioning for 2 days...)\n\n- Advanced spec: adjust up or down a set of readings between timeframes, downsample data, etc.\n","x":330,"y":1040,"wires":[]},{"id":"b88d2174.4922e","type":"debug","z":"53541a30.e1abb4","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":730,"y":1340,"wires":[]},{"id":"bc4212ca.b96f4","type":"link in","z":"53541a30.e1abb4","name":"","links":["51fdc78e.1cbe28","678b70d.e41889"],"x":215,"y":1420,"wires":[["5bd6fbad.653384"]]},{"id":"d236ec40.5f3ce","type":"comment","z":"53541a30.e1abb4","name":"Hot tub readings","info":"","x":180,"y":1400,"wires":[]},{"id":"5f80d464.88baec","type":"influxdb out","z":"53541a30.e1abb4","influxdb":"9d9ad3f4.49ce7","name":"","measurement":"hot_tub","precision":"","retentionPolicy":"","x":790,"y":1420,"wires":[]},{"id":"5bd6fbad.653384","type":"function","z":"53541a30.e1abb4","name":"","func":"\nbromine = msg.bromine\nph = msg.ph\nalkalinity = msg.alkalinity\ntimestamp = new Date(1564128384000)\nmsg.payload = {\n    bromine: bromine,\n    ph: ph,\n    alkalinity: alkalinity,\n}\n\nif (msg.time  != 'undefined' && msg.date != 'undefined') {\n    datetime = msg.time+msg.date\n    msg.payload.time = new Date(datetime)\n}\n\n\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":370,"y":1420,"wires":[["5f80d464.88baec","4cf09692.008628"]]},{"id":"4cf09692.008628","type":"debug","z":"53541a30.e1abb4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":550,"y":1480,"wires":[]},{"id":"16394282.598f2d","type":"change","z":"53541a30.e1abb4","name":"","rules":[{"t":"set","p":"tub.time","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":820,"y":1180,"wires":[[]]},{"id":"2f030e6c.ab41a2","type":"ui_text_input","z":"53541a30.e1abb4","name":"","label":"","tooltip":"","group":"3ac4cc84.a53204","order":5,"width":0,"height":0,"passthru":true,"mode":"time","delay":300,"topic":"","x":600,"y":1180,"wires":[["16394282.598f2d"]]},{"id":"c725ff69.3e60e","type":"ui_date_picker","z":"53541a30.e1abb4","name":"","label":"date","group":"3ac4cc84.a53204","order":6,"width":0,"height":0,"passthru":true,"topic":"","x":590,"y":1220,"wires":[["ed4256ef.3ee2e8"]]},{"id":"ed4256ef.3ee2e8","type":"change","z":"53541a30.e1abb4","name":"","rules":[{"t":"set","p":"tub.date","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":820,"y":1220,"wires":[[]]},{"id":"3ac4cc84.a53204","type":"ui_group","z":"","name":"Hot Tub","tab":"484568b1.0b3ae8","order":2,"disp":true,"width":"6","collapse":false},{"id":"9d9ad3f4.49ce7","type":"influxdb","z":"","hostname":"127.0.0.1","port":"8086","protocol":"http","database":"nodered","name":"","usetls":false,"tls":""},{"id":"484568b1.0b3ae8","type":"ui_tab","z":"","name":"Garden","icon":"dashboard","disabled":false,"hidden":false}]```

[Colin]

I have just remembered I have a flow that does almost exactly the same thing for a tab that is used for saving blood pressure into an influx db, so you might like to look at that. One of the issues with the Date picker is (or was, it may have been fixed) that it sometimes returns an apparently random time offset into the day, I think is is related to when the node is deployed or something similar. The flow allows for that and in addition preloads the fields with the current date/time when the tab is selected. In addition it has an ok/cancel popup to allow confirmation before committing to the database.

[{"id":"3ba66233.9f3d6e","type":"tab","label":"Health","disabled":false,"info":""},{"id":"d446800d.4fc24","type":"ui_form","z":"3ba66233.9f3d6e","name":"Systolic Diastolic Rate","label":"","group":"fe4030b8.44e0d8","order":6,"width":0,"height":0,"options":[{"label":"Systolic","value":"systolic","type":"number","required":true,"rows":null},{"label":"Diastolic","value":"diastolic","type":"number","required":true,"rows":null},{"label":"Rate","value":"rate","type":"number","required":true,"rows":null}],"formValue":{"systolic":"","diastolic":"","rate":""},"payload":"","submit":"submit","cancel":"cancel","topic":"form","x":340,"y":200,"wires":[["241cc302.57b924"]]},{"id":"9870001d.cc78b","type":"ui_dropdown","z":"3ba66233.9f3d6e","name":"","label":"Name","tooltip":"","place":"Select option","group":"fe4030b8.44e0d8","order":1,"width":0,"height":0,"passthru":false,"multiple":false,"options":[{"label":"","value":"Someone","type":"str"},{"label":"","value":"Someone else","type":"str"}],"payload":"","topic":"name","x":130,"y":40,"wires":[["241cc302.57b924"]]},{"id":"aaeab7d4.b4df9","type":"ui_text_input","z":"3ba66233.9f3d6e","name":"","label":"Time","tooltip":"","group":"fe4030b8.44e0d8","order":5,"width":5,"height":1,"passthru":true,"mode":"time","delay":"10","topic":"time","x":200,"y":150,"wires":[["241cc302.57b924"]]},{"id":"241cc302.57b924","type":"join","z":"3ba66233.9f3d6e","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":true,"timeout":"","count":"1","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":390,"y":120,"wires":[["a1b92a73.e52418"]]},{"id":"5154c1bd.045ee","type":"function","z":"3ba66233.9f3d6e","name":"Format for influx","func":"// calc timestamp\nlet timestamp  = new Date(msg.payloadSaved.date + msg.payloadSaved.time + (new Date()).getTimezoneOffset()*60000)\nlet fields = {\n    systolic: msg.payloadSaved.form.systolic, \n    diastolic: msg.payloadSaved.form.diastolic, \n    rate: msg.payloadSaved.form.rate\n}\nlet point = {\n    measurement: \"health\",\n    fields: fields,\n    tags: {name: msg.payloadSaved.name},\n    timestamp: timestamp\n}\nmsg.payload = [point]\n\nreturn msg;","outputs":1,"noerr":0,"x":290,"y":370,"wires":[["adf7fcd8.219028","7ba5a2c3.5dffac"]]},{"id":"adf7fcd8.219028","type":"debug","z":"3ba66233.9f3d6e","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":490,"y":440,"wires":[]},{"id":"3d25c1d7.ef381e","type":"ui_ui_control","z":"3ba66233.9f3d6e","name":"","events":"change","x":90,"y":640,"wires":[["1cc8620b.141dc6"]]},{"id":"1cc8620b.141dc6","type":"switch","z":"3ba66233.9f3d6e","name":"BP Tab?","property":"name","propertyType":"msg","rules":[{"t":"eq","v":"Blood Pressure","vt":"str"}],"checkall":"true","repair":false,"outputs":1,"x":230,"y":640,"wires":[["ee0e9d0e.9cd3b8"]]},{"id":"ee0e9d0e.9cd3b8","type":"function","z":"3ba66233.9f3d6e","name":"Time Of Day","func":"let now = new Date()\nlet msg1 = {payload: now}\nlet msg2 = {payload: (now.getTime() - now.getTimezoneOffset()*60000) % (24 * 3600 * 1000)}\nreturn [msg1, msg2];","outputs":2,"noerr":0,"initialize":"","finalize":"","x":140,"y":290,"wires":[["ca137bf3.1daf6"],["aaeab7d4.b4df9"]]},{"id":"ca137bf3.1daf6","type":"ui_date_picker","z":"3ba66233.9f3d6e","name":"","label":"","group":"fe4030b8.44e0d8","order":3,"width":5,"height":1,"passthru":true,"topic":"date","x":120,"y":90,"wires":[["62d932e6.8f478c"]]},{"id":"62d932e6.8f478c","type":"function","z":"3ba66233.9f3d6e","name":"Truncate to start of day","func":"msg.payload = Math.floor(msg.payload/(24*3600*1000))*24*3600*1000\nreturn msg;","outputs":1,"noerr":0,"x":350,"y":80,"wires":[["241cc302.57b924"]]},{"id":"a1b92a73.e52418","type":"switch","z":"3ba66233.9f3d6e","name":"Submit?","property":"topic","propertyType":"msg","rules":[{"t":"eq","v":"form","vt":"str"}],"checkall":"true","repair":false,"outputs":1,"x":550,"y":120,"wires":[["a259d4f6.31b448"]]},{"id":"7ba5a2c3.5dffac","type":"change","z":"3ba66233.9f3d6e","name":"Clear values","rules":[{"t":"set","p":"payload","pt":"msg","to":"{\"rate\":\"\",\"diastolic\":\"\",\"systolic\":\"\"}","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":450,"y":270,"wires":[["d446800d.4fc24"]]},{"id":"800df366.9efd58","type":"ui_toast","z":"3ba66233.9f3d6e","position":"dialog","displayTime":"3","highlight":"","sendall":false,"outputs":1,"ok":"Cancel","cancel":"OK","raw":false,"topic":"","name":"","x":630,"y":210,"wires":[["ededbe54.a7d8a8"]]},{"id":"a259d4f6.31b448","type":"function","z":"3ba66233.9f3d6e","name":"Prepare for OK/Cancel","func":"let saved = msg.payload\nmsg.payload = `  ${saved.name} ${saved.form.systolic}/${saved.form.diastolic}       ${saved.form.rate}`\nmsg.payloadSaved = saved\nmsg.topic = null\nreturn msg;","outputs":1,"noerr":0,"x":600,"y":160,"wires":[["800df366.9efd58"]]},{"id":"ededbe54.a7d8a8","type":"switch","z":"3ba66233.9f3d6e","name":"OK?","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"OK","vt":"str"}],"checkall":"true","repair":false,"outputs":1,"x":650,"y":260,"wires":[["5154c1bd.045ee"]]},{"id":"a997ebc1.336618","type":"comment","z":"3ba66233.9f3d6e","name":"Influx Out here","info":"","x":490,"y":360,"wires":[]},{"id":"fe4030b8.44e0d8","type":"ui_group","z":"","name":"Blood Pressure","tab":"fe876286.a57788","order":1,"disp":false,"width":"6","collapse":false},{"id":"fe876286.a57788","type":"ui_tab","z":"","name":"Blood Pressure","icon":"dashboard","disabled":false,"hidden":false}]

[Edit] I should have said, the output is formatted for the Influx Batch node. At the time I thought you couldn't send the time to the normal Influx Out node.

[hazymat]

Thanks! This is excellent, I have adapted yours for my uses and it works a treat.