SQLite: datetime and timestamp can't query as chrono::DateTime<chrono::Utc>?

[biluohc]

Version: v0.4.0-beta.1 and master

Problem: Whether it is datetime or timestamp in sql, the rust code can choose NaiveDatetime only.

create table users (
    id INTEGER primary key AUTOINCREMENT not null,
    name text UNIQUE not null,
    email char(20) UNIQUE not null,
    pass char(65) not null, -- 'passwd hash'
    create_dt datetime not null default (datetime('now')), -- 'create datetime'
    update_dt datetime not null default (datetime('now')) -- 'update datetime'
);

code
```rust
#[cfg(any(feature = "postgres", feature = "sqlite"))]
type SqlID = i64;

// time_zone: https://github.com/launchbadge/sqlx/issues/329
#[cfg(any(feature = "mysql", feature = "postgres"))]
type SqlDateTime = chrono::DateTime<chrono::Utc>;
// type SqlDateTime = chrono::NaiveDateTime;

// Extend derive(FromRow): https://github.com/launchbadge/sqlx/issues/156
#[cfg(any(feature = "sqlite"))]
type SqlDateTime = chrono::DateTime<chrono::Utc>;

#[derive(FromRow, Serialize, Deserialize, Debug)]
pub struct User {
    pub id: SqlID,
    pub name: String,
    // pub phone: String,
    pub email: String,
    // not return password
    #[serde(skip_serializing)]
    pub pass: String,
    pub create_dt: SqlDateTime,
    pub update_dt: SqlDateTime,
}

    async fn user_query(&self, name: &str) -> sqlx::Result<User> {
        sqlx::query_as!(
            User,
            r#"
        SELECT id, name, email, pass, create_dt, update_dt
        FROM users
        where name = ?
                "#,
            name
        )
        .fetch_one(&self.sql)
        .await
    }

Error:

error[E0308]: mismatched types
  --> src/users/dao.rs:32:9
   |
32 | /         sqlx::query_as!(
33 | |             User,
34 | |             r#"
35 | |         SELECT id, name, email, pass, create_dt, update_dt
...  |
39 | |             name
40 | |         )
   | |_________^ expected struct `chrono::DateTime`, found struct `chrono::NaiveDateTime`
   |
   = note: expected struct `chrono::DateTime<chrono::Utc>`
              found struct `chrono::NaiveDateTime`
   = note: this error originates in a macro (in Nightly builds, run with -Z macro-backtrace for more info)

error: aborting due to previous error

[mikeyhew]

Does anyone have any insight into what's causing this? I don't know much about how the query_as macro works yet.

BTW @biluohc there's a markdown error in your issue description, I think you need a ``` below the create table sql snippet.

[jplatte]

Just works™ for me. I'm using the postgres TIMESTAMPTZ (= TIMESTAMP WITH TIME ZONE) type, are you maybe using TIMESTAMP (= TIMESTAMP WITHOUT TIME ZONE)?

EDIT: Sorry, I see this seems to be MySQL.

[mikeyhew]

@jplatte this is sqlite actually, unless i totally misread the issue

[mikeyhew]

It looks like this issue is related: #1004

My guess is that query_as! decides which output type to use solely based on the sql type of the output column (datetime), and even though there's three options, it has to pick something. In this case it picks chrono::NaiveDateTime. Is that correct?

[mikeyhew]

And the workaround is to opt out of type checking of that column with select created_dt as "created_dt: _

[jplatte]

Oh, it says SQLite in the title 🤦🏼

I saw AUTOINCREMENT and ? placeholders and assumed MySql.

[jplatte]

My guess is that query_as! decides which output type to use solely based on the sql type of the output column (datetime), and even though there's three options, it has to pick something. In this case it picks chrono::NaiveDateTime. Is that correct?

Sounds correct.

[mehcode]

Yeah, this is working as intended. Unfortunately, SQLite doesn't have a timezone-aware datetime type so our only option is NaiveDateTime. The as "created_at: _" is intended as a "I know what I'm doing, I stored UTC times in SQLite, go away SQLx, etc." escape hatch.

[mikeyhew]

@mehcode interesting, I thought that this was just a limitation, but it sounds like it's actually a design decision to only let you get a NaiveDatetime and make you convert it to Datetime<Utc> yourself.

Should DateTime<Utc> and Datetime<Local> be removed from the list on https://docs.rs/sqlx/0.5.1/sqlx/sqlite/types/index.html then?

[mehcode]

It's true that SQLite in SQLx does have support for UTC date times though.

A point of confusion here could be that - in general - SQLx needs to map a SQL type to one Rust type. This is observable in many areas, if you use BIGINT, a i64 will be inferred by SQLx. That doesn't mean we don't support casting that to i32 using the type override syntax documented with the query macro.

For datetimes, we need to pick a canonical type for SQLite. It's true that DATETIME is not timezone aware in SQLite. It's barely even a real type.

We could actually quite easily support inferring a utc datetimes from something like DATETIME UTC. Its extended syntax but supported by SQLite as much as regular datetimes. It's still going to be a breakable contract between developer and database to never put non UTC dates into the db, but at least it's explicit.