CODEWITHSUNDEEP
mysqlunixtimestampjooq
Kevin
Kevin

Reputation: 168

Querying Unix Timestamp using JOOQ

I'm using a MySQL server, and I'm trying to retrieve a timestamp using the JOOQ API. However, I think the default JOOQ behavior returns the time in the local timezone, and not UTC (it's not using UNIX_TIMESTAMP(). Using JOOQ, my query looks like

db.select(USER_TABLE.REGISTERED_ON)
    .from(USER_TABLE)
    .where(USER_TABLE.EMAIL.equal(email)
    .fetchAny()

However, the sql that I want to execute should be something like this

SELECT UNIX_TIMESTAMP(schema.table.timestamp_col)
    FROM schema.table
    WHERE email="[email protected]"

Is it possible using the JOOQ API? If not, what's the best way to run this query, because I really want to be able to use the generated code (USER_TABLE, USER_TABLE.REGISTERED_ON, etc).

EDIT: I'm now doing the following, but is it safe? Basically I'm removing the quotations from JOOQ's generated classes.

String timestamp_field = USER_TABLE.REGISTERED_ON.toString().replace("\"", "");
Field<?> f = DSL.field("UNIX_TIMESTAMP(" + timestamp_field + ")");
Record r = db.select(f)
    .from(USER_TABLE)
    .where(USER_TABLE.EMAIL.equal(email))
    .fetchAny();

Upvotes: 2

Views: 1667

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220762

There are several ways to tackle this problem:

Use a Converter or Binding to convert the type

You can register a data type Converter or Binding in the source code generator. This way, the code generator will generate a Field<YourType> for every TIMESTAMP field in the database. Possible useful types are:

  • java.lang.Long
  • java.time.Instant
  • java.time.OffsetDateTime (note that jOOQ 3.7 will support this via [#4338])3

Use plain SQL

Use plain SQL every time you want to do an explicit conversion using the UNIX_TIMESTAMP() function. Your solution works:

String timestamp_field = USER_TABLE.REGISTERED_ON.toString().replace("\"", "");
Field<?> f = DSL.field("UNIX_TIMESTAMP(" + timestamp_field + ")");

But it is not recommended because:

  • You should never rely on any toString() implementation of any Java type.
  • You should generally try to avoid string concatenation with jOOQ's plain SQL API

A better solution would be:

DSL.field("UNIX_TIMESTAMP({0})", Long.class, USER_TABLE.REGISTERED_ON);

Or even:

public static Field<Long> unixTimestamp(Field<Timestamp> arg) {
    return DSL.field("UNIX_TIMESTAMP({0})", Long.class, arg);
}

Use BIGINT in the database

You could of course use BIGINT or BIGINT UNSIGNED in the database instead of TIMESTAMP. This way, you will always automatically have the integer unix timestamp value. This is just a workaround for completeness's sake.

Upvotes: 1

Related Questions