JOOQ as a request between dates

Question

I am using jooq in my project and I need to request some data between two dates.

A sql query that produces the correct data,

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

equivalent jooq-request, which I wrote below, but does not give 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(); 

A jooq query produces a result, but only produces records that exactly match the fromDate value. So basically this does not work for a date range.

Can anyone help here?

Answer 1

I think the problem is passing the timestamp or date and time stamp (I don't know java well). Therefore, instead of sending ex "2015-07-16", you get "2015-07-16 12:55:00" or "1436187300".

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

To get the 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/