Trouble with DateTime formats

[Kirby]

Hello!  I am having a lot of trouble with datetime formats.  I am downloading data from an API that returns a date string such as "2022-03-31T08:24:08-07:00".  I would like to show this as a DateTime to the user in my local time zone but Appsheet seems to be auto-formatting the string into a DateTime incorrectly by displaying it in the wrong time zone: "3/31/2022 3:24:08 PM".   

I could not find anything in the forums that affected this behavior (confirmed timezone in app, source data, google settings, personal computer, etc), so I thought I would develop a workaround by storing this data as a string, truncating the timezone at the end, and giving that to Appsheet as a DateTime virtual column.   

This is the bigger issue I am having: Even if I mark the column as plain text in google sheets, tell the app the data is text, name the column 'text' before regenerating the structure, AppSheet only ever displays some interpreted version of the data and shows me the same date string as above.  Thus when I try to truncate the text in my column, I am truncating "3/31/2022 3:24:08 PM" and not "2022-03-31T08:24:08-07:00".

I am importing this data from a third party source and can potentially alter that script to change the text there, or I can treat this as a datetime and then subtract the timezone in a virtual column, but in my opinion the data is not bad, it is the App auto-converting to a DateTime instead of reading in plain text that is the problem.

Any insight or suggestions on this issue would be greatly appreciated.  Please let me know if you need more information or have any other questions.  Thanks!

[Marc_Dillon · Accepted solution]

I can confirm the issue with Appsheet auto-converting the text value to a DateTime, even though it is formatted as text in the spreadsheet, and in a Text column in Appsheet.

I would call this a bug, and would contact support about it.

As a workaround for now, you can add some extra characters to the text on the backend to prevent the auto-conversion. For example adding a # at the end seems to work.

[Kirby]

Thank you so much for your quick response!  Glad to know the bug is repeatable.  I will implement a workaround for now.  I am having trouble finding a path to communicate with support.  Do you have a link by any chance?

[Marc_Dillon]

https://www.appsheet.com/Support/Contact

[Kirby]

Thank you!

[Steve]

Escalated.