如何为PgRange<DateTime<Utc>>实现Serialize序列化支持

在Rust项目中使用sqlx操作PostgreSQL的TSTZRANGE类型字段时，直接为包含PgRange<DateTime<Utc>>字段的结构体添加#[derive(Serialize)]派生宏会编译失败，错误信息如下：

pub timespan: PgRange<DateTime<Utc>>,
^^^ the trait `Serialize` is not implemented for `PgRange<chrono::DateTime<chrono::Utc>>`

此前的临时解决方案是将时间范围字段拆分为start、end两个独立的时间字段，该方式可正常实现序列化，但会提升SQL编写复杂度，在数据插入场景下问题尤为明显。

对应数据库表结构 #

CREATE TABLE reservation (
    id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    timespan TSTZRANGE
);

INSERT INTO reservation(timespan) VALUES
    (TSTZRANGE(now() + INTERVAL '0 hour', now() + INTERVAL '1 hour')),
    (TSTZRANGE(now() + INTERVAL '2 hour', now() + INTERVAL '3 hour'));

初始测试代码 #

依赖配置：

[dependencies]
chrono = { version = "0.4", features = ["serde"] }
tokio = { version = "1", features = ["full"] }
sqlx = { version = "0.5", features = ["runtime-tokio-native-tls" , "postgres", "chrono"] }
serde = { version = "1.0", features = ["derive"] }
serde_json = "1.0"

代码实现：

use chrono::prelude::*;
use serde::{Serialize, Deserialize};
use sqlx::FromRow;
use sqlx::postgres::types::PgRange;
use sqlx::postgres::PgPoolOptions;

// 目标结构体，需要贴近数据库表结构同时支持序列化
#[derive(FromRow, Debug)]
pub struct Reservation {
    pub id: i32,
    pub timespan: PgRange<DateTime<Utc>>,
}

// 临时方案结构体，可正常序列化但SQL编写复杂
#[derive(FromRow, Serialize, Debug)]
pub struct ReservationWorkaroud {
    pub id: i32,
    pub start: DateTime<Utc>,
    pub end: DateTime<Utc>,
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = PgPoolOptions::new()
        .max_connections(2)
        .connect(&"postgresql://postgres:postgres@localhost")
        .await?;

    // 目标写法：直接查询timespan字段，无需手动拆分
    let select_query = sqlx::query_as::<_, Reservation>("SELECT id, timespan FROM reservation");
    let reservations: Vec<Reservation> = select_query.fetch_all(&pool).await?;
    dbg!("{:?}", reservations);

    // 临时方案写法：需要在SQL中调用lower、upper函数拆分边界
    let select_query = sqlx::query_as::<_, ReservationWorkaroud>("SELECT id, lower(timespan) AS start, upper(timespan) AS end FROM reservation");
    let reservations: Vec<ReservationWorkaroud> = select_query.fetch_all(&pool).await?;
    dbg!("{:?}", reservations);

    Ok(())
}

使用serde提供的serialize_with属性为PgRange字段自定义序列化逻辑，无需修改SQL查询语句，即可让结构体保持和表结构一致的定义同时支持序列化，实现代码如下：

#[derive(FromRow, Serialize, Debug)]
pub struct Reservation {
    pub id: i32,
    #[serde(serialize_with = "serialize_range", flatten)]
    pub timespan: PgRange<DateTime<Utc>>,
}

fn serialize_range<S, T>(range: &PgRange<T>, serializer: S) -> Result<S::Ok, S::Error>
where
    S: serde::Serializer,
    T: Serialize,
{
    let PgRange { start, end } = range;
    std::ops::Range { start, end }.serialize(serializer)
}

实现要点：

• 从PgRange实例中解构出start、end两个边界值
• 转换为标准库自带Serialize实现的std::ops::Range结构完成序列化
• 添加flatten属性后，序列化输出的start、end字段会直接挂载在Reservation对象层级，和之前拆分字段的输出结构完全兼容

如果需要支持反序列化，可按照相同逻辑编写deserialize_with对应的反序列化函数，将输入的std::ops::Range转换回PgRange类型即可。

内容的提问来源于stack exchange，提问作者Blank