How do I use Postgres's to_char in a jOOQ query?

Question

I am trying to convert the following PostgreSQL query to jOOQ:

SELECT count(*), to_char(created_date, 'YYYY-MM-DD') as year_month_date
  FROM log
  GROUP BY year_month_date
  ORDER BY year_month_date

What I have is:

jooq.select(
    DSL.count(),
    DSL.field("to_char(created_date, 'YYYY-MM-DD') as year_month_date")
  )
  .from(LOG)
  .groupBy(DSL.field("year_month_date"))
  .orderBy(DSL.field("year_month_date"))
  .fetch();

Is there a way to do using jOOQ's fluent API so I don't have to use strings?

Answer 1

Using TO_CHAR()

There is a pending feature request to add support for vendor specific to_char() functions: https://github.com/jOOQ/jOOQ/issues/8381

In order to standardise on such a function, more research needs to be done to be sure we can cover everything each vendor implements here, as the formatting logic is unfortunately quite vendor specific, and stringly typed.

So, if you want to use to_char(), currently, you will have to resort to using plain SQL templating, which you already did. You could obviously factor out this utility in a reusable form, such as:

public static Field<String> toChar(Field<?> date, String format) {
    return DSL.field("to_char({0}, {1})", SQLDataType.VARCHAR, date, DSL.inline(format));
}

Truncating dates

Of course, in your particular query, you could also resort to using standard SQL features, such as CAST(). I think that what you're trying to do is truncate time information from your timestamp or timestamptz column, so you could do this instead:

SELECT count(*), CAST (created_date AS DATE) d
  FROM log
  GROUP BY d
  ORDER BY d

Or with jOOQ:

Field<Date> d = LOG.CREATED_DATE.cast(SQLDataType.DATE);

jooq.select(count(), d)
    .from(LOG)
    .groupBy(d)
    .orderBy(d)
    .fetch();