Convert the time input to local time zone in node red through moment function

Isuru · 12 February 2022 10:57

Hi,

I'm trying to get the time from date picker and time input nodes and then map that timestamp to my database and retrieve a maximum value. Here I have used a format published in this forum before.

I'm getting the time inputs but I can't convert it to local time zone (UTC + 05.30 H) as the times are displayed in UTC time.

flows (1).json (4.3 KB)

This is my flow

Any help regarding this will be highly appreciated.

E1cid · 12 February 2022 11:00

try the moment tz() function

$moment($$.payload).tz("Europe/London").format("hh:mm:ss A")

https://momentjs.com/timezone/

Isuru · 12 February 2022 11:14

I have tried this but still the issue prevails. Time is receiving in UTC.

$moment($$.payload).tz("Asia/Colombo").format("hh:mm:ss A")

Colin · 12 February 2022 11:25

Please

In order to make the flow importable it is necessary to surround your code with three backticks (also known as a left quote or backquote ```)

``` 
   code goes here 
```

You can edit and correct your post by clicking the pencil icon.

See this post for more details - How to share code or flow json

In the meantime, provided the timestamps in the database are in UTC (as they should be) then you should not need to do any conversion.

E1cid · 12 February 2022 11:42

Works here you must be doing something wrong post an example of it not working
e.g.

[{"id":"17838719.4d97c9","type":"inject","z":"c791cbc0.84f648","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":140,"y":4180,"wires":[["8955f012.e6a028","280c7342.baca6c"]]},{"id":"8955f012.e6a028","type":"change","z":"c791cbc0.84f648","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$moment($$.payload).format(\"hh:mm:ss A\")","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":370,"y":4200,"wires":[["a59504fe.27529"]]},{"id":"280c7342.baca6c","type":"change","z":"c791cbc0.84f648","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$moment($$.payload).tz(\"Asia/Colombo\").format(\"hh:mm:ss A\")","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":390,"y":4280,"wires":[["a59504fe.27529"]]},{"id":"a59504fe.27529","type":"debug","z":"c791cbc0.84f648","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":580,"y":4180,"wires":[]}]

Isuru · 12 February 2022 11:51

[{"id":"d2556121.4d5c1","type":"tab","label":"Flow 5","disabled":false,"info":""},{"id":"1ec0d27a.f64fae","type":"ui_date_picker","z":"d2556121.4d5c1","name":"","label":"Start Date","group":"1435e071.39b76","order":1,"width":6,"height":2,"passthru":true,"topic":"startDate","topicType":"msg","className":"","x":400,"y":120,"wires":[["f6da907d.19b36"]]},{"id":"f6da907d.19b36","type":"change","z":"d2556121.4d5c1","name":"","rules":[{"t":"set","p":"startDate","pt":"flow","to":"$moment($$.payload).tz(\"Asia/Colombo\").format(\"DD-MM-YYYY\")","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":650,"y":120,"wires":[[]]},{"id":"5fb3276f.f26f88","type":"change","z":"d2556121.4d5c1","name":"","rules":[{"t":"set","p":"startTime","pt":"flow","to":"$moment($$.payload).tz(\"Asia/Colombo\").format(\"hh:mm:ss A\")","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":660,"y":160,"wires":[[]]},{"id":"fe1ffcae.ee1e3","type":"ui_text_input","z":"d2556121.4d5c1","name":"","label":"Start Time","tooltip":"","group":"1435e071.39b76","order":2,"width":6,"height":2,"passthru":true,"mode":"time","delay":"0","topic":"startTime","sendOnBlur":true,"className":"","topicType":"msg","x":410,"y":160,"wires":[["5fb3276f.f26f88"]]},{"id":"d35670e6.22a12","type":"ui_date_picker","z":"d2556121.4d5c1","name":"","label":"End Date","group":"1435e071.39b76","order":4,"width":6,"height":2,"passthru":true,"topic":"endDate","topicType":"msg","className":"","x":300,"y":220,"wires":[["ab1cc480.225378"]]},{"id":"47ac46d6.840bc8","type":"ui_text_input","z":"d2556121.4d5c1","name":"","label":"End Time","tooltip":"","group":"1435e071.39b76","order":5,"width":6,"height":2,"passthru":true,"mode":"time","delay":"0","topic":"endTime","sendOnBlur":true,"className":"","topicType":"msg","x":300,"y":260,"wires":[["c6953e54.84ffd"]]},{"id":"ab1cc480.225378","type":"change","z":"d2556121.4d5c1","name":"","rules":[{"t":"set","p":"endDate","pt":"flow","to":"$moment($$.payload).tz(\"Asia/Colombo\").format(\"DD-MM-YYYY\")","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":510,"y":220,"wires":[[]]},{"id":"c6953e54.84ffd","type":"change","z":"d2556121.4d5c1","name":"","rules":[{"t":"set","p":"endTime","pt":"flow","to":"$moment($$.payload).tz(\"Asia/Colombo\").format(\"hh:mm:ss A\")","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":510,"y":260,"wires":[[]]},{"id":"68fc2e0b.82c97","type":"inject","z":"d2556121.4d5c1","name":"query","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"query","payload":"","payloadType":"date","x":230,"y":340,"wires":[["375c6c15.d9c7e4"]]},{"id":"6e27c3b8.745b3c","type":"debug","z":"d2556121.4d5c1","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":770,"y":360,"wires":[]},{"id":"e0833d6b.fcdbf","type":"sqlite","z":"d2556121.4d5c1","mydb":"9f3c85e2.f9a5d8","sqlquery":"msg.topic","sql":"","name":"","x":630,"y":500,"wires":[["6e27c3b8.745b3c"]]},{"id":"375c6c15.d9c7e4","type":"function","z":"d2556121.4d5c1","name":"My SQL Database Request","func":"// only execute query if download button has been clicked\nif(msg.topic == \"download\" || msg.topic == \"query\"){\n\n    var startDate = flow.get(\"startDate\");\n    var startTime = flow.get(\"startTime\");\n    \n    var endDate = flow.get(\"endDate\") || false;\n    var endTime = flow.get(\"endTime\") || false;\n\n    // check if date range is correct\n    if (startTime && startTime && endDate && endTime &&\n    new Date(endDate) >= new Date(startDate)) {\n        msg.topic = \"SELECT max(temperature) FROM data WHERE timestamp BETWEEN '\" + startDate + \" \" + startTime + \" 'AND '\" + endDate + \" \" + endTime + \"' ORDER BY timestamp limit 5000\";\n        return msg;\n    } else {\n        node.warn(\"end date is before start date. or context not set\");\n    }\n\n}\n\n\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":400,"y":400,"wires":[["e0833d6b.fcdbf"]]},{"id":"1435e071.39b76","type":"ui_group","name":"Data Logger","tab":"48313a88.497314","order":3,"disp":true,"width":"17","collapse":false,"className":""},{"id":"9f3c85e2.f9a5d8","type":"sqlitedb","db":"C:\\sqlite\\newtest.db","mode":"RWC"},{"id":"48313a88.497314","type":"ui_tab","name":"monitoring","icon":"fa-desktop","order":1,"disabled":false,"hidden":false}]

This is the flow. Here I Input two time inputs and need to convert these inputs to the local time zone.

E1cid · 12 February 2022 11:58

As said working here

I am on UTC time GMT
and is 11.57am here
What version of node-red are you running?
please supply an example where it is not working.

E1cid · 12 February 2022 12:03

Maybe ask the OP what format they want, I would not know.

Isuru · 12 February 2022 13:14

I shall get 02.43 am and 12.04 am but I'm getting 08.13 am and 05.34 pm respectively where offset is 05 hrs 30 mints.
I'm using node red version 1.3.5

E1cid · 12 February 2022 13:29

utc is 02.43 and columbo is +05:30 hours so the time would be 08.13, which is what you are getting.

I think there is some confusion as to what you are trying to do.

If you want to get the same as in the timepicker try

$moment($$.payload).tz("UTC").format("hh:mm:ss A")

If not please explain further what you are trying to get as output.

Isuru · 12 February 2022 14:03

Here I input time as 02.43 am and 12.04 am. So when I input time using date picker & time nodes, is it entered as UTC time? Is there a way to enter time as a input in local time zone. As what I need is for the user to enter time in local time zone and link the same applied input time to the database.

For example, If the user select time to be 02.43 am and 12.04 am in two different dates, I need to retrieve the maximum of a selected Column (say Temperature) from 02.43 am and 12.04 am of the mentioned dates in SQL the database table.

As I understood, here I'm entering the time in UTC and getting the output from local zone. Is there a way to make the UTC offset 0 through moment function.

E1cid · 12 February 2022 14:21

You enter time and get a timestamp in milliseconds from midnight. Convert with tz("UTC") to get the same output as displayed in timepicker.

mickym2 · 12 February 2022 14:27

You can specifiy the input and the output format:

So if you want to use 12.04 am as input - specify:

$moment(payload,'hh.mm a').format('DD.MM.YYYY HH:mm:ss')

Normally if not specified the local time is used.

TotallyInformation · 12 February 2022 15:01

The Dashboard text input set to time uses type="time" on the input field so returned value should be a string - however, Dashboard converts it to a number. So entering "00:01" returns 60000. I'd never spotted this previously as I don't generally use Dashboard.

I would actually say that the return from the text input node for time type may be incorrect? That's because if, for example, in the UK you entered 08:00 on March 27th this year, you should get a value of 25200000 (because the clocks go forward 1 hour at 1am so you lose an hour) but I suspect you might get a value of 28800000. However, I can't really test that until March 27th so I don't know for sure. It would actually be much clearer if the native HH:mm or HH:mm:ss string format were returned so that you could correctly choose what to do with it depending on your needs.

Other than that issue, the return value is just a ms from midnight value so it isn't relevant whether it is a local or UTC input. Effectively it is a local value but you need to make sure that you correctly allow for it when you combine the date and time. You should combine them as a local date-time value. JavaScript mostly likes to work in UTC (which is generally correct, you should do date/time calculations and store values in UTC in order to avoid the issues of timezone and DST changes). But input from and output to real people should normally be in local time.

There is, however, another input type type="datetime-local" which combines date and time input to a single input. It isn't supported by IE.

It would be worth nicely asking @dceejay if he wouldn't mind adding the datetime-local type in the next release of Dashboard - that would also avoid these issues.

Isuru · 12 February 2022 16:57

Could you kindly share how to modify the moment node to convert tz(''UTC")?

UnborN · 12 February 2022 16:58

maybe its easier to have one date time picker component to select the start and end DateTime
and then convert the timezone with the methods described above.

Example using ui_template :

[{"id":"b125397bd37e1b79","type":"ui_template","z":"d2556121.4d5c1","group":"06154a33390c79c4","name":"DateTimePicker","order":0,"width":"6","height":"6","format":"<link rel=\"stylesheet\" href=\"https://pro.fontawesome.com/releases/v5.10.0/css/all.css\"\n  integrity=\"sha384-AYmEC3Yw5cVb3ZcuHtOA93w35dYTsvhLPVnYs9eStHfGJvOvKxVfELGroGkvsg+p\" crossorigin=\"anonymous\" />\n\n<!-- Bootstrap is not required for the picker to work-->\n<script src=\"https://cdn.jsdelivr.net/npm/bootstrap@5.1.0/dist/js/bootstrap.min.js\"\n  integrity=\"sha384-cn7l7gDp0eyniUwwAZgrzD06kc/tftFf19TOAs2zVinnD/C7E91j9yyk5//jjpt/\" crossorigin=\"anonymous\"></script>\n\n<link href=\"https://cdn.jsdelivr.net/npm/bootstrap@5.1.0/dist/css/bootstrap.min.css\" rel=\"stylesheet\"\n  integrity=\"sha384-KyZXEAg3QhqLMpG8r+8fhAXLRk2vvoC2f3B09zVXn8CA5QIVfZOJ3BCsw2P0p/We\" crossorigin=\"anonymous\" />\n<!-- end bootstrap-->\n\n\n<!-- Popperjs -->\n<script src=\"https://cdn.jsdelivr.net/npm/@popperjs/core@2.9.3/dist/umd/popper.min.js\"\n  integrity=\"sha384-eMNCOe7tC1doHpGoWe/6oMVemdAVTMs2xqW4mwXrXsW0L84Iytr2wi5v2QjrP/xp\" crossorigin=\"anonymous\"></script>\n\n<script src=\"https://cdn.jsdelivr.net/gh/Eonasdan/tempus-dominus@master/dist/js/tempus-dominus.js\"></script>\n\n<link href=\"https://cdn.jsdelivr.net/gh/Eonasdan/tempus-dominus@master/dist/css/tempus-dominus.css\" rel=\"stylesheet\" />\n\n<h6>Start Date</h6>\n<div class='input-group mb-2' id='datetimepicker1' data-td-target-input='nearest' data-td-target-toggle='nearest'>\n  <input\n     id='datetimepicker1Input'\n     type='text'\n     class='form-control'\n     data-td-target='#datetimepicker1'\n   />\n  <span\n     class='input-group-text'\n     data-td-target='#datetimepicker1'\n     data-td-toggle='datetimepicker'\n   >\n     <span class='fas fa-calendar'></span>\n  </span>\n</div>\n\n<h6>End Date</h6>\n<div class='input-group mb-2' id='datetimepicker2' data-td-target-input='nearest' data-td-target-toggle='nearest'>\n  <input\n     id='datetimepicker2Input'\n     type='text'\n     class='form-control'\n     data-td-target='#datetimepicker2'\n   />\n  <span\n     class='input-group-text'\n     data-td-target='#datetimepicker2'\n     data-td-toggle='datetimepicker'\n   >\n     <span class='fas fa-calendar'></span>\n  </span>\n</div>\n\n<button class=\"btn btn-primary btn-sm mt-3\" ng-click=\"send({ payload: getDate() })\">Send Selected Dates</button>    \n\n<script>\n  var theScope = scope\n\nsetTimeout(()=> {\n\n// define tomorrow to limit maxDate option\n// theScope.tomorrow = new Date();\n// theScope.tomorrow.setHours(23,59,59);\n\ntheScope.startDate = new tempusDominus.TempusDominus(document.getElementById('datetimepicker1'), {\n  useCurrent: false, \n  defaultDate: new Date(), \n  restrictions: {\n   //  maxDate : theScope.tomorrow,\n  },\n  display: {\n    buttons: {\n       today: true\n    },\n    // components: {\n    //    clock:false,\n    //    date: false,\n    // }\n  },\n \n });\n\n\ntheScope.endDate = new tempusDominus.TempusDominus(document.getElementById('datetimepicker2'), {\n  useCurrent: false, \n  defaultDate: new Date(), \n  restrictions: {\n   //  maxDate : theScope.tomorrow,\n  },\n  display: {\n    buttons: {\n       today: true\n    },\n    // components: {\n    //    clock:false,\n    //    date: false,\n    // }\n  },\n \n });\n\n\n }, 1000)\n\n// getDate function \n theScope.getDate = function() {\n   return { payload: {  \"startDate\": theScope.startDate.viewDate.toISOString(), \"endDate\": theScope.endDate.viewDate.toISOString()  } }\n }\n\n\n</script>","storeOutMessages":false,"fwdInMessages":false,"resendOnRefresh":true,"templateScope":"local","className":"","x":480,"y":780,"wires":[["24b00ad504f22a3a"]]},{"id":"24b00ad504f22a3a","type":"debug","z":"d2556121.4d5c1","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":650,"y":780,"wires":[]},{"id":"06154a33390c79c4","type":"ui_group","name":"Calendar","tab":"a3a053c01a1ffc93","order":1,"disp":true,"width":"20","collapse":false,"className":""},{"id":"a3a053c01a1ffc93","type":"ui_tab","name":"Home","icon":"dashboard","disabled":false,"hidden":false}]

E1cid · 12 February 2022 17:22

Post 11 shows how to apply the tz() in the JSONata expression. This is in a change node not a moment node.

Isuru · 17 February 2022 07:06

Thanks @E1cid, @TotallyInformation, @Colin and @mickym2 for your help. Still working on this. Seems date picker node together with moment node can be used for the requirement.

Topic		Replies	Views
Local time for text input node using datetime picker General	25	339	15 June 2023
Convert format of datetime from database to local time with Moment node Dashboard	6	3575	17 June 2020
Using "moment" node. Not quite there General	2	573	1 August 2018
Change timezone from UTC to UTC 01:00 General	13	2230	16 November 2022
Convert datetime with moment General	11	3690	10 July 2019

Convert the time input to local time zone in node red through moment function

Related topics