postgresql - rust 使用 postgres crate 这个客户端，用select * 查询出来的结果用serde_json 序列化，怎么处理postgres 中的 TIMESTAMP 类型？

Question

使用 postgres rust 驱动，文档 https://docs.rs/postgres/latest/postgres/，

使用 select * 语句查询数据，在用 serde_json 序列化基本类型的时候正常，但是特殊类型就panic 了，如 时间戳类型 TIMESTAMP，代码如下

l  let query = format!("SELECT * FROM \"{}\".\"{}\";", &schema, &table);  let records = client.query(&query, &[]).expect("没有获取到结果");  let mut result: Vec<Map<String, Value>> = vec![];  for row in records.iter() {    let mut record = serde_json::Map::new();    for (i, column) in row.columns().iter().enumerate() {      let value: Value = match column.type_() {        &postgres::types::Type::INT4 => row.get::<_, i32>(i).into(),        &postgres::types::Type::TEXT => row.get::<_, String>(i).into(),        &postgres::types::Type::BOOL => row.get::<_, bool>(i).into(),        /// 这里报错        &postgres::types::Type::TIMESTAMP => row.get::<_, String>(i).into(),        // &postgres::types::Type::TIMESTAMP => {        //   row.get::<_, NaiveDateTime>(i).into()        // }        // Add more type conversions as needed        _ => Value::Null,      };      record.insert(column.name().to_string(), value);    }    result.push(record);  }  result

怎么处理 postgres 中的特殊类型如 TIMESTAMP 类型的序列化？

Answer 1

extern crate chrono;extern crate postgres;extern crate serde_json;extern crate serde;extern crate serde_derive;use chrono::NaiveDateTime;use postgres::{Client, NoTls};use serde_json::Value;use std::collections::HashMap as Map;fn main() {    let conn_str = "host=localhost user=postgres password=yourpassword dbname=yourdbname";    let mut client = Client::connect(conn_str, NoTls).expect("Failed to connect to DB");    let schema = "your_schema";    let table = "your_table";    let records = query_records(&mut client, &schema, &table);    println!("Records: {:?}", records);}fn query_records(client: &mut Client, schema: &str, table: &str) -> Vec<Map<String, Value>> {    let query = format!("SELECT * FROM \"{}\".\"{}\";", schema, table);    let records = client.query(&query, &[]).expect("Failed to fetch records");    let mut result: Vec<Map<String, Value>> = vec![];    for row in records.iter() {        let mut record = serde_json::Map::new();        for (i, column) in row.columns().iter().enumerate() {            let value: Value = match column.type_() {                &postgres::types::Type::INT4 => row.get::<_, i32>(i).into(),                &postgres::types::Type::TEXT => row.get::<_, String>(i).into(),                &postgres::types::Type::BOOL => row.get::<_, bool>(i).into(),                &postgres::types::Type::TIMESTAMP => {                    let dt = row.get::<_, NaiveDateTime>(i);                    let dt_string = serde_json::to_string(&dt.format("%Y-%m-%d %H:%M:%S").to_string()).unwrap();                    serde_json::from_str(&dt_string).unwrap()                }                _ => Value::Null,            };            record.insert(column.name().to_string(), value);        }        result.push(record);    }    result}

Cargo.toml文件：

[dependencies]postgres = "0.20.0"serde_json = "1.0.72"chrono = "0.4.19"serde = "1.0.130"serde_derive = "1.0.130"