How to make diesel understand how to convert between postgres timestamptz and SystemTime which implements postgres FromSql and ToSql?

1 
pub struct Game {
    pub id: Option<i32>,
    pub name: String,
    pub ball_ips: String,
    pub description: String,
    pub created_at: SystemTime,
    pub updated_at: SystemTime,
}

...
//this line gives an error
        let game = games::table.find(id).first::<Game>(&mut conn).await?;
//error[E0277]: the trait bound `(Integer, diesel::sql_types::Text, diesel::sql_types::Text, diesel::sql_types::Text, diesel::sql_types::Timestamptz, diesel::sql_types::Timestamptz): CompatibleType<Game, Pg>` is not satisfied
//   --> src/models/game.rs:39:56
//    |
//39  |         let game = games::table.find(id).first::<Game>(&mut conn).await?;
//    |                                          -----         ^^^^^^^^^ the trait `CompatibleType<Game, Pg>` is not implemented for `(Integer, diesel::sql_types::Text, diesel::sql_types::Text, diesel::sql_types::Text, diesel::sql_types::Timestamptz, diesel::sql_types::Timestamptz)`
//    |                                          |
//    |                                          required by a bound introduced by this call
//    |
//    = help: the following other types implement trait `CompatibleType<U, DB>`:
//              (ST0, ST1)
//              (ST0, ST1, ST2)
//              (ST0, ST1, ST2, ST3)
//              (ST0, ST1, ST2, ST3, ST4)
//              (ST0, ST1, ST2, ST3, ST4, ST5)
//              (ST0, ST1, ST2, ST3, ST4, ST5, ST6)
//              (ST0, ST1, ST2, ST3, ST4, ST5, ST6, ST7)
//              (ST0, ST1, ST2, ST3, ST4, ST5, ST6, ST7, ST8)
//            and 24 others
//    = note: required for `SelectStatement<FromClause<table>, query_builder::select_clause::DefaultSelectClause<FromClause<table>>, query_builder::distinct_clause::NoDistinctClause, query_builder::where_clause::WhereClause<diesel::expression::grouped::Grouped<diesel::expression::operators::Eq<columns::id, diesel::expression::bound::Bound<Integer, i32>>>>, query_builder::order_clause::NoOrderClause, LimitOffsetClause<LimitClause<diesel::expression::bound::Bound<BigInt, i64>>, NoOffsetClause>>` to implement `diesel_async::methods::LoadQuery<'_, _, Game>`
//note: required by a bound in `diesel_async::RunQueryDsl::first`
//   --> /Users/nikos/.cargo/registry/src/github.com-1ecc6299db9ec823/diesel-async-0.1.1/src/run_query_dsl/mod.rs:690:35
//    |
//690 |         diesel::dsl::Limit<Self>: methods::LoadQuery<'query, Conn, U> + Send + 'query,
//    |                                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ required by this bound in `diesel_async::RunQueryDsl::first`
2

There are quite a few things to note here:

  • Your question is missing important context. It's not clear which diesel version you use and it's not clear how the corresponding table in defined in your schema.rs file. (I can guess some of that based on the error message, but that might be wrong).
  • The error message is a general mismatch between what your query returns and what your target type provides as types. These need to match. Checkout the corresponding API documentation for all of the involved SQL types for a list of compatible rust types.
  • In your case there seem to be 3 mismatches:
    • The id: Option<i32> field does not match the Integer returned by your query, because you just cannot assign a non nullable value to nullable values is rust as well
    • The created_at: SystemTime field does not match the Timestamptz returned by your query, because Timestamptz is timezone aware, while SystemTime is not time zone aware.
    • The updated_at: SystemTime field does not match the Timestamptz returned by your query, because Timestamptz is timezone aware, while SystemTime is not time zone aware.

Now you seem to ask: How can I force diesel to convert that nevertheless: The answer there is that you probably shouldn't do that for the reasons why these conversions are not supported by default. If you really want to make that work, checkout the documentation of #[diesel(deserialize_as)].

Also as you seem to imply that this must work as the corresponding type implements postgres::FromSql/postgres::ToSql: I do not understand how that's related. These are traits from a totally unrelated crate.

1 Like
3

I did solve the issue after our chat on gitter yesterday

4

This topic was automatically closed 90 days after the last reply. We invite you to open a new topic if you have further questions or comments.