Group by date using a datetime field with JOOQ

Question

I have a table called delivery and datetime field delivery_time.

+--------------------+------------------------------------------+------+-----+-------------------+
| Field              | Type                                     | Null | Key | Default           |
+--------------------+------------------------------------------+------+-----+-------------------+
| delivery_time      | datetime                                 | YES  |     | NULL              |

I want to count deliveries for each date. Using plain sql I can do this

select  CAST(delivery_time as DATE) as date, count(*) as count from delivery group by CAST(delivery_time as DATE);

+------------+-------+
| date       | count |
+------------+-------+
| 2021-04-21 |     1 |
| 2021-03-22 |    11 |
| NULL       |     3 |
| 2021-03-21 |     1 |
| 2021-04-22 |     2 |
| 2021-04-30 |     1 |
+------------+-------+

But when I try to do this using JOOQ it's not working properly (Only null row is returned)

jooq.dsl()
.select(
        date(Tables.DELIVERY.DELIVERY_TIME.toString()),
        count()
)
.from(Tables.DELIVERY)
.groupBy(date(Tables.DELIVERY.DELIVERY_TIME.toString()))
.fetch()

Can someone help me to write this query with jooq

Answer 1

You're using the Object.toString() method, which is available on all Java objects for debugging purposes. In jOOQ, you're going to get a string representation of your column expression. There's no point in doing that in your case. Just use the DSL.cast() method, as you did with SQL.

Assuming the usual static imports:

import static org.jooq.impl.DSL.*;
import static org.jooq.impl.SQLDataType.*;
import static com.example.Tables.*;

Write

jooq.dsl()
    .select(cast(DELIVERY.DELIVERY_TIME, DATE), count())
    .from(DELIVERY)
    .groupBy(cast(DELIVERY.DELIVERY_TIME, DATE))
    .fetch();