Timestamp in Rust's Diesel Library with Postgres

Question

I have been taking a look at Rust's Diesel ORM today by following along on this walk-through, and I can't get a Timestamp to work.

Cargo.toml

[dependencies]
diesel = { version = "0.6.2", features = ["chrono"] }
diesel_codegen = { version = "0.6.2", default-features = false, features = ["nightly", "postgres"] }
dotenv = "0.8.0"
dotenv_macros = "0.8.0"

models.rs

#[derive(Queryable)]

pub struct Author {
    pub id: i32,
    pub first_name: String,
    pub last_name: String,
    pub email: String
}

pub struct Post {
    pub id: i32,
    pub author: Author,
    pub title: String,
    pub body: String,
    pub published: bool,
    pub created: Timestamp,
    pub updated: Timestamp
}

(I read that there's a diesel::types::Timestamp type)

lib.rs

#![feature(custom_derive, custom_attribute, plugin)]
#![plugin(diesel_codegen, dotenv_macros)]

#[macro_use]
extern crate diesel;
extern crate dotenv;

pub mod schema;
pub mod models;

use diesel::prelude::*;
use diesel::types::Timestamp;
use diesel::pg::PgConnection;
use dotenv::dotenv;
use std::env;

pub fn establish_connection() -> PgConnection {
    dotenv().ok();

    let database_url = env::var("DATABASE_URL").
        expect("DATABASE_URL must be set");
    PgConnection::establish(&database_url).
        expect(&format!("Error connecting to {}", database_url))
}

But these are the errors I get when I try to use it:

<diesel macros>:5:1: 5:71 note: in this expansion of table_body! (defined in <diesel macros>)
src/schema.rs:1:1: 1:40 note: in this expansion of table! (defined in <diesel macros>)
src/schema.rs:1:1: 1:40 note: in this expansion of infer_schema! (defined in src/lib.rs)
src/lib.rs:1:1: 1:1 help: run `rustc --explain E0412` to see a detailed explanation
src/lib.rs:1:1: 1:1 help: no candidates by the name of `Timestamptz` found in your project; maybe you misspelled the name or forgot to import an external crate?
src/lib.rs:1:1: 1:1 error: type name `Timestamptz` is undefined or not in scope [E0412]
src/lib.rs:1 #![feature(custom_derive, custom_attribute, plugin)]

...

<diesel macros>:38:1: 38:47 note: in this expansion of column! (defined in <diesel macros>)
<diesel macros>:5:1: 5:71 note: in this expansion of table_body! (defined in <diesel macros>)
src/schema.rs:1:1: 1:40 note: in this expansion of table! (defined in <diesel macros>)
src/schema.rs:1:1: 1:40 note: in this expansion of infer_schema! (defined in src/lib.rs)
src/lib.rs:1:1: 1:1 help: run `rustc --explain E0412` to see a detailed explanation
src/lib.rs:1:1: 1:1 help: no candidates by the name of `Timestamptz` found in your project; maybe you misspelled the name or forgot to import an external crate?
src/models.rs:16:18: 16:27 error: type name `Timestamp` is undefined or not in scope [E0412]
src/models.rs:16     pub created: Timestamp,
                              ^~~~~~~~~
src/models.rs:16:18: 16:27 help: run `rustc --explain E0412` to see a detailed explanation
src/models.rs:16:18: 16:27 help: you can import it into scope: `use diesel::types::Timestamp;`.
src/models.rs:17:18: 17:27 error: type name `Timestamp` is undefined or not in scope [E0412]
src/models.rs:17     pub updated: Timestamp
                              ^~~~~~~~~

It looks like the first error, Timestamptz is a result of infer_schema not knowing how to interpret that Postgresql type, which is already in the table. As for the second, I thought perhaps if explicitly imported that Timestamp type, I could create a Post struct with it.

Is there something obvious that I am doing wrong here?

As an aside, I am pretty new to Rust and Diesel uses a fair bit of code-generation, so it's easy to get lost, but I thought this should be a straightforward thing to accomplish.

---

Edit:

I used timestamp with time zone to create the table, and it looks like that may not be supported yet:

CREATE TABLE post (
    ...
    created timestamp with time zone NOT NULL,
    updated timestamp with time zone
)

---

Edit 2:

I changed models.rs to look like the following and got rid of the error about Timestamp being undefined. I also realized that I needed #[derive(Queryable)] above each of the structs to be derived. The following compiles fine, but the previous errors with Timestamptz remain:

use diesel::types::Timestamp;

#[derive(Queryable)]
pub struct Author {
    pub id: i32,
    pub first_name: String,
    pub last_name: String,
    pub email: String
}

#[derive(Queryable)]
pub struct Post {
    pub id: i32,
    pub author: Author,
    pub title: String,
    pub body: String,
    pub published: bool,
    pub created: Timestamp,
    pub updated: Timestamp
}

Solution

All of the types in diesel::sql_types are markers to represent various SQL datatypes for your schema. They should never be used in your own structs. What you need is a type which implements diesel::deserialize::FromSql<diesel::sql_types::Timestamp, diesel::pg::Pg> (docs: FromSql, Timestamp, Pg). There are two types which implement that trait.

The first is std::time::SystemTime which doesn't require additional dependencies, but doesn't have a ton of capabilities.

The second is chrono::NaiveDateTime. This is probably the type you want. In order to use it, you'll need to add chrono to your dependencies, and change the diesel line in Cargo.toml to include the chrono feature, so it'll look something like diesel = { version = "0.7.0", features = ["postgres", "chrono"] }

(Technically there's a third type, which is diesel::data_types::PgTimestamp but that's almost certainly not what you want, as that struct is just the literal representation of timestamp in the database so other types don't have to worry about raw bytes)