Timestamp value change while insert and select from mariadb


#1

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");


#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.


#3

Good day,

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


#4

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...


#5

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


#6

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...