Timestamp value change while insert and select from mariadb

INSERT

9/20/2018, 12:40:24 AMnode: 4dd4086a.fba828INSERT INTO mytab (DeviceId,Line,outPIN,count,Time) VALUES (?,?,?,?,?) : msg.payload : array[5]

[ 2, 1, 7, 1, "2018-09-20 00:40:24" ]

SELECT
9/20/2018, 12:40:36 AMnode: 9e0ad6c1.d131e8Select DeviceId, Line, outPIN, count,Time FROM mytab LIMIT 1 : msg.payload : string[82]

"[{"DeviceId":2,"Line":1,"outPIN":"7","count":1,"Time":"2018-09-19T16:40:24.000Z"}]"

I did use the following function but still now changes
var utc = json[0]["Time"].replace(/^(.)T(.).\d+Z$/, "$1 $2");

I've not used MariaDb, but it looks like it's expecting the input time to already be converted to the UTC timezone -- then when the same time is retrieved it is converted to the local timezone.

What are you using to generate that time string? One simple way is to install node-red-contrib-moment, and use it to convert that time from the local timezone to UTC before inserting in the db.

Best practice is to keep all timestamps in their internal format until it's time to display them to the end user. Otherwise, you will end up chasing your tail through different client timezones, and things like Daylight savings and leap seconds.

Good day,

I do have "182018......" in my time stamp. How i can remove that, pls
Regards
Rama Tito

If there is nothing in the "Input From" field of the moment node, the default behavior is to use the current system timestamp -- which looks like what you are trying to do with all of that date string slicing/dicing in the function node. From the moment node's help information:

Input

Must be either a JS datetime object or a string that Moment.js can resolve (with the help of moment.parseFormat). If the input is null, does not exist or is a blank string, the current Date/Time will be used.

This can be used to add a current timestamp to a flow of any kind easily.

So you can safely get rid of the function node, and clear the input field, and be guaranteed to have a valid timestamp output in whatever timezone and format you need. Check the help text for a link to the momentjs site, where it describes all the possible output formatting options...

Is the first time intended to be a local time? If so then I suspect the two times are the same.

AH, I missed the formatting error when I responded last time... so for posterity, here is the fix:

You have specified your moment date format string as YYY-MM-DD HH:MM:SS starting with 3 Y's instead of 4. Note also that MM represents the Month number (not Minutes), so your timestamp would be malformed as well...

Hi all, I had some issue today with this kind of topic and here is one of the solution I had.
When I was selecting data from MYSQL, where was timestamp column data in database and in chart was shifted with some amount in my case +10h. I was checking server settings timezone and it was set to New York even if we are in Europe in Slovakia. I changed this but it was not helpful. Now() command in MYSQL also was sowing local time without any problem. Than I checked MYSQL node settings and there is one small setting I committed called Timezone where I did not have set any option there. By clicking on it, it selected node-red option. Than when I selected data from databse again all was exactly as suppouse to be.

1 Like

Cheers, thats really helps

this worked for me
thanks