Date picker output is one day ahead

The date picker output on the dashboard is exactly one day ahead. Is this intentional or a bug? For example, if one chooses "7/10/2021" from the date picker, then the output is "7/11/2021".

Here is the test flow.

[{"id":"d91caa22.d93c68","type":"ui_date_picker","z":"3853257b.be4c8a","name":"","label":"date","group":"14f3a809.89dcc8","order":4,"width":0,"height":0,"passthru":true,"topic":"topic","topicType":"msg","x":580,"y":1220,"wires":[["af73c0a7.f00af"]]},{"id":"af73c0a7.f00af","type":"function","z":"3853257b.be4c8a","name":"","func":"//msg.payload=new Date(1625880253754);  database\n\n//msg.payload=new Date(1625931000000);\n\n\n//msg.payload=new Date(1625934180000);\n\nvar date=new Date(msg.payload);\n\nmsg.payload=date;\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":800,"y":1220,"wires":[["12558dc5.0d0702"]]},{"id":"12558dc5.0d0702","type":"debug","z":"3853257b.be4c8a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1110,"y":1220,"wires":[]},{"id":"14f3a809.89dcc8","type":"ui_group","name":"Group 1","tab":"eb6fe9af.cd7238","order":1,"disp":true,"width":6},{"id":"eb6fe9af.cd7238","type":"ui_tab","name":"Test","icon":"dashboard","order":16,"disabled":false,"hidden":false}]

Attach a debug to the output of the date picker - what do you see?

The debug node is attached in the above flow. It is one day ahead.

Attach a debug to the output of the date picker

image

What do you see

The same: one day ahead.

The function just makes the conversion easy to read.

The point of this was to show you the debug output is UTC and that the date is correct.

If you click the timestamp in the debug window you will see different representations of the same time - one of them being the UTC offset time for your timezone.

e.g...

YVC01udPtc

What i am trying to demonstrate is the date is correct, it is down to how you use it.

To get just the date part as a string for your region, you need to do a bit more than just use the date object as it is in UTC.

Demo...
image

I see, so the default output from the date picker is UTC date, instead of the local date chosen by the user.

So this is clear:
Because an end user only uses local date, when he queries a database using the date picker, he needs to convert the date picker date (utc date) to local date, then coverts the local date back to utc date for the query to get correct result.

Not necessarily. If the database node accepts a date object and the database field is a datetime there should be no issue. However if the field is a string date then yes, you will have to mess about with formats.

There are a few factors involved.

There is still a problem with the date picker output.

We are in US pacific time. If we query a database with the date picker for today (7/10) and we have data in the database, then the query will return null (since the UTC date returned from the date picker is 7/11), which is incorrect.

UTC date is still 7/10 at the moment.

And if you add the date picker with the time picker from the dashboard for the database time query, it becomes very messy.

We know that if the time zone is a few hours behind the UTC, we have to deduct one day from the date picker output, and add the time picker time, and then convert it to UTC time for correct database query.

Now we need to figure out what if the time zone is a few hours ahead of UTC, do we need to add one day to the date picker output instead? (we have clients at different time zones).

An option to output the user-selected local date will be very helpful :laughing:

Try adding the time picker time first, that should give the correct UTC time, I think. Though I cannot test it at the moment.

We ended up writing a conversion function. The function adjusts the user selected date and time automatically to the UTC format query for a database. Here are the codes in case that somebody needs it.

var adate=flow.get('ana_date')||0; // date picker date
var atime=flow.get('ana_time')||0; // time picker time
adate=adate+atime;
var now = new Date();
var timeoffset=now.getTimezoneOffset() * 60000  // Get time zone info
var utcdate;  // UTC time for database query
if(timeoffset>0)
{
    utcdate=adate-86400000+timeoffset; 
}
else if(timeoffset<0)
{
    utcdate=adate+86400000+timeoffset; 
}
else
    utcdate=adate;
1 Like

This is certainly one solution -- another would be to add the node-red-contrib-moment node to your flow. It can convert from UTC to whatever timezone your runtime is currently using.

1 Like

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.