Mysql DATE and DATETIME columns in Retool queries

Hi, we're running into an issue with Retool queries where mysql DATE and DATETIME columns (which are stored without timezone in mysql) are being converted to an ISO timestamp with timezone added. E.g., querying in mysql for a column of type 'date' would return:

select my_date from my_table;

+------------+
| my_date    |
+------------+
| 2021-04-19 |
+------------+

Where, the result in retool query response for the same field, with the same date is:

image

The latter being incorrect, it should be interpreted without a timezone. The usual fashion for a date or datetime column is to have timezone data stored somewhere else, and use that for conversion (in this case, the timezone is application-specific). This is requiring us to re-cast 100% of our date fields.

Is this expected behavior, or a bug?

Hey Church! This is relatively expected- All data types are cast into their Javascript counterparts inside of Retool, which doesn't have a type for a date without time. The closest would be casting it to a string and then anywhere you want to use that data in some JS logic you would need to parse it back into a Date.

The moment.js library is included inside of Retool, and can be helpful with this. When the time/timezone is unclear, {{ moment(INPUT) }} will create a new moment date object interpreted in the local timezone of the user, and {{ moment.utc(INPUT) }} will interpret it as being in UTC time as the source



Hi there Alex,

Yes, we've been using moment() pretty heavily, but it's pretty challenging as we have to do it for every date column, everywhere it's used (which is a lot).

I understand the issue with Javascript dates. Is there any way we could, for example, in the resources specify the session TZ to use for all queries, so that we wouldn't have to manually convert every date field?

Thanks

Not quite a global setting, but are you using the query transformers? That would let you cast the results of each query once and then reference the transformed results inside of the UI/other queries

Yes, we are - but I'll need to go back to a lot of queries =)

There is also a setting in the resource setup for controlling whether to automatically cast MySQL date types into JS Date objects. Disabling that might get closer to what you are looking for, but keep in mind when you change it it will impact the existing logic and downstream will probably involve different work getting those non Dates to work as expected



Ohh, actually, that works quite nicely.

FWIW, the moment(timestamp) doesn't work, because it's already getting the 'Z' date code before we get to that point, the fix was to replace 'Z' with '-0500' before feeding to moment (faster than using a stacked moment timezone conversion).

Turning off dates -> timestamps does exactly what we want, and doesn't seem to impact any of our logic, as moment handles the dates just fine w/o being timestamps.

Ok, so it does look like doing that has some unintended consequences. It seems like I can no longer convert any timestamps, even after loading them. All timestamps are show in UTC now, in tables, even when selecting between Date time (original timezone) and Date time (local time zone), additionally, moment() seems incapable of converting to any other time zone as well.

Any thoughts on how to resolve this?

See - for example (I'm in central time zone):

image

image

Additionally, switching between (local timezone) and (original timezone) for Date time shows the exact same date and time, with the only difference being 'UTC' added to the end of the date.

Nevermind my previous post, now I understand the setting.

It doesn't turn off conversion of date strings to javascript dates, it turns off the conversion of all date and time columns, including timestamps resulting in a loss of timezone information from the DB, and preventing any further conversion of times from that point on.

It would be nice if this feature just turned off the non-TZ'd native columns like DATE and DATETIME, while leaving TIMESTAMP alone. But, it looks like we're just going to have to consider our use-cases around due dates and such and look for a different way to solve this.