How can I read a timestamp with timezone (timestamptz) value from PostgreSQL in Rust?

Question

What's the right Rust data type to use for a timestamptz when using postgres version 0.17.0 with Rust 1.40.0?

I read the docs for Timestamp but have no idea what this means or how to implement it.

The readme for 0.17.0-alpha.1 has a table which says that timezone corresponds to Rust types time::Timespec or chrono::DateTime<Utc> but neither works for me.

When I try to use the stipulated features in my Cargo.toml using:

[dependencies]
postgres = {version="0.17.0-alpha.1", features=["with-chrono", "with-time"]}

I get this error:

the package `mypackage` depends on `postgres`, with features: `with-time, with-chrono` but `postgres` does not have these features.

---

Here's some functional code and corresponding dependencies. I want to be able to read and print the timezone per row (commented out)

main.rs

use postgres::{Client, Error, NoTls};
extern crate chrono;
use chrono::{DateTime, Local, NaiveDateTime, TimeZone, Utc};
extern crate time;
use time::Timespec;

pub fn main() -> Result<(), Error> {
    let mut client = Client::connect("host=localhost user=postgres", NoTls)?;

    client.simple_query(
        "
        CREATE TABLE mytable (
            name        text NOT NULL,
            timestamp   timestamptz NOT NULL)",
    )?;

    client.execute("INSERT INTO mytable VALUES ('bob', now());", &[])?;

    for row in client.query("SELECT * FROM mytable", &[])? {
        let name: &str = row.get(0);
        // let timestamp: chrono::DateTime<Utc> = row.get(1);   //doesnt work
        // let timestamp: Timespec = row.get(1);  //doesnt work
        println!("name: {}", name);
        // println!("timestamp: {}", timestamp);
    }

    Ok(())
}

Uncommenting

let timestamp: Timespec = row.get(1);  //doesnt work

error[E0277]: the trait bound `time::Timespec: postgres_types::FromSql<'_>` is not satisfied  
--> src/main.rs:30:39  | 30 | 
let timestamp: Timespec = row.get(1);   //doesnt work     
                              ^^^ the trait `postgres_types::FromSql<'_>` is not implemented for `time::Timespec`

Uncommenting

let timestamp: chrono::DateTime<Utc> = row.get(1);   //doesnt work

error[E0277]: the trait bound `chrono::DateTime<chrono::Utc>: postgres_types::FromSql<'_>` is not satisfied
--> src/main.rs:29:52 29 |         
let timestamp: chrono::DateTime<Utc> = row.get(1);   //doesnt work
                                           ^^^ the trait `postgres_types::FromSql<'_>` is not implemented for `chrono::DateTime<chrono::Utc>`

Cargo.toml

[dependencies]
postgres = "0.17.0"
chrono = "0.4.10"
time = "0.1.14"

This link says to use time = "0.1.14". latest version also fails https://crates.io/crates/postgres/0.17.0-alpha.1

Sovran

0.17.0 is newer than 0.17.0-alpha.1; why would you continue using the alpha? – Aintab

@Aintab i was only using 0.17.0-alpha.1 cos its docs have a "Type Correspondence" table and features info. thank you for your help/answers!! – Sovran

For a long time I thought Chrono was the only way to read timestamps. But it is also possible with the crate time. Enable the feature "with-time-0_3" before use. – Processional

Answer 1

Once you know what features are available, it's reasonably direct to see that you need to use the with-chrono-0_4 feature.

use chrono::{DateTime, Utc}; // 0.4.10
use postgres::{Client, Error, NoTls}; // 0.17.0, features = ["with-chrono-0_4"]

pub fn main() -> Result<(), Error> {
    let mut client = Client::connect("host=localhost user=stack-overflow", NoTls)?;

    client.simple_query(
        r#"
        CREATE TABLE mytable (
            name        text NOT NULL,
            timestamp   timestamptz NOT NULL
        )"#,
    )?;

    client.execute("INSERT INTO mytable VALUES ('bob', now());", &[])?;

    for row in client.query("SELECT * FROM mytable", &[])? {
        let name: &str = row.get(0);
        let timestamp: DateTime<Utc> = row.get(1);
        dbg!(name, timestamp);
    }

    Ok(())
}

[src/main.rs:20] name = "bob"
[src/main.rs:20] timestamp = 2020-01-16T01:21:58.755804Z

Aintab

Thanks; this worked for me: [dependencies.postgres] version = "0.19.5" features = ["with-chrono-0_4", "with-time-0_3"] – Homiletics

Answer 2

Thanks to https://github.com/sfackler/rust-postgres/issues/211, this works using version 0.15.0 of the postgres crate, but I'd like a solution using version 0.17.0.

main.rs

extern crate postgres;
use postgres::{Connection, TlsMode};

extern crate chrono;
use chrono::{DateTime, Local, NaiveDateTime, TimeZone, Utc};

fn main() {
    let conn = Connection::connect("postgresql://postgres@localhost:5432", TlsMode::None).unwrap();

    conn.execute(
        "CREATE TABLE person (
             name            VARCHAR NOT NULL,
             timestamp       timestamptz
        )",
        &[],).unwrap();

    conn.execute("INSERT INTO person VALUES ('bob', now());", &[]).unwrap();

    for row in &conn.query("SELECT * FROM person", &[]).unwrap() {
        let name: String = row.get(0);
        let timestamp: chrono::DateTime<Utc> = row.get(1);
        println!("name: {}", name);
        println!("timestamp: {}", timestamp);
    }
}

Output:

name: bob
timestamp: 2020-01-15 23:56:05.411304 UTC

Cargo.toml

[dependencies]
postgres = { version = "0.15", features = ["with-chrono"] }
chrono = "0.4.10"
time = "0.1.14"

Sovran

Answer 3

Use the time crate

A timestamptz value can be read directly via the time crate:

use time::OffsetDateTime;

for row in client.query("SELECT * FROM mytable", &[])? {
    let nullable_value: Option<OffsetDateTime> = row.get(0);
    if let Some(value) = nullable_value {
      println!("value: {:?}", value.to_string();
    }
}

After enabling the with-time-0_3 feature flag on the postgres crate the traits FromSql and ToSql are implemented for the structs Date, Time, PrimitiveDateTime, OffsetDateTime of the time crate.

But beware, the time is returned in UTC:

impl<'a> FromSql<'a> for OffsetDateTime {
    fn from_sql(type_: &Type, raw: &[u8]) -> Result<OffsetDateTime, Box<dyn Error + Sync + Send>> {
        let primitive = PrimitiveDateTime::from_sql(type_, raw)?;
        Ok(primitive.assume_utc())
    }
    accepts!(TIMESTAMPTZ);
}

If you want to use the local time instead, just ask Postgres to convert it for you using ::timestamp, and read the value with PrimitiveDateTime.

Processional

Actually, using time (0.3) is a really bad idea because the lack of time zone support will be a problem for larger applications. #79042852 – Processional