Datepicker stores and retrieves values incorrectly

Using the date picker item, if I store a date to MSSQL server, it retrieves it correctly but displays it as the day prior. The correct date is getting set in the SQL server.

Storing: value 2015-01-01T05:00:00.000Z
Annotation 2020-04-19 175042

You can see in the image that it has read the value correctly, but it is displaying it as the prior date. The formatted string field has it as 12/31/2014 not 1/1/2015.

Hmmmm looks like a bug! Thanks & noted – will let you know when we have a fix!

@pdamato @kent Is 2015-01-01 at midnight UTC 2015-12-31 in your timezone by any chance? If you live in any timezone that is a - utc adjustment rather than a +, it would be. I’m pretty sure that the datepicker displays values in the local timezone by default.

:thinking:

Very interesting

Table values also incorrectly show date values. My example is using MSSQL again, but it actually looks like Retool is reading the value correctly and then changing it a little when it shows it on the screen. Annotation 2020-04-30 102244

i’m having the same issue. it always appears to change the date to local. A lot of what we do it in UTC so it would great if it didn’t automatically change the date.

@pdamato It looks like the date column type has the same functionality where it parses dates into the local user’s timezone. We’re currently working on adding an option to datetimepickers to disable the conversion, and I’ll open up another ticket for adding that option to date type columns in the table!

1 Like

@pdamato We’ve now added in this option to both timepicker and datetimepicker components!

image

1 Like

Hi @alex-w - thanks for the work on the datetimepickers - great stuff! I just refactored my api/tables to always have timezone to get around the bug you mentioned in post #7, e.g. “I’ll open up another ticket for adding that option to date type columns in the table!” Is there a way to track that issue?

Thanks again!

Dan

Can the same option be extended to these components within a table field data type ?

I’m currently using the “Date” column type for a table, which displays just the 4-byte day representation of a date/time correctly in the table (as shown in the screenshot below).

However, you can see that the newRow query for a date entered is accounting for the current timezone when a date is entered, which means (for me) that the date displayed switches back a day, due to UTC being 10 or 11 hours behind my local timezone.

Is it possible for either:

  1. A “Date” datatype to be a 4-byte date that ignores time and timezones altogether? e.g. postgresql “Date” type
  2. A “Date” datatype in a table field/column to have the same local time toggle as has been added above for text input controls ?

Hi @gsanders! That’s actually queued up as well to get the same option as the components, so #2! @gilcrest We’ll post back in this same thread with updates on the date column type!

1 Like

@gsanders We do still need to add this or a similar option to the date column type, but just want to check- have you seen the Date time (original timezone) column type?