如何用统一SQL语句在H2与PostgreSQL中截断时间戳的时间部分？

针对你遇到的JPQL中日期截断函数在PostgreSQL（date()）和H2（trunc()）不兼容的问题，以下是无需修改业务查询代码的可行方案：

方案1：扩展Hibernate方言，注册统一函数名 #

通过自定义数据库方言，让Hibernate自动将统一的函数名转换为对应数据库的原生函数：

1. 自定义PostgreSQL方言

public class CustomPostgreSQLDialect extends PostgreSQLDialect {
    public CustomPostgreSQLDialect() {
        super();
        // 注册统一函数trunc_date，映射到PostgreSQL的date()
        registerFunction("trunc_date", new StandardSQLFunction("date", StandardBasicTypes.DATE));
    }
}

2. 自定义H2方言

public class CustomH2Dialect extends H2Dialect {
    public CustomH2Dialect() {
        super();
        // 注册统一函数trunc_date，映射到H2的trunc(..., 'day')
        registerFunction("trunc_date", new StandardSQLFunction("trunc", StandardBasicTypes.DATE) {
            @Override
            public String render(Type firstArgType, List<?> args, SessionFactoryImplementor factory) {
                return "trunc(" + args.get(0) + ", 'day')";
            }
        });
    }
}

3. 配置方言
   在环境配置文件中指定对应方言：

• 生产环境：hibernate.dialect=com.yourpackage.CustomPostgreSQLDialect
• 测试环境：hibernate.dialect=com.yourpackage.CustomH2Dialect

4. 统一使用查询
   所有JPQL/HQL中用trunc_date(timestampfield)替代原有的date()或trunc()，Hibernate会自动适配数据库。

方案2：给H2添加兼容PostgreSQL的date()函数 #

直接在H2初始化时创建date()函数，让它的行为和PostgreSQL一致：

在测试环境的H2初始化SQL脚本中添加：

-- 定义和PostgreSQL兼容的date()函数
CREATE FUNCTION DATE(t TIMESTAMP) RETURNS DATE
RETURN TRUNC(t, 'DAY');

或者用Java别名实现：

CREATE ALIAS IF NOT EXISTS DATE AS $$
java.sql.Date convertToDate(java.sql.Timestamp ts) {
    return new java.sql.Date(ts.getTime());
}
$$;

这样你就可以在H2中直接使用date(timestampfield)，和生产环境的PostgreSQL查询完全一致，无需修改任何代码。

方案3：使用JPA Criteria API构建查询 #

如果是新开发的查询，用Criteria API的日期截断方法，让JPA自动生成适配数据库的SQL：

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<SomeEntity> query = cb.createQuery(SomeEntity.class);
Root<SomeEntity> root = query.from(SomeEntity.class);

// 用cb.truncate实现日期截断，自动适配数据库
Predicate dateMatch = cb.equal(
    cb.truncate(root.get("timestampfield"), TemporalUnit.DAY),
    cb.literal(targetDate)
);

query.where(dateMatch);
List<SomeEntity> result = entityManager.createQuery(query).getResultList();

Hibernate会根据当前方言，将cb.truncate(..., DAY)转换为PostgreSQL的date()或H2的trunc(..., 'day')。

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