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

when looking at [this](https://www.jooq.org/doc/3.0/manual/reference/reference-data-types/data-types-oracle-date/) I think the problem might be in `date(Tables.DELIVERY.DELIVERY_TIME.toString())`, Did you try `CAST(Tables.DELIVERY.DELIVERY_TIME AS DATE)` ? (see: [docs](https://www.jooq.org/doc/3.14/manual/sql-building/column-expressions/datetime-functions/date-function/)) — Luuk, Apr 30 '21 at 12:25
according to documentation date() function is same as what CAST as DATE does. correct me if i'm wrong — Ruchira Nawarathna, Apr 30 '21 at 13:16
But I doubt that `date(DELIVERY_TIME.toString())` is equal to `CAST(DELIVERY_TIME AS DATE)`, that's why I commented. (I am not familiar with jooq) — Luuk, Apr 30 '21 at 14:01

score 1 · Accepted Answer · answered Apr 30 '21 at 18:06

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();

Group by date using a datetime field with JOOQ

1 Answers1