Diesel: Insertable and chrono::DateTime<Utc> does not work

[vityafx]

I am trying to make my model insertable but I have a error which I don't know how to solve.

use diesel::prelude::*;
use chrono;
use chrono::prelude::*;
use super::schema::*;

#[derive(Debug, Clone, Queryable, Insertable)]
#[table_name = "punishments"]
pub struct Punishment {
    pub user_id: i32,
    pub server_id: i32,
    pub start_time: DateTime<Utc>,
    pub duration: chrono::Duration,
    pub reason: String,
}

models.rs:6:35
  |
6 | #[derive(Debug, Clone, Queryable, Insertable)]
  |                                   ^^^^^^^^^^ the trait `diesel::Expression` is not implemented for `chrono::DateTime<chrono::Utc>`
  |
  = note: required because of the requirements on the impl of `diesel::Expression` for `&'insert chrono::DateTime<chrono::Utc>`
  = note: required because of the requirements on the impl of `diesel::expression::AsExpression<diesel::sql_types::Text>` for `&'insert chrono::DateTime<chrono::Utc>`
  = note: this error originates in a macro outside of the current crate (in Nightly builds, run with -Z external-macro-backtrace for more info)

error[E0277]: the trait bound `chrono::Duration: diesel::Expression` is not satisfied

I use:

• sqlite
• diesel = "1"

[sgrif]

Datetime does not have a time xone. The appropriate type is NaiveDateTime

[vityafx]

Can we have a list of all available types for use with diesel? And also with all backends (pg, sqlite, mysql, and so on)? This would be very useful.

[vityafx]

Tried to change to NaiveDateTime:

error[E0277]: the trait bound `chrono::NaiveDateTime: diesel::Expression` is not satisfied
 --> models.rs:6:35
  |
6 | #[derive(Debug, Clone, Queryable, Insertable)]
  |                                   ^^^^^^^^^^ the trait `diesel::Expression` is not implemented for `chrono::NaiveDateTime`
  |
  = note: required because of the requirements on the impl of `diesel::Expression` for `&chrono::NaiveDateTime`
  = note: required because of the requirements on the impl of `diesel::expression::AsExpression<diesel::sql_types::Text>` for `&chrono::NaiveDateTime`
  = note: this error originates in a macro outside of the current crate (in Nightly builds, run with -Z external-macro-backtrace for more info)

[Eijebong]

You're probably missing the chrono feature on diesel

[vityafx]

@Eijebong
I don't miss it:

r2d2-diesel = "1"
r2d2 = "0.8"

[dependencies.diesel]
version = "1"
features = ["sqlite", "chrono"]

[dependencies.chrono]
version = "0.4"
features = ["serde"]

[vityafx]

The diesel migration sql script:

-- Your SQL goes here
CREATE TABLE punishments (
    user_id INTEGER NOT NULL,
    server_id INTEGER NOT NULL,
    start_time TEXT NOT NULL,
    duration TEXT NOT NULL,
    reason TEXT NOT NULL,
    PRIMARY KEY (user_id, server_id)
)

[Eijebong]

Ha, that's why, you have to tell diesel this is a datetime. Either change the type of start_time to DATETIME (or is it TIMESTAMP ?) in your migration or lie to diesel when you describe the table with table! and replace Text by Timestamp for that column (can't do that if you're using infer_schema!)

[vityafx]

@Eijebong I thought that was the problem! :) But I looked over the documentation of diesel and the code for the diesel::sqlite::types::chrono module. I saw that there is a implementation of FromSql<Text> or something like that. Looking over tests there convinced me this should work. I also looked at the sqlite 3 data types page and did not find anything like TIMESTAMP or DATETIME types there, I suppose, the date time types should be stored as TEXT, INTEGER or REAL in the sqlite. As for infer_schema!, I don't use it, I use DATABASE_URL=database.db diesel print-schema > schema.rs instead.

[vityafx]

Checked again - there is nothing like FromSql<Text, Sqlite>, I was wrong. But there are two more questions I need answers for: what sql types should I use for datetime for the sqlite 3? And we should really list all the type mappings for all the database the diesel support.

[sgrif]

http://docs.diesel.rs/diesel/sql_types/struct.Timestamp.html

You'll need to call your type TIMESTAMP or DATETIME on the SQL end for infer_schema! to infer that you want to store that sort of data in this column.

[vityafx]

Thank you. I somehow thought that the DATETIME is not proper type for the sqlite 3 database. Actually it is, so everything is fine. As for infer_schema, I don't use it but use print-schema instead as it was suggested in the diesel.rs website.

[sgrif]

infer_schema! and diesel print-schema use the same logic. I was referring to either one. ;)