如何在JPA Criteria中实现PostgreSQL时区转换SQL语句

你要实现的AT TIME ZONE逻辑可以通过JPA标准的CriteriaBuilder#function调用PostgreSQL内置timezone函数实现，二者语义完全等价：PostgreSQL中a AT TIME ZONE b和timezone(b, a)的执行结果完全一致。

具体实现步骤 #

假设你的实体类为MyTable，对应表mytable，两个字段的映射如下：

• mytimestamptzcolumn：实体类属性类型为OffsetDateTime/Instant，对应数据库timestamptz类型
• mytimezonecolumn：实体类属性类型为String，存储时区ID（如Asia/Shanghai）

完整代码示例 #

// 1. 获取CriteriaBuilder和构造查询基础结构
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<MyTable> query = cb.createQuery(MyTable.class);
Root<MyTable> root = query.from(MyTable.class);

// 2. 构造时区转换表达式：等价于m.mytimestamptzcolumn AT TIME ZONE m.mytimezonecolumn
Expression<LocalDateTime> convertedTime = cb.function(
    "timezone",
    LocalDateTime.class,
    root.get("mytimezonecolumn"),
    root.get("mytimestamptzcolumn")
);

// 3. 构造比较条件
ParameterExpression<LocalDateTime> compareParam = cb.parameter(LocalDateTime.class, "targetTime");
query.where(cb.lessThan(convertedTime, compareParam));

// 4. 执行查询并传入参数
TypedQuery<MyTable> typedQuery = entityManager.createQuery(query);
typedQuery.setParameter("targetTime", 你要比较的LocalDateTime实例);
List<MyTable> resultList = typedQuery.getResultList();

注意事项 #

• 如果你使用的JPA实现对timezone函数返回类型识别有异常，可以把function的返回类型参数换成Timestamp.class，参数同步改为Timestamp类型即可
• 该实现全程使用JPA标准API，没有依赖Hibernate专属扩展，完全符合要求
• 最终生成的原生SQL执行效果和你给出的原始SQL完全一致

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