HQL按时间单位(小时/天/月)选择总和和时间组，然后 [英] HQL select sum and time group by time unit (hour/day/month) and

问题描述

我有一个这样的表:

COLUMN               TYPE
------------------------------
ID                   INTEGER
VALUE                INTEGER
TIME                 TIMESTAMP

如何使用HQL编写查询，选择按时间单位分组的值列之和(例如，按天分组)，然后选择此时间单位作为第二列.

How can I write query with HQL selecting sum of value column groupped by time unit(f.e. groupped by day) and selecting this time unit as a second column.

我试图做到这一点，并得到了这样的东西: 但是HQL中没有parsedatetime函数，所以我不知道现在如何获取正确的查询.

I have tried to do it and got something like this: but there is no parsedatetime function in HQL so I have no idea how can I get the proper query now.

select sum(value),
parsedatetime(day(time) || '.' || month(time) || '.' || year(time) || ' ' || hour(time) ||':00:00', 'dd.MM.yy hh:mm:ss')
as xtime 
from Table 
group by time

我希望此查询返回具有2个字段的对象:int和java.sql.Date.

I want this query to return object with 2 fields: int and java.sql.Date.

推荐答案

您要对整个datetime值进行分组，这就是为什么您没有预期的结果，必须按所需的datetime对其进行分组，然后尝试一下如下所示:

You are grouping by whole the datetime value, that's why you haven't expected result, you must group it by the piece of datetime that you want, try something like below:

select sum(value),
day(time) as xtime 
from Table 
group by day(time)

这篇关于HQL按时间单位(小时/天/月)选择总和和时间组，然后的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！