代码之家 › 专栏 › 技术社区 › Sebastiaan van den Broek

当使用JPA和Hibernate时,与PostgreSQL date_trunc('day',(entity.date在时区'UTC')函数相当的JPQL是什么

jpql jpa hibernate spring java

Sebastiaan van den Broek · 技术社区 · 7 年前

数据库函数的JPQL/JPA/Hibernate等价物是什么 date_trunc('day', (entity.date AT TIME ZONE 'UTC')) ?

我在Hibernate论坛上发现了一篇没有回复的古老帖子: https://forum.hibernate.org/viewtopic.php?f=1&t=990451

HQL equivalent of Postgres' "datetime at time zone"

"SELECT NEW " + AggregateQueryEntity.class.getName() +
                "(date_trunc('" + aggregationPeriod.toString() +
                "', a.date), a.alertConfiguration.id.id, a.alertConfiguration.name, a.alertLevel, count(*)) from Alert a" +
                whereClause.toString() +
                " GROUP BY 1, a.alertConfiguration.id.id, a.alertConfiguration.name, a.alertLevel" +    //column 1 is the truncated date
                " ORDER BY 1, alertLevel DESC"

Vlad的答案很好,我对它进行了一些修改,以将日期部分作为变量。此外,我还必须重写生成 Date timestamp without timezone 并明确提出 timestamp with timezone

@Column(columnDefinition = "TIMESTAMP WITH TIME ZONE") private Date date;

如需进一步阅读,请参阅 https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_timestamp_.28without_time_zone.29

使用Hibernate属性仍然是一个好主意 hibernate.jdbc.time_zone https://vladmihalcea.com/how-to-store-date-time-and-timestamps-in-utc-time-zone-with-jdbc-and-hibernate/ 并在UTC中启动JVM。为了解决我所有的时区问题,我也不得不这么做。

1 回复 | 直到 7 年前

Vlad Mihalcea 5 年前

Hibernate User Guide ,余可以使用 EXTRACT 功能:

List<Integer> days = entityManager
.createQuery(
    "select extract( day from c.timestamp ) " +
    "from Call c ", Integer.class )
.getResultList();

或者 day 功能:

List<Integer> days = entityManager
.createQuery(
    "select day( c.timestamp ) " +
    "from Call c ", Integer.class )
.getResultList();

`MetadataBuilderContributor`

如果您需要使用 AT TIMEZONE

public class SqlFunctionsMetadataBuilderContributor
        implements MetadataBuilderContributor {

    @Override
    public void contribute(MetadataBuilder metadataBuilder) {
        metadataBuilder.applySqlFunction(
                "date_trunc",
                new SQLFunctionTemplate(
                        StandardBasicTypes.TIMESTAMP,
                        "date_trunc('day', (?1 AT TIME ZONE 'UTC'))"
                )
        );
    }
}

配置 `MetadataBuilderContributor` 使用JPA persistence.xml

SqlFunctionsMetadataBuilderContributor 冬眠 hibernate.metadata_builder_contributor 配置属性:

<property>
    name="hibernate.metadata_builder_contributor" 
    value="com.vladmihalcea.book.hpjp.hibernate.query.function.SqlFunctionsMetadataBuilderContributor"
</property>

使用弹簧靴

如果您使用的是Spring Boot,则需要将以下配置添加到 application.properties

spring.jpa.properties.hibernate.metadata_builder_contributor=com.vladmihalcea.book.hpjp.hibernate.query.function.SqlFunctionsMetadataBuilderContributor

测试数据

那么,假设你有以下几点 Post 数据库中的实体:

Post post = new Post();
post.setId(1L);
post.setTitle(
    "High-Performance Java Persistence"
);
post.setCreatedOn(
    Timestamp.valueOf(
        LocalDateTime.of(2018, 11, 23, 11, 22, 33)
    )
);

entityManager.persist(post);

你可以打电话给 date_trunc

Tuple tuple = entityManager
.createQuery(
    "select p.title as title, date_trunc(p.createdOn) as creation_date " +
    "from Post p " +
    "where p.id = :postId", Tuple.class)
.setParameter("postId", 1L)
.getSingleResult();

assertEquals(
    "High-Performance Java Persistence", 
    tuple.get("title")
);

assertEquals(
    Timestamp.valueOf(
        LocalDateTime.of(2018, 11, 23, 0, 0, 0)
    ), 
    tuple.get("creation_date")
);

自定义传递到日期的时区\u TRUNC

如果要自定义时区,只需将时区作为如下参数传递:

public static class SqlFunctionsMetadataBuilderContributor
        implements MetadataBuilderContributor {

    @Override
    public void contribute(MetadataBuilder metadataBuilder) {
        metadataBuilder.applySqlFunction(
                "date_trunc",
                new SQLFunctionTemplate(
                        StandardBasicTypes.TIMESTAMP,
                        "date_trunc('day', (?1 AT TIME ZONE ?2))"
                )
        );
    }
}

日期 功能如下:

Tuple tuple = entityManager
.createQuery(
    "select p.title as title, date_trunc(p.createdOn, :timezone) as creation_date " +
    "from Post p " +
    "where p.id = :postId", Tuple.class)
.setParameter("postId", 1L)
.setParameter("timezone", "UTC")
.getSingleResult();

测试用例

我在我的高性能java persistence GitHub存储库中创建了以下测试用例,它们运行良好:

karthiks3000 6 年前

function('date_trunc', 'month', date)

例如-

select distinct w from YourModel w where studentId=:studentId and function('date_trunc', 'month', date) = :month

Jacob van Lingen 5 年前

除了@karthiks3000 answer之外,如果您使用Hibernates PostgreSQL方言,您只需在JPQL查询中调用其函数:

date_trunc('month', date)

How do you call a PostgreSQL function with Hibernate?