4

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)?

Tomáš M.
  • 752
  • 7
  • 24
  • if the problem is with `EPOCH` you can try extract `DAY` and multiply it by 60*60*24?.. – Vao Tsun Feb 02 '17 at 14:28
  • I would lose a good bit of precision by that. – Tomáš M. Feb 02 '17 at 14:38
  • `(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)` gives exactly same as EXTRACT(EPOCH FROM now()) - see my answer?.. – Vao Tsun Feb 02 '17 at 14:39
  • if you can use any functions, you could call `date_part('epoch', "ts")` instead (they are the same in PostgreSQL). – pozs Feb 02 '17 at 15:11

3 Answers3

5

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

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
3

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.

Dmitry Gusev
  • 881
  • 8
  • 15
-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
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • 1: `extract()` has a special syntax, it cannot accept values before `from`, just identifiers. 2. `day` is day-of-month, i.e. it is `2` in case of `2017-02-02`, which is far from usable instead of `epoch`. – pozs Feb 02 '17 at 15:08
  • ah. I see - I thought it does not accept EPOCH only. Thank you – Vao Tsun Feb 02 '17 at 15:11
  • Just noticed, that you `extract` from an `interval`, which is generated by subtracting 2 timestamps. This is a special case, which generates an interval with maximum `days` resolution, so your formula could work. But usually it is dangerous to extract days from timestamp/interval, consider f.ex. `extract(day from interval '2 years 2 days')` – pozs Feb 02 '17 at 15:17
  • yes - this is why I summarize extract days*nnn extract hours*nnn etc – Vao Tsun Feb 02 '17 at 15:20
  • Ah, and 9.6 accepts the quoted syntax too (which is not standard, and older versions did not supported it, at least when I last tried it threw me syntax errors -- the docs still not state that it is accepted though). – pozs Feb 02 '17 at 15:21