sqlite: unsupported type DATE/DATETIME when using macros

[moubctez]

In Sqlx 0.6.1, when using macros, I get error: unsupported type DATE of column or error: unsupported type DATETIME of column for Sqlite DATE and TIME columns. This fails for both chrono and time.

I guess, sqlx-macros are not aware of the new supported types.

[dragonnn]

https://www.sqlite.org/datatype3.html 3.1.1. Affinity Name Examples sqlite doesn't have DATE/DATETIME types, when you create such table everything just becomes NUMERIC. That issue do pop up wth sqlite so often: https://github.com/launchbadge/sqlx/issues/1982 https://github.com/launchbadge/sqlx/issues/1907

[vip-ehowlett]

I am getting this with mssql as well.

Field it's referencing: #[sqlx(rename="Date")] pub date : Option<sqlx::types::time::Date>,

Cargo entry sqlx = { version = "0.6", features = [ "runtime-async-std-native-tls", "mssql", "time", "chrono" ] }

[zopieux]

The documentation is quite misleading then, as it explicitly references a DATETIME Sqlite "type" in the tables: https://docs.rs/sqlx/latest/sqlx/sqlite/types/index.html#time

I've read the various resources @dragonnn linked, but I'm not sure what the recommended workaround. It seems like sqlite is fine being typed with non-existing type names such DATETIME that could be used as hint for sqlx to use the proper type. Can projects register a custom adaptor that uses that fake type, or at least a heuristic using the column name eg. ends_with("_ts")?

---

Edit: I'm a bit at a loss here. The code clearly registers type adaptors for sqlite DATETIME, TIMESTAMP etc. "types", and also defines proper time crate adaptors. Therefore, why is sqlx 0.6.1 refusing to handle a DATETIME column?

error: unsupported type DATETIME of column #1 ("created_at")

---

Edit again: I think I get it, sorry for the dumb statements: while sqlx is fine transforming date/time types at runtime (with the knowledge of the expect type), the compile-time macros might not be able to know/understand the introspected type because it's internally just a text/numeric. If this is it, I'm not sure I understand why the error message is able to specify that it doesn't support DATETIME though…

---

Edit again: Oh okay, sqlx-macros only has adapters for chrono, not time. But weirdly enough, time adapters are not required if you also enable the chrono feature. I suppose this works magically through come compat layer. I got macros to work with time types by:

1. making sure I opt-in both time and chrono features, even though I don't use chrono types
2. casting to OffsetDateTime (or some other time type) using the usual select col as "col: OffsetDateTime" syntax, as otherwise the macro expects the chrono type.

[abonander]

The types listed in that file are just the types that the macros can choose by default. Using type overrides in the query itself bypasses that list.

There isn't a particular reason the time types aren't in that list, it just didn't get added in #1865.