postgresql - 按JPA中的截断日期分组

我需要帮助！
我需要为

SELECT date_trunc('day', start_time)
FROM Example
GROUP BY date_trunc('day', start_time)

（PostgreSQL）
我有密码：

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Object[]> query = cb.createQuery(Object[].class);

Root<Example> root = query.from(Example.class);

Expression<Date> exp = cb.function("date_trunc", Date.class , cb.literal("day"), root.get("startTime"));

List<Object[]> result = entityManager.createQuery(query.select(exp).groupBy(exp)).getResultList();

但我得到的解释是：

Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2122)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1905)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1881)
    at org.hibernate.loader.Loader.doQuery(Loader.java:925)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:342)
    at org.hibernate.loader.Loader.doList(Loader.java:2622)
    at org.hibernate.loader.Loader.doList(Loader.java:2605)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2434)
    at org.hibernate.loader.Loader.list(Loader.java:2429)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:501)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:371)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1339)
    at org.hibernate.internal.QueryImpl.list(QueryImpl.java:87)
    at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:606)
    at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:483)
    ... 122 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: column "examplei0_.start_time" must appear in the GROUP BY clause or be used in an aggregate function
  Position: 69

我做错什么了？
休眠sql:

select date_trunc(?, examplei0_.start_time) as col_1_0_ from examplei0_ group by date_trunc(?, examplei0_.start_time)

如果我在数据库控制台中执行它，它就会工作。

最佳答案

我猜Postgres看到了
select date_trunc(?, examplei0_.start_time) as col_1_0_ from examplei0_ group by date_trunc(?, examplei0_.start_time)
拒绝，因为第一个date_trunc(?, examplei0_.start_time)不一定与第二个date_trunc(?, examplei0_.start_time)相同，而不查看传入的实际参数。
如果是这样，您需要hibernate生成一个查询，其中'day'没有参数化。或者，在postgresdate_trunc_day(timestamp)中创建一个调用date_trunc('day', timestamp)的函数，并改为调用新函数。

关于postgresql - 按JPA中的截断日期分组，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/48994080/