mysql - JOOQ date field query consider one day less of the provided date parameters

Question

I'm having this strange issue where the jooq query on a mysql date field counts one day less than the given parameter.

I have person table and I want to query the person created between two dates say 2021-02-01 and 2021-02-05.

mysql> desc person;
+--------------+--------------------------------+------+-----+-------------------+-----------------------------------------------+
| Field        | Type                           | Null | Key | Default           | Extra                                         |
+--------------+--------------------------------+------+-----+-------------------+-----------------------------------------------+
| first_name   | varchar(255)                   | NO   |     | NULL              |                                               |
| created_date | date                           | YES  |     | NULL              |                                               |
+--------------+--------------------------------+------+-----+-------------------+-----------------------------------------------+    

mysql> select first_name , created_date from person order by created_date desc;
+------------+--------------+
| first_name | created_date |
+------------+--------------+
| john       | 2021-02-05   |
| joe        | 2021-02-04   |
| andrew     | 2021-02-01   |
| mike       | 2021-01-31   |
+------------+--------------+
4 rows in set (0.00 sec)

If I query this with plain sql I get following correct result set

mysql> select first_name , created_date from person where created_date between '2021-02-01' and '2021-02-05' order by created_date desc;
+------------+--------------+
| first_name | created_date |
+------------+--------------+
| john       | 2021-02-05   |
| joe        | 2021-02-04   |
| andrew     | 2021-02-01   |
+------------+--------------+
3 rows in set (0.01 sec)

But when I query this with jooq I get data for the date range 2021-01-31 and 2021-02-04. That means one day less than the provided parameters

jooq.dsl()
.select(Tables.PERSON.FIRST_NAME, Tables.PERSON.CREATED_DATE)
.from(Tables.PERSON)
.where(Tables.PERSON.CREATED_DATE.between(LocalDate.parse("2021-02-01"), LocalDate.parse("2021-02-05")))
.orderBy(Tables.PERSON.CREATED_DATE.desc())
.fetch()

result = {ResultImpl@13870}  size = 3
 0 = {RecordImpl2@13883} "|first_name|created_date|
+----------+------------+
|joe       |2021-02-04  |"
 1 = {RecordImpl2@13884} "|first_name|created_date|
+----------+------------+
|andrew    |2021-02-01  |"
 2 = {RecordImpl2@13885} "|first_name|created_date|
+----------+------------+
|mike      |2021-01-31  |"

This is the jooq generated TableField

 /**
 * The column <code>person.created_date</code>.
 */
public final TableField<PersonRecord, LocalDate> CREATED_DATE = createField(DSL.name("created_date"), SQLDataType.LOCALDATE, this, "");

what am I doing wrong here. Can someone help me

question from:https://stackoverflow.com/questions/66058871/jooq-date-field-query-consider-one-day-less-of-the-provided-date-parameters

Answer 1

Waitting for answers