JOOQ how query for between dates

Question

I'm using jooq in my project and I need to query some data between two dates.

The sql query which produces right data is

select created_on from queue_token where created_on between '2015-07-16' and '2015-07-17' and token_queue_id=1;

the equivalent jooq query which i have written is below but doesn't give out the required result

 create.select().from(com.database.tables.QueueToken.QUEUE_TOKEN)
                   .where(com.database.tables.QueueToken.QUEUE_TOKEN.TOKEN_QUEUE_ID.equal(1))
                    .and(com.database.tables.QueueToken.QUEUE_TOKEN.CREATED_ON.between(new Timestamp(fromDate.getTime())).and(new Timestamp(toDate.getTime())))
                    .fetch();

The jooq query produces result but only produces records that exactly match the fromDate. So basically it's not working for the date range.

Can somebody help here?

Answer 1

I think the problem is in passing a timestamp or date and time (I do not know java well). So instead sending e.x. "2015-07-16", you get "2015-07-16 12:55:00" or "1436187300".

Try debug the value of new Timestamp(fromDate.getTime()) first and if I'm right, try to convert it to a simple date without time.

To getting correct date value without time you can use:

• Java 8 package java.time LocalDate https://docs.oracle.com/javase/8/docs/api/java/time/LocalDate.html

• or lib Joda Time http://joda-time.sf.net/