How to translate millis to date and group by month and year in Jooq?

I have the following SQL which works as expected:

select  YEAR(CONVERT_TZ(FROM_UNIXTIME(creation_date / 1000), @@session.time_zone, "Europe/Berlin")) as year,
    MONTH(CONVERT_TZ(FROM_UNIXTIME(creation_date / 1000), @@session.time_zone, "Europe/Berlin")) as month,
    sum(value)
from transaction
GROUP BY year, month; 

I’m trying to recreate these SQL in Jooq, but I don’t know how to create a Date object from the milliseconds I have as creation_date in my database.

dsl.select(DSL.month(DSL.date(TRANSACTION.CREATION_DATE)), // This does not work
           DSL.year(DSL.date(TRANSACTION.CREATION_DATE)), // This does not work
           DSL.sum(TRANSACTION.VALUE))
       .from(TRANSACTION)
       .groupBy(???); // How to group by month and year?

Answer

A common confusion when writing SQL GROUP BY is the logical order of SQL operations. While syntactically, SELECT seems to appear before GROUP BY, logically the order is inverse. This means that you cannot really reference columns in the SELECT clause from the GROUP BY clause.

Some dialects may have implemented exceptions “for convenience”, but this is generally very confusing. I recommend not doing that.

But to solve your problem:

Producing the SQL you wanted to produce

While in your original SQL query, you used aliases for your two expressions (AS year and AS month), in the jOOQ query you did not. I recommend you use aliases as well, and assign the column expressions to local variables for reuse in the groupBy() clause:

Field<?> month = DSL.month(DSL.date(TRANSACTION.CREATION_DATE)).as("month");
Field<?> year  = DSL.year(DSL.date(TRANSACTION.CREATION_DATE)).as("year");

dsl.select(month, year, DSL.sum(TRANSACTION.VALUE))
   .from(TRANSACTION)
   .groupBy(month, year)
   .fetch();

Aliased columns produce the full declaration in the SELECT clause, but only the alias in all other clauses, so this does exactly what you wanted:

SELECT
  month(date(transaction.creation_date)) as month,
  year(date(transaction.creation_date)) as year
  sum(transaction.value)
FROM transaction
GROUP BY
  month,
  year;

A better SQL statement according to the logical order of operations

If you want your SQL to remain portable and correct according to the logical order of SQL operations I’ve mentioned, I recommend you write this instead:

Field<?> month = DSL.month(DSL.date(TRANSACTION.CREATION_DATE));
Field<?> year  = DSL.year(DSL.date(TRANSACTION.CREATION_DATE));

dsl.select(month.as("month"), year.as("year"), DSL.sum(TRANSACTION.VALUE))
   .from(TRANSACTION)
   .groupBy(month, year)
   .fetch();

Notice that I’ve moved the aliasing to the SELECT clause, whereas in the GROUP BY clause, I’m now referencing the full column expressions. This will produce thef ollowing query:

SELECT
  month(date(transaction.creation_date)) as month,
  year(date(transaction.creation_date)) as year
  sum(transaction.value)
FROM transaction
GROUP BY
  month(date(transaction.creation_date)),
  year(date(transaction.creation_date));

The full expressions are now expanded into the GROUP BY clause, without you having to repeat them manually.