JOOQ and TimescaleDB - How to implement a basic time_bucket statement in JOOQ

Question

I would like to implement a basic time_bucket statement in JOOQ.

If I run this Statement in the console it works perfekly fine:

SELECT time_bucket('5 minutes', time) as t, avg(average)
from TABLE
GROUP BY t

If i try to implement it with JOOQ I have the Problem that the generated routines do not work, because I cannot add the intervals.

I want something like the following code, but the Interval '5 minutes' doesn't work:

dslContext.select(timeBucket('5 minutes', TABLE.TIME), avg(TABLE.AVERAGE))
                .from(TABLE)
                .groupBy(timeBucket8('5 minutes', TABLE.TIME))
                .fetch())

Is there a simple way to implement the Intervals?

What exactly doesn't work? I'm assuming you've written some [plain SQL templating functions](https://www.jooq.org/doc/latest/manual/sql-building/plain-sql-templating) like `timeBucket()`. What's the implementation there? — Lukas Eder, Nov 19 '18 at 11:09

score 1 · Answer 1 · answered May 13 '20 at 10:53

SQL:

create table mytable (moment timestamp with timezone, ...)
select create_hypertable('mytable', 'moment', chunk_time_interval => INTERVAL '5 minute');

Select with jooq:

import org.jooq.types.YearToSecond
import java.time.Duration
import org.jooq.impl.DSL.count
import org.jooq.impl.DSL.field

// custom function to combine arguments Duration and OffsetDateTime for TimeBucket2
private fun timeBucket(d: Duration, field: Field<OffsetDateTime>): Field<OffsetDateTime> {
    val f = TimeBucket2()
    f.setBucketWidth(YearToSecond.valueOf(d))
    f.setTs(field)
    return f.asField()
}

// field name alias
val BUCKET = field("bucket", OffsetDateTime::class.java)
val COUNT = field("count", Int::class.java)

val ctx = DSL.using(conn, SQLDialect.POSTGRES, settings)

val recs = ctx.select(
        timeBucket(Duration.ofMinutes(5), MYTABLE.MOMENT).`as`(BUCKET),
        count().`as`(COUNT)
).from(MYTABLE)
        .groupBy(BUCKET)
        .fetch()

for (rec in recs) {
    println(rec[BUCKET] + " => " + rec[COUNT])
}

JOOQ and TimescaleDB - How to implement a basic time_bucket statement in JOOQ

1 Answers1