Date fields being automatically converted to Datetime strings

[Richard_Brouwer]

I'm having some trouble with Date fields from my mysql database being converted to Datetime strings in Retool. I'm not sure why a Date field is automatically converted to Datetime in Retool when the column in the UI is specified as Date. This leads to errors when trying to write back to the underlying table. I have tried using the moment() function which only works if I click into the date field manually, even if I was updating other fields in the form. This is a bit fiddly and I was wondering if there was any other advice on how to get this to work more smoothly.

[Paulo]

Hi @Richard_Brouwer, happy to help!
I just tested it on my end by creating an app and connecting it to a MySQL database. I did not run into this issue when I added rows to the table, or when retrieving data from it.

I'll share my configuration.

The "Format" for the date column is set to Date:

I'm using the same "Format" for the date input as you, but I'm not using moment.js for the "Default value" or "Placeholder":

Here is the insert query using the GUI:

* I added a "Success" event handler to refresh the table after the insert.


With this configuration, I'm able to successfully add rows to the table without the column type ever going from Date to Datetime or vice versa .

Here is how the table looks like after adding to it through the UI:

If you are still experiencing the same issue after trying this approach, I would check a couple of things:

1. The MySQL db column type, perhaps it was set to string or datetime.
2. The query that runs the insert, maybe there is some data manipulation that has an impact on the value before it is sent to the db.

Let me know if you have any questions!
​

[Richard_Brouwer]

I really appreciate you looking at this. Unfortunately, when I replicated your steps, the same issue arises.

1. create database in mysql and put in some data
   

   
   
   
   

2. I make an app in retool and run a query from that table and the dates are now datetime.
   

   

3. Table from the data has fields as DATE, but when looking at the javascript, they are DATETIME.
   

   
   
   
   

Appreciate your thoughts.

[Paulo]

@Richard_Brouwer, thank you for adding more details!
I used Railway to create the MySQL db and I connected to it using Retool's built in MySQL db "Create resource" configuration, I did not run into this issue there. To be able to replicate it and find a way to fix it, could you share with us who is the provider of your db, as well as how are you connected to this resource?

[Richard_Brouwer]

I am a total Luddite, so I created the mysql database on an ubuntu server. Totally vanilla.

I sometimes interact with this thru phpmyadmin hosted on the same ubuntu server.

I connect to retool through the same builtin MySQL db "Create Resource" configuration.

As I work around, I use {{moment(date5.value).format("YYYY-MM-DD")}} to convert it back to right format so it will write to the database correctly.

[Paulo]

With this workaround, are we still having issues when the application retrieves data from the db?

[bobthebear]

you can see this with the Demo Data used as the Data Source. the table/date selector displays Date(year, month, day) but the data is stored as ISO 8601 (year, month, day, hour, min, sec, millisec).

@Richard_Brouwer if you put the call to moment() in Mapped Value area it'll format all columns. you'll notice table1.selectedRow[0].Date will have the formatted version and table1.selectedSourceRow[0].Data will be the original ISO 8601 format. now you don't have to remember to call moment whenever you want to use the date. hopefully this helps?

[Richard_Brouwer]

@Paulo no it retrieves it as datetime but with displays it as date if I have set the column type as date in tables/forms.

@bobthebear great idea - thanks! - save me remembering to use moment() in the queries.