cannot do a where clause with jooq DSL in Kotlin/Java

Question

I am trying to run a query of the following form with jooq in Kotlin:

val create = DSL.using(SQLDialect.POSTGRES)
val query: Query = create.select().from(DSL.table(tableName))
            .where(DSL.field("timestamp").between("1970-01-01T00:00:00Z").and("2021-11-05T00:00:00Z"))
            .orderBy(DSL.field("id").desc())

The code above gives me:

syntax error at or near \"and\

Also, looking at this query in the debugger, the query.sql renders as:

select * from data_table where timestamp between ? and ? order by id desc

I am not sure if the ? indicates that it could not render the values to SQL or somehow they are some sort of placeholders..

Also, the code works without the where chain.

Additionally, on the Postgres command line I can run the following and the query executes:

select * from data_table where timestamp between '1970-01-01T00:00:00Z' and '2021-11-05T00:00:00Z' order by id

Querying the datatypes on the schema, the timestamp column type is rendered as timestamp without time zone.

Before I had declared variables as:

val lowFilter = "1970-01-01T00:00:00Z"
val highFilter = "2021-11-05T00:00:00Z"

and this did not work and it seems passing raw strings does not work either. I am very new to this, so I am pretty sure I am messing up the usage here.

EDIT Following @nulldroid suggestion, did something like:

.where(DSL.field("starttime").between(DSL.timestamp("1970-01-01T00:00:00Z")).and(DSL.timestamp("2021-11-05T00:00:00Z")))

and this resulted in:

Type class org.jooq.impl.Val is not supported in dialect POSTGRES"

user_521799 · Answer

Not using the code generator:

I'm going to assume you have a good reason not to use the code generator for this particular query, the main reason usually being that your schema is dynamic.

So, the correct way to write your query is this:

create.select()
      .from(DSL.table(tableName))

      // Attach a DataType to your timestamp field, to let jOOQ know about this
      .where(DSL.field("timestamp", SQLDataType.OFFSETDATETIME)

          // Use bind values of a temporal type
          .between(OffsetDateTime.parse("1970-01-01T00:00:00Z"))
          .and(OffsetDateTime.parse("2021-11-05T00:00:00Z")))
      .orderBy(DSL.field("id").desc())

Notice how I'm using actual temporal data types, not strings to compare dates and declare fields.

I'm assuming, from your question's UTC timestamps, that you're using TIMESTAMPTZ. Otherwise, if you're using TIMESTAMP, just replace OffsetDateTime by LocalDateTime...

Using the code generator

If using the code generator, which is always recommended if your schema isn't dynamic, you'd write almost the same thing as above, but type safe:

create.select()
      .from(MY_TABLE)

      // Attach a DataType to your timestamp field, to let jOOQ know about this
      .where(MY_TABLE.TIMESTAMP

          // Use bind values of a temporal type
          .between(OffsetDateTime.parse("1970-01-01T00:00:00Z"))
          .and(OffsetDateTime.parse("2021-11-05T00:00:00Z")))
      .orderBy(MY_TABLE.ID.desc())

cannot do a where clause with jooq DSL in Kotlin/Java

1 Answers

Not using the code generator:

Using the code generator