Spring Data JPA查询Postgres JSONB时间范围报错解决

一、解决语法错误（syntax error at or near ":"） #

问题根源是Spring Data JPA会把PostgreSQL的::timestamp类型转换语法中的冒号误识别为参数占位符，导致解析报错。

替换为PostgreSQL标准的CAST()函数即可避免冲突，修改后的SQL如下：

SELECT id, text_code as textCode, json_data as jsonData
FROM some_table
WHERE text_code = :textCode
    AND CAST(json_data ->> 'created' AS timestamp) BETWEEN :startDate AND :endDate

二、解决类型不匹配错误（operator does not exist: timestamp without time zone >= character varying） #

这个错误是因为传入的日期参数是字符串类型，和转换后的timestamp类型无法直接比较，PostgreSQL找不到对应的运算符。提供两种可行方案：

方案1：将参数改为时间类型，让JPA自动处理转换 #

把方法参数的String类型替换为LocalDateTime（或java.sql.Timestamp），Spring Data JPA会自动完成类型转换，无需手动处理格式：

@Query(
        nativeQuery = true,
        value = 
                """
                SELECT id, text_code as textCode, json_data as jsonData
                FROM some_table
                WHERE text_code = :textCode
                    AND CAST(json_data ->> 'created' AS timestamp) BETWEEN :startDate AND :endDate
               """
)
List<EntityData> getBetween(
        @Param("textCode") String textCode,
        @Param("startDate") LocalDateTime startDate,
        @Param("endDate") LocalDateTime endDate);

调用示例：

LocalDateTime start = LocalDateTime.of(2023, 10, 10, 0, 0, 0);
LocalDateTime end = LocalDateTime.of(2023, 10, 10, 23, 59, 59);
List<EntityData> dataList = yourRepository.getBetween("7845754645434878", start, end);

方案2：在SQL中显式转换参数类型 #

如果必须保留String类型参数，需要在SQL中把字符串参数也转换为timestamp类型，同时注意传入的字符串格式必须符合PostgreSQL要求（日期和时间用空格分隔，如'2023-10-10 00:00:00'，而非冒号分隔）：

SELECT id, text_code as textCode, json_data as jsonData
FROM some_table
WHERE text_code = :textCode
    AND CAST(json_data ->> 'created' AS timestamp) 
    BETWEEN CAST(:startDate AS timestamp) AND CAST(:endDate AS timestamp)

内容的提问来源于stack exchange，提问作者this.srivastava