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:
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:
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.
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.
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.
high concurrency of webhooks being sent and received.
important to log the exact time to the millisecond for debugging and audit purposes.
esp for chats (our real life use case), ms matters as it changes the sequence of msgs!
we use Wappler apps in business critical PROD environments, you see.
if you could create a new NOW_UTC_MS it'd be awesome. what say @patrick?
Bumping this request.
bumping this request.
bumping this request.
We should start a new feature request. I‘m with you, we definetly need millisecond precision. I‘m working with MS SQL and there it is the default format. As long as I‘m not able to make a query with millisecond precision I get wrong results.
@patrick
is your following post still up to date?
@nshkrsh inserting with milliseconds does already work
