Help: HQL query to select first records in time interval.

mneethling · **Posted:** Fri Sep 05, 2008 3:38 am

Hi

I have a situation where devices in the field send their readings and is persisted every hour. The devices measure consumption with a counter (cumulative). This counter reading is persisted every hour.
The persistant entity is as follows:

Code:

class Reading {
 int deviceId;
 int reading;
 Date timestamp;
}

I need to report on the total consumption of all the devices during a time interval (say, a day), and how many devices' reading i used.
To get an accurate report, for each device, the difference between the first reading (minimum reading) of the current time interval and the next time interval must be calculated.
I wrote a query which gives me the first reading (smallest reading ) for each device for a time interval. But now I need to get te second reading as well, which will be the smallest reading after the nextIntervalStart time. I don't know if a subquery is the answer?

Current query:

Code:

select r.deviceId, min(r.reading),
from Reading r 
where r.timestamp >= :intervalStart
and r.timestamp < :nextIntervalStart
group by r.deviceId

It would be nice to have an HQL query which returns the device id, and 2 readings (the first reading of each interval per device).

Query result wanted:

Code:

deviceId, reading1, reading2
1, 2000, 2100
2, 1000, 1150
3, 1560, 1800

Or a query which returns device id and consumption delta.

Code:

deviceId, consumption
1, 100
2, 150
3, 240

Thanks, any help will be appreciated.