{{NOW_UTC}} time issues in MySQL saving from Wappler node project

[StevenM]

I have server action which basically creates an account and part of the account it adds in the current time, which I will use to track the trial period for the account.

However, on saving the form for registration and initiating that action the datetime insert into MYSQL throws an error and can't work out why

message: "insert into Account (confirmed, expiry, type) values ('No', '2020-10-08T23:42:38.000Z', 'Admin') - ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '2020-10-08T23:42:38.000Z' for column 'expiry' at row 1

I have the database field set to datetime I have done a bit of a search and found similar issues on saving in node.js and mention of correctly formatting to save.

However, I am not sure how to o this and assume that you should have to do that in Wappler, so perhaps something else is going on or I am doing incorrectly?

Can anyone provide some light?

[brad]

Have you tried setting your field type to timestamp?

Not sure if that will solve it but worth a try.

[StevenM]

Gave that a try and still no joy.

message: "insert into Account (confirmed, expiry, type) values ('No', 1600994214, 'Admin') - ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '1600994214' for column 'expiry' at row 1"

[StevenM]

Tried changing the field type to timestamp and then datetime but still no good.

[StevenM]

Ok this is interesting and somewhat insane! Maybe someone from Wappler can shed some light here.

I decide to create a new datetime field using Wappler database Manager and adding this in the insert query "trial_time"

When it added the insert in the query it specifies the field type as "text", I checked the database and it is definitely type "datetime".

So I had this:

So when I run the server action it appears to work with the new field created using the Wappler database manager this is what I get. the new field which is datetime is not in error.

message: "insert into Account (Trial, confirmed, expiry, trial_time, type) values ('1', 'No', 1600995330, '2020-09-25T00:55:30', 'Admin') - ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '1600995330' for column 'expiry' at row 1"

Ok stay with me because this is where it gets nuts!

I decided to change the "expiry" insert from the {{TIMESTAMP}} back to {{NOW_UTC}} (Keep in mind this is what I had it before when I was experiencing the error).

So I have this:

I then ran the server action.

And blow me over no errors it worked! What the....

OK so looking through this logically, there is something else going on here and perhaps adding the new datetime field added some MySQL fix for time insert??

Can someone from Wappler explain what is going on here?

I also have another table which has a {{NOW_UTC}} on a "datetime" field which is also throwing the same error on the "hash_expiry" field below.

message: "insert into User (account_id, creation_date, email, firstname, hash, hash_expiry, lastname, password) values (525, '2020-09-25T01:02:56', 'stegg@groupquality.com', 'helpyu', '930130384984051b54ddc2b773a76b2cff0dc60175ce3bee43383da0dbf67b8f', '2020-09-27T01:02:56.000Z', 'sther', 'bed4efa1d4fdbd954bd3705d6a2a78270ec9a52ecfbfb010c61862af5c76af1761ffeb1aef6aca1bf5d02b3781aa854fabd2b69c790de74e17ecfec3cb6ac4bf') - ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '2020-09-27T01:02:56.000Z' for column 'hash_expiry' at row 1"

I will follow the same process and see if it automatically fixes this too.

[StevenM]

Yes updated following the same process fixed the field that wasn’t working

Then I can remove the new Wappler created field from the query and the previous field saves to the database where as before you get the error explained above.

[Teodor]

I am really confused of your posts in this topic.
What i did to test your issue (if i understand it correctly):

1. Created a database connection in database manager.
2. Created a new table. Added a datetime filed to it, applied changes:

3. Went to Server Connect and added an insert step in my server action. Used {{NOW_UTC}}:

4. Run the server action.
5. Checked the results, also in mysql workbench:

All good - not sure what did you do and how exactly did you it, but in my test case everything is good with mysql database and nodejs server model.

[StevenM]

No didn’t create a table not sure where that came from. Sorry it’s not clear and suspect it’s not clear because the issue is not a simple one.

1.Existing database created using Navicat.
2. Connection database to wappler and all ok.
3. Tried to save datetime to the database on a date time field with server action.
4. Error in insert query as above - should not have thrown error.
5. Decide to try adding a new datetime field type using wappler database manager.
6. Changed a server action to insert UTC Now into this new field (in query manager insert it shows this new field as type as ‘text’ even though it’s type is datetime - you can see the existing datetime field in the images above.
7. Run the insert query action and this time it works with the new datetime field as well as the old One which previously did not work and produced the error- see error above.
8. Remove the new datetime field I created Using Wappler from the insert query and run the action again and the old date time field works now without the error.

I did this with two tables that had an existing date time field createdh , and after adding the additional datetime field using wappler database manager, and adding it to the insert query server action it worked and fixed the datetime error with the preexisting datetime field.