JPA原生查询报错：pg_catalog.timezone函数不存在

问题根源 #

错误核心是Spring Data JPA将ZoneId类型的参数zoneId序列化成了PostgreSQL无法识别的bytea（二进制）类型，而PostgreSQL的AT TIME ZONE子句需要字符串格式的时区标识符（比如'Asia/Shanghai'、'UTC'），导致数据库找不到匹配参数类型的timezone函数。

解决方案 #

有两种直接的修复方式：

方式1：将方法参数改为String类型 #

把仓库方法中的ZoneId zoneId改成String zoneId，调用时传入ZoneId的id属性（比如zoneId.id）：

@Query("""
            SELECT SUM(p.total) AS total,
                   date_trunc('day', p.date_time AT TIME ZONE 'UTC' AT TIME ZONE :zoneId) AS date
            FROM xyz p
            WHERE p.employee_id = :id
              AND p.date_time >= :startDate
              AND p.date_time <= :endDate
              AND p.date_time <= now()
              AND p.status in :statuses
            GROUP BY date
            ORDER BY date DESC
  """, nativeQuery = true)
fun get(id: Int, zoneId: String, startDate: ZonedDateTime, endDate: ZonedDateTime, statuses: List<PaymentStatus>) : List<DTO>

调用示例：

val zoneId = ZoneId.of("Asia/Shanghai")
repo.get(1, zoneId.id, startZdt, endZdt, listOf(PaymentStatus.SUCCESS))

方式2：在查询中显式转换ZoneId参数类型 #

如果要保留ZoneId参数，在查询里用CAST(:zoneId AS text)把二进制参数转成字符串：

@Query("""
            SELECT SUM(p.total) AS total,
                   date_trunc('day', p.date_time AT TIME ZONE 'UTC' AT TIME ZONE CAST(:zoneId AS text)) AS date
            FROM xyz p
            WHERE p.employee_id = :id
              AND p.date_time >= :startDate
              AND p.date_time <= :endDate
              AND p.date_time <= now()
              AND p.status in :statuses
            GROUP BY date
            ORDER BY date DESC
  """, nativeQuery = true)
fun get(id: Int, zoneId: ZoneId, startDate: ZonedDateTime, endDate: ZonedDateTime, statuses: List<PaymentStatus>) : List<DTO>

额外注意点 #

• 确保date_time字段类型是PostgreSQL的timestamptz（timestamp with time zone），否则AT TIME ZONE的行为可能不符合预期。
• ZonedDateTime参数传入PostgreSQL时，Spring Data JPA会自动处理成合适的数据库类型，无需额外转换。

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