Insert Date with milliseconds in a datetime(3) column MySQL

How can I insert a Date with milliseconds in MySQL?

I want to Export Data from a MS SQL table in a MySQL table. Everything works fine besides the dateformat.

In MS SQL datetime is stored with milliseconds. In my MySQL table I changed the type from datetime to datetime(3), but the milliseconds are ignored on insert.

JSON:

{"ErstelltAm":"2020-05-22 09:06:28.580"}

Inserted Date in MySQL:

image

Hi Marcel,

Maybe this link will help.

Hi Brad,

thank you for the link.

My problem is that I already have the datetime with milliseconds, because thats the way MS SQL stores the date in a datetime column.

My workflow is as follows:

I have 2 server connections, one for my MS SQL database and one for my MySQL database.
I am transferring the data from MS SQL to MySQL with a Query of the MS SQL table and then insert the Output to a Server Action Insert into the MySQL table. The JSON Output of the date is correct with milliseconds.
My problem now is that the date is not saved correctly in the MySQL table on insert (Server Action).

bump

Any news on this?

The insert in a datetime(3) column does not work like it should.

Wappler Insert Action:

MySQL database:
image

The milliseconds are ignored.

If I try to insert it manually with the following code in Navicat it works:

INSERT INTO navi_mytable (ErstelltAm) VALUES ('2020-05-25 12:38:23.277')

@patrick Did you already had the time to look at this?

For App Connect and Server Connect we are not going to change the current time format, it will stay with a precision in seconds.

What is the use-case for the time in ms precision? You could use a default value in your database that sets the timestamp/datetime, datetime passed from app/server connect will always have precision in seconds.

Our CRM Software stores date time in MS SQL with milliseconds, because that is the default in MS SQL.
For our portal I have to import some data from MS SQL to our MySQL database (daily). I’m checking the last entry to know where I have to start the new import. I thought that this would be the easiest way. The problem is that it does not work if I have two date time entries with 2020-05-25 12:38:23.277 and 2020-05-25 12:38:23.221.

Doesn’t it have an incremental id that you could use as reference?

No, it has an UUID.

image

You could try to edit the action file, the parameter is probably defined as type datetime, change that to string, that should prevent the transform.

1 Like

Your workaround worked! Thank you for your help!

@patrick Would it be possible for you to integrate a dropdown for the type in the Insert Action? This way it would be easier to change the type.