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

I'm sure there should be smth less monstrous then:

t=# select
  (extract('DAY' from now()-'1970-01-01')*60*60*24 + extract(seconds from now())+ extract(minutes from now())*60 + extract(hours from now())*60*60)
  -
  extract(epoch from now())
;
 ?column?
----------
        0
(1 row)

Time: 0.315 ms

Answer 2

Just wanted to add there's org.jooq.impl.DSL.timestampDiff(...) in case you needed the epoch to calculate difference between two timestamps in milliseconds.

Answer 3

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