appsmithorg
[Bug]: Sql server OffsetDateTime not supported?
Is there an existing issue for this?
- [X] I have searched the existing issues
Current Behavior
Connected Azure SQL server db.
Tried to run a simple select * from users; query.
Got
Java 8 date/time type java.time.OffsetDateTime not supported by default: add Module "com.fasterxml.jackson.datatype:jackson-datatype-jsr310" to enable handling (through reference chain: java.util.ArrayList[0]->java.util.LinkedHashMap["CreatedAt"]->microsoft.sql.DateTimeOffset["offsetDateTime"])
{
"requestParams":{...}
}
Steps To Reproduce
- Create sql server db table with column of datatype datetimeoffset
- Try to query data in appsmith
Environment
Production
Version
Self-Hosted
@pbjorklund thank you for following up, agree that this should just work. Will check with the team and update you here
Workaround
@pbjorklund This is definitely an issue. The team will look into this and will get back with a permanent solution. To unblock you, can you please try the following query and let us know if this serves the purpose for now?
SELECT
CONVERT(varchar(255), your_offset_datetime_column, 127) ts
FROM your_table
This is not straightforward to achieve. It will take special handling of the OffsetDateTime to let it be parsed through Jackson. A possible solution could be found in this SO post.
Total users of Ms sql in last months : 499 Assuming 10% of users are affected by this, total count : 50
Stats
| Stat | Values |
|---|---|
| Reach | 50 |
| Effort (months) | 0.75 |
Workaround
@pbjorklund This is definitely an issue. The team will look into this and will get back with a permanent solution. To unblock you, can you please try the following query and let us know if this serves the purpose for now?
SELECT CONVERT(varchar(255), your_offset_datetime_column, 127) ts FROM your_table
This workaround works for me, thanks
Sure, workaround does enable querying.
But means select * will not work for any table in our db since all tables contains offsets.
Will check back in another year or so 👌