How to select unix timestamp as date in Jooq?

Question

I am working in with a database in which dates are stored as unix time (seconds since 1970). I have the following sql which works as expected:

select CONVERT_TZ(FROM_UNIXTIME(creation_date), @@session.time_zone, "Europe/Berlin")
from transaction; 

This is how I tried to do it in Jooq:

dsl.select(DSL.date(TRANSACTION.CREATION_DATE) // This does not work
   .from(TRANSACTION)
   .fetch();

Answer 1

You're using quite a few vendor specific functions there, which are not supported out of the box in jOOQ. As always, when you run into this situation, you can roll your own support by using plain SQL templating, ideally by building your own library:

public static Field<Timestamp> fromUnixtime(Field<? extends Number> field) {
    return field("from_unixtime({0})", SQLDataType.TIMESTAMP, field);
}

public static Field<Timestamp> convertTz(
    Field<Timestamp> field,
    Field<String> fromTz,
    Field<String> toTz
) {
    return field("convert_tz({0}, {1}, {2})", SQLDataType.TIMESTAMP, field, fromTz, toTz);
}

public static Field<String> sessionTimeZone() {
    return field("@@session.time_zone", SQLDataType.VARCHAR);
}

Now, you can use it like this:

dsl.select(convertTz(
        fromUnixtime(TRANSACTION.CREATION_DATE), 
        sessionTimeZone(), 
        inline("Europe/Berlin"))
   )
   .from(TRANSACTION)
   .fetch();

Or, take it a step further and wrap all of these expressions in yet another auxiliary function, like this one:

public static Field<Timestamp> fromUnixtimeToBerlinTZ(Field<? extends Number> field) {
    return convertTz(
        fromUnixtime(TRANSACTION.CREATION_DATE), 
        sessionTimeZone(), 
        inline("Europe/Berlin")
    );
}

All of these examples are assuming the usual static import:

import static org.jooq.impl.DSL.*;