DateTime compiles but not DateTime querying a table with a column defined as timestamp with time zone

Question

I have a postgresql-table with a column defined as timestamp with time zone. The table is mapped to this struct:

#[derive(Serialize, Queryable)]
pub struct Location {
    pub publication_time: DateTime,
    pub id: i32,
    pub name: String,
    pub latitude: BigDecimal,
    pub longitude: BigDecimal,
}

The schema have this definition:

table! {
    locations {
        publication_time -> Timestamptz,
        id -> Integer,
        name -> Text,
        latitude -> Numeric,
        longitude -> Numeric,
    }
}

(partial) Cargo.toml:

serde = "1.0.125"
serde_json = "1.0.64"
serde_derive = "1.0.125"
diesel = { version = "1.4.6", features = ["postgres", "r2d2", "chrono", "numeric"] }
bigdecimal = { version = "0.1.0", features = ["serde"] }
chrono = { version = "0.4.19", features = ["serde"] }

The function that queries the database:

fn get_all_locations(pool: web::Data) -> Result, diesel::result::Error> {
    let conn = pool.get().unwrap();
    let items = locations.load::(&conn)?;
    Ok(items)
}

This is then serialized to a JSON-array using serde_json. The DateTime in the database is 2021-04-08 15:02:02.514+02. When DateTime is Utc the program compiles fine, but the DateTime shown in UTC like 2021-04-08T13:02:02.514Z. I changed publication_time to DateTime to retain the time zone information but then cargo build fails with:

error[E0277]: the trait bound `DateTime: FromSql` is not satisfied
  --> src/controller.rs:21:27
   |
21 |     let items = locations.load::(&conn)?;
   |                           ^^^^ the trait `FromSql` is not implemented for `DateTime`
   |
   = help: the following implementations were found:
              as FromSql>
   = note: required because of the requirements on the impl of `diesel::Queryable` for `DateTime`
   = note: 2 redundant requirements hidden
   = note: required because of the requirements on the impl of `diesel::Queryable<(diesel::sql_types::Timestamptz, diesel::sql_types::Integer, diesel::sql_types::Text, diesel::sql_types::Numeric, diesel::sql_types::Numeric), Pg>` for `models::Location`
   = note: required because of the requirements on the impl of `LoadQuery<_, models::Location>` for `locations::table`

I have another program that insert to this table and this works and the only difference is derive(Deserialize, Insertable).

#[derive(Deserialize, Insertable)]
pub struct Location {
    pub publication_time: DateTime,
    pub id: i32,
    pub name: String,
    pub latitude: BigDecimal,
    pub longitude: BigDecimal,
}

user_6803492 · Answer

Mapping a Timestamptz field to a DateTime is not supported by diesel itself, as it only provides the corresponding impl for DateTime. You can work this around by using the #[diesel(deserialize_as = "…")] attribute on the corresponding field and providing your own deserialization wrapper:

#[derive(Serialize, Queryable)]
pub struct Location {
    #[diesel(deserialize_as = "MyDateTimeWrapper")]
    pub publication_time: DateTime,
    pub id: i32,
    pub name: String,
    pub latitude: BigDecimal,
    pub longitude: BigDecimal,
}

pub struct MyDatetimeWrapper(DateTime);

impl Into> for MyDatetimeWrapper {
    fn into(self) -> DateTime {
        self.0
    }
}

impl Queryable for MyDateTimeWrapper
where
    DB: Backend,
    DateTime: Queryable,
{
    type Row =  as Queryable>::Row;

    fn build(row: Self::Row) -> Self {
        Self( as Queryable>::build(row).with_timezone(&Local))
    }
}

DateTime<Utc> compiles but not DateTime<Local> querying a table with a column defined as timestamp with time zone

1 Answers