group by time interval in h2 database
group-by, h2, sql
Solution
H2 supports a number of date and time functions, for example DATEDIFF and DATEADD. Example:
drop table if exists test;
create table test(id int primary key, ts timestamp);
insert into test
select x, dateadd(s, x, now()) from system_range(1, 100);
select dateadd(s, datediff(s, now(), ts) / 10 * 10, now()) as diff,
avg(id) from test group by diff order by diff;
Problem
Using a h2 database I want to get average values of a column while grouping entries based on a timestamp. My table looks like this: ``` TIME SID SERVICE TET 2012-11-21 11:31:45.433 164DSQL0L4JW7 service1 3868 2012-11-21 11:31:45.608 164DSS6TJS321 service2 350 2012-11-21 11:31:45.711 164DSROS9DNXA service2 285 2012-11-21 11:31:45.766 164DSH2PMPY4M service1 4044 2012-11-21 11:31:45.778 164DSOGRCKG8J service1 4248 2012-11-21 11:31:45.82 164DSRMV0QMFT service1 3766 2012-11-21 11:31:46.089 164DSP1L9W6MC service1 4257 2012-11-21 11:31:46.226 164DSOPUNUFJF service3 703 2012-11-21 11:31:46.752 164DSLJHST6E8 service3 411 2012-11-21 11:31:46.781 164DSHPI1T3J9 service3 393 2012-11-21 11:31:45.24 164DSQI4RY330 service2 235 2012-11-21 11:31:45.268 164DSPRDHROQN service2 197 2012-11-21 11:31:45.991 164DSRUUGQ8LN service2 380 2012-11-21 11:31:46.338 164DSMARDXMD3 service3 783 ``` I'd like to extract average values for the column TET grouped by the column TET. I want to have the interval flexible (e.g. 15 seconds and for another query 5 minutes). I found this question which is pretty much what I need, but unfortunately I can't get any of the solutions to work in H2 (there's no strftime).