"Trait bound for NaiveDateTime is not satisfied" when adding a timestamp column

[kieraneglin]

Hello! I know this is small, however I cannot find anything that helps online or in the docs and I feel it may be a bug.

Please keep in mind, this is my first ever time using Rust (or any low-level language). I'm coming from Ruby and JavaScript. This may be something very simple, but my research before posting this didn't lead to anything.

Anyway, off to it:

Versions

• Rust: 1.20.0-nightly
• Diesel: 0.13.0
• Chrono: Originally 0.4.x, now 0.3.1 for troubleshooting purposes
• SQLite: 3.8.10.2

Features

• diesel: sqlite, chrono
• diesel_codegen: sqlite

Problem description

When you have a timestamp DATETIME field in your DB, you cannot use NaiveDateTime as it's type in Rust. When you do, compilation fails with this message:

  --> src/bin/read_ticket.rs:12:27
   |
12 |     let results = tickets.load::<Ticket>(&connection).expect(
   |                           ^^^^ the trait `diesel::types::FromSqlRow<diesel::types::Nullable<diesel::types::Timestamp>, _>` is not implemented for `chrono::naive::datetime::NaiveDateTime`
   |
   = help: the following implementations were found:
             <chrono::naive::datetime::NaiveDateTime as diesel::types::FromSqlRow<diesel::types::Timestamp, DB>>
   = note: required because of the requirements on the impl of `diesel::types::FromSqlRow<(diesel::types::Integer, diesel::types::Text, diesel::types::Text, diesel::types::Nullable<diesel::types::Timestamp>), _>` for `(i32, std::string::String, std::string::String, chrono::naive::datetime::NaiveDateTime)`
   = note: required because of the requirements on the impl of `diesel::Queryable<(diesel::types::Integer, diesel::types::Text, diesel::types::Text, diesel::types::Nullable<diesel::types::Timestamp>), _>` for `ticket::models::Ticket`

This message didn't occur and the code otherwise worked before the addition of a timestamp.

Steps to reproduce

1. Create new SQLite DB. Set up Diesel as normal
2. Create a migration with a timestamp field, like so: created_at DATETIME DEFAULT CURRENT_TIMESTAMP
3. Within your model, try to assign the type NaiveDateTime to the timestamp field
4. Try to fetch records from DB

My setup

Model

use super::schema::tickets;
use chrono::NaiveDateTime;

#[derive(Queryable)]
pub struct Ticket {
    pub id: i32,
    pub title: String,
    pub description: String,
    pub created_at: NaiveDateTime,
}
// ...

Migration

CREATE TABLE tickets (
  id INTEGER NOT NULL PRIMARY KEY,
  title VARCHAR NOT NULL,
  description TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)

"Controller"

extern crate ticket;
extern crate diesel;

use ticket::*;
use self::models::*;
use diesel::prelude::*;

fn main() {
    use self::schema::tickets::dsl::*;

    let connection = establish_connection();
    let results = tickets.load::<Ticket>(&connection).expect(
    //                    ^^^^ Problem here
        "Error loading tickets",
    );
    // ...
}

Here's a link to the repo as well. It's pretty-well empty, so it may as well be a minimal reproducible set for this error.

Disclaimer

Again, this is my first Rust project. So if I forgot any information or if you need me to elaborate, please let me know!

Thank you for your time.

[Eijebong]

Wow, this is a nice report :o

Your created_at column is not defined as NOT NULL. You should deserialize it in an Option<NaiveDateTime> instead of NaiveDateTime

[kieraneglin]

Thank you!

That did it. Now it's giving me an error saying that it can't parse Datetime, but I'll try and sort that one out on my own ;)

[Eijebong]

Huuu if you inserted those values with diesel it should work. Maybe you ran into a bug in the implementation of Datetime for sqlite

[kieraneglin]

Hmmm. Good point. But honestly, I'm willing to bet it's a "me problem" ;). I'll try to flesh out the issue and I'll add a comment here if it turns out to be at all related to this. Just in case it helps out another beginner in the future.

Unrelated, but would a PR for an issue template be welcomed?

That aside, I'm happy with this solution and I'll close the issue. Thank you again for your help!

[Eijebong]

See #969 for the issue template. (And yes a PR would be welcome :))

[kieraneglin]

Update: The issue was because I had a raw-SQL seed file. Like you said, if I entered the data through Diesel, everything worked as expected.