jOOQ "EXTRACT(EPOCH FROM [field])" workaround?

Question

There's syntax that allows transforming a Timestamp into various date parts, including the unix epoch. This works as follows (in lastest PostgreSQL at least):

SELECT EXTRACT(EPOCH FROM "ts") FROM...

However, jOOQ doesn't seem to support this syntax, as evidenced by this discussion I found, which links to the still open Issue #2132 on the jOOQ github.

What workarounds are there for this? How can I emulate this behavior within jOOQ's syntax (i.e. without having to write the entire query in pure SQL)?

Answer 1

Workaround for jOOQ 3.10 and less

You can always resort to plain SQL with jOOQ:

public static Field<Integer> extractEpochFrom(Field<Timestamp> field) {
    return DSL.field("extract(epoch from {0})", Integer.class, field);
}

Support in jOOQ 3.11 and more

There is currently (jOOQ 3.11) experimental support for additional, non standard DatePart types, such as DatePart.EPOCH. It might work already with PostgreSQL, but not with other databases.

This support will be improved in future versions, including jOOQ 3.12, see: https://github.com/jOOQ/jOOQ/issues/7794