Group by time with postgres and jooq

Question

How to group by minute, hour, day, or week in jooq. I have found out that in postgres we can use date_trunc('minute', created_at) function for that.

The simplified SQL I am using for that is:

select date_trunc('day', created_at) as date,
       sum(time_spent) as time_spent,
from progress 
group by date

How can I achieve the equivalent with jooq?

Answer 1

As always, if you're missing support for some vendor-specific functionality, you can easily build it yourself using plain SQL templating

public static <T> Field<T> dateTrunc(String datePart, Field<T> field) {
    return DSL.field("date_trunc({0}, {1})", 
        field.getDataType(), DSL.inline(datePart), field);
}