URGENT: DateTime Picker Invalid Defaults

Queries and Resources
1

This is a high priority issue where dates times cannot be inserted/restored from the database correctly because the default is inserted as NAN and restored as invalid.
Steps to reproduce:

  1. Create a DATETIME column.
  2. Create a Retool DateTime picker.
  3. Create a SQL Builder query that takes the value of DateTime picker and inserts it into the DATETIME column.
  4. Save default DATETIME picker value to database which is NAN.
  5. Restore Retool DateTime picker default which is NAN.
  6. DateTime breaks.
    As it stands, it appears Retool's date time picker is broken. Please fix ASAP!
2

When you look at the value of the datetimepicker field in the left panel, the value is formatted as 2019-10-03T10:03:03.000Z. Looking at the accepted formats for a datetime column and a datetimeoffset column, I’d guess it’s possible that since datetime doesn’t accept time zones, the “Z” is the issue.

3

Hmm, what do you suggest as a fix? Should I change the database column?

4

Try {{moment(datetimepicker1.value).format("YYYY-MM-DD HH:MM:SS")}} as the value you send to the query, or you could possibly change the column type in the database (I’m not an expert on sql)

5

I guess it would depend on how you want to store/handle timezones

6

Thanks, that’s a good temporary fix!

7

I hope there’s something that can be done to better support defaults.

8

Unfortunately, the date/time picker component is broken.

9

Step 1, pick a time

10

Step 3, restore value

11

Step 4, verify value again

12

The date component saves and restores the same value incorrectly.

13

I think this is a problem with the timezone offset, isn’t it? Possible the timezone global differing between the Retool environment and your DB. Do you need the time component?
In Postgres, I would store the data in a date column and cast the variable in the update query (I don’t know if you can do that with the retool sql write resource but you can definitely use moment.format(“L”) in a transformer or in the inline JS to strip out the time component).

14

ahh yeah timezones are really confusing!
TLDR: if you want to work with dates, date strings are your friend. use datetimepicker.formattedString, and date strings directly from the database. the less you use JS Date objects and moment objects, the better.
dates/datetimes in retool have a bunch of quirks:

1050×396

  1. moment(‘1995-06-14’) works, because moment will interpret it as “1995-06-14 at midnight, local timezone”
  2. string “1995-06-14” also works, because we cast strings to moment in the datetimepicker
  3. date object new Date(‘1995-06-14’) will be interpreted as “1995-06-14 at midnight UTC”, which in my local timezone is something like “1995-06-13 5pm”, and so the datetimepicker confusingly shows “1995-06-13”
    i’m actually super curious about the screenshots you posted @ray, where the previews are identical but the datetimepicker shows a different date. I haven’t been able to repro it just yet!
    and regarding date columns in the db, everything generally works smoothly when using postgres:

    2042×992


    the important thing here is that our postgres integration returns dates as date strings, which works well with the datepicker. but im not totally sure if all our integrations return this exact format.
15

@ray what database are you using? I can’t get date inserts to work with MySQL using a bulk update query because I can’t work out how to manipulate the date fields in columns that come from DATETIME types in an existing database . Somewhere additional ‘T’ and ‘Z’ are added and theyse can’t be fed back to the database. I get an SQL error rather than a ‘NaN’, but the net result is it doesn’t work. I can, however insert DATEMTIMES from a datetime picker if I use the format string YYYY-MM-DD HH:mm

16

So I had the same issue with updating Mysql when using a datepicker. I only needed YYYY-MM-DD.

In the BulkUpdateActionTrigger (that was created automatically by retool when adding inline editing to a table) you will see Under General -> Array of records to update {{table_name.recordUpdates}}

You can change this to:

{{_.forEach(table_name.recordUpdates, function (row) {
  if (row.date_column_name) {
row.date_column_name= moment(row.date_column_name).format("YYYY-MM-DD")
  }
})}}

This will convert the date to YYYY-MM-DD format when updating the Mysqltable. (table_name needs to be renamed to match the table, and date_column_name needs to be renamed to match the date field that it being updated)

17