Rust tokio-postgres with timestamp column

Question

I have a simple Rust program which tries to fetch from a table in postgres using tokio. New to Rust, struggling with fetching jsonb and timestamp columns. Integer, string, jsonb columns all fine. I saw with postgres you can use with-chrono-0_4. I am using tokio-postgres, which doesn't seem to support chrono. But I tried add that feature from Cargo.toml anyway.

Cargo.toml

[package]
name = "postgres_demo"
version = "0.1.0"
edition = "2021"

[dependencies]
chrono = "0.4.30"
tokio = { version = "1", features = ["full"] }
tokio-postgres = { version="0.7", features = ["with-serde_json-1", "with-chrono-0_4"]}
serde_json = "1.0"

The code as follows:

use chrono::{DateTime, Utc}; // 0.4.10
use tokio_postgres::{NoTls, Error};
use serde_json::Value;

#[tokio::main]
async fn main() {
    if let Err(e) = run().await {
        eprintln!("Error: {}", e);
    }
}
async fn run() -> Result<(), Error> {
    // Replace these values with your actual PostgreSQL connection details
    let (client, connection) = tokio_postgres::connect(
        "host=localhost user=xxx password=xxxdbname=postgres", 
        NoTls).await?;

    tokio::spawn(async {
        if let Err(e) = connection.await {
            eprintln!("connection error: {}", e);
        }
    });

    // Fetch all rows from the 'xxx' table
    let rows = client.query("SELECT * FROM algo_order", &[]).await?;

    for row in &rows {
        let id: i32 = row.get("id"); 
        let xtype: &str = row.get("type"); 
        let status: &str = row.get("status"); 
        let data: Value = row.get("data"); // Jsonb

        /*
        panicked at 'error retrieving column created: error deserializing column 6: cannot convert between the Rust type `chrono::datetime::DateTime<chrono::offset::utc::Utc>` and the Postgres type `timestamp`', C:\Users\xxx\.cargo\registry\src\index.crates.io-6f17d22bba15001f\tokio-postgres-0.7.10\src\row.rs:151:25
        */
        let created_ : DateTime<Utc> = row.get("created");
        
        println!("id: {}, name: {}, status: {}, data: {}", id, xtype, status, data);
    }

    Ok(())
}

row.get('data') still give me

"panicked at 'error retrieving column created: error deserializing column 6: cannot convert between 
the Rust type `chrono::datetime::DateTime<chrono::offset::utc::Utc>` 
and the Postgres type `timestamp`', C:\Users\xxx\.cargo\registry\src\index.crates.io-6f17d22bba15001f\tokio-postgres-0.7.10\src\row.rs:151:25"

I also tried:

let created_str: &str = row.get("created");
let created_: DateTime<Utc> = DateTime::from_utc(
            NaiveDateTime::parse_from_str(created_str, "%Y-%m-%d %H:%M:%S%.6f")
                .expect("Error parsing timestamp"),
            Utc,
        );

Problem is similar:

'main' panicked at 'error retrieving column created: error deserializing column 6: cannot convert between 
 the Rust type `&str` and the Postgres type `timestamp`'

Answer 1

Ok, at last find the fix. You need implement your own struct 'TimestampWithTimeZone' which need override/implement FromSql. (Sounds like this is something tokio should improve upon as this is sooo common)

use chrono::{DateTime, Utc, NaiveDateTime};
use tokio_postgres::types::{FromSql, Type};
use tokio_postgres::{NoTls, Error};
use serde_json::Value;

#[derive(Debug)]
struct TimestampWithTimeZone(DateTime<Utc>);

impl<'a> FromSql<'a> for TimestampWithTimeZone {
    fn from_sql(ty: &Type, raw: &'a [u8]) -> Result<Self, Box<dyn std::error::Error + Sync + Send>> {
        if ty.name() == "timestamp" {
            let naive_datetime = NaiveDateTime::from_sql(ty, raw)?;
            Ok(TimestampWithTimeZone(DateTime::from_utc(naive_datetime, Utc)))
        } else {
            Err("Unexpected column type".into())
        }
    }

    fn accepts(ty: &Type) -> bool {
        ty.name() == "timestamp"
    }
}



#[tokio::main]
async fn main() {
    if let Err(e) = run().await {
        eprintln!("Error: {}", e);
    }
}
async fn run() -> Result<(), Error> {
    let (client, connection) = tokio_postgres::connect(
        "host=localhost user=xxx password=xxx dbname=postgres", 
        NoTls).await?;

    tokio::spawn(async {
        if let Err(e) = connection.await {
            eprintln!("connection error: {}", e);
        }
    });

    let rows = client.query("SELECT * FROM xxx", &[]).await?;

    for row in &rows {
        let id: i32 = row.get("id"); 
        let xtype: &str = row.get("type"); 
        let status: &str = row.get("status"); 
        let data: Value = row.get("data"); // Jsonb

        /*
        If you do this:
            let created_ : &str = row.get("created");

        You'd run into error:

            the trait bound `DateTime<Utc>: FromSql<'_>` is not satisfied
    --> src\main.rs:35:44
        |
    35  |         let created_ : DateTime<Utc> = row.get("created");
        |                                            ^^^ the trait `FromSql<'_>` is not implemented for `DateTime<Utc>`
        |
        = help: the following other types implement trait `FromSql<'a>`:
                &'a [u8]
                &'a str
                Box<[T]>
                Box<str>
                HashMap<std::string::String, Option<std::string::String>, S>
                IpAddr
                Json<T>
                Option<T>
                and 16 othersthe trait bound `DateTime<Utc>: FromSql<'_>` is not satisfied
                --> src\main.rs:35:44
                    |
                35  |         let created_ : DateTime<Utc> = row.get("created");
                    |                                            ^^^ the trait `FromSql<'_>` is not implemented for `DateTime<Utc>`
                    |
                    = help: the following other types implement trait `FromSql<'a>`:
                            &'a [u8]
                            &'a str
                            Box<[T]>
                            Box<str>
                            HashMap<std::string::String, Option<std::string::String>, S>
                            IpAddr
                            Json<T>
                            Option<T>
                            and 16 others
        */
        let created : TimestampWithTimeZone = row.get("created");
        
        println!("id: {}, name: {}, status: {}, data: {}, created: {}", id, algo_order_type, status, data, created.0.to_string());
    }

    Ok(())
}