Find and sum difference between timestamps in seconds in PostgreSQL using JOOQ

Question

This is a follow up question to another question of mine (Find difference between timestamps in seconds in PostgreSQL using JOOQ).

I want to to sum the diff of TIMETRACKS.ENDTIME and TIMETRACKS.STARTTIME in seconds. It can happen that there will not be any TIMETRACKS in the database. In this case the database should return 0 instead.

Thats how the TIMETRACKS table looks like:

CREATE TABLE "TimeTracks" (
    "id" uuid PRIMARY KEY,
    "startTime" timestamp NOT NULL,
    "endTime" timestamp,
);

This is one solution that i tried:

coalesce(sum(timestampDiff(TIMETRACKS.ENDTIME, TIMETRACKS.STARTTIME)), 0)

However coalesce does not work with the interval type: org.postgresql.util.PSQLException: ERROR: COALESCE types interval and integer cannot be matched

Another solution that i found on StackOverflow actually works for me but seems a bit "complicated" because i have to write some raw SQL:

coalesce(sum(extractEpochFrom(timestampDiff(TIMETRACKS.ENDTIME, TIMETRACKS.STARTTIME))), 0)

fun extractEpochFrom(field: Field<DayToSecond>): Field<Integer> =
    DSL.field("extract(epoch from {0})", Integer::class.java, field)

Is there any working solution that does not require me to write raw SQL?

You already found the solution that would work for you in this case. There is currently no better solution, I'm afraid. So, you might as well give your solution as an answer to your own question — Lukas Eder, Aug 22 '18 at 07:35
Even though the current solution is not that big of a problem, are there any plans of supporting it in the future? — Tobias Marschall, Aug 22 '18 at 07:43
Yes! https://github.com/jOOQ/jOOQ/issues/2132. I'm working on this right now. Will definitely prioritise the `DatePart.EPOCH`. (Just realised that there *is*, in fact already experimental support for this in jOOQ 3.11) — Lukas Eder, Aug 22 '18 at 09:30

score 2 · Answer 1 · answered Aug 22 '18 at 07:48

The only working solution for now is to resort to plain SQL as found here.

coalesce(sum(extractEpochFrom(timestampDiff(TIMETRACKS.ENDTIME, TIMETRACKS.STARTTIME))), 0)

fun extractEpochFrom(field: Field<DayToSecond>): Field<Integer> =
    DSL.field("extract(epoch from {0})", Integer::class.java, field)

Lukas Eder · Answer 2 · 2018-08-22T13:22:09.073

1

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

edited Aug 22 '18 at 13:22

answered Aug 22 '18 at 09:31

Lukas Eder

211,314
129
689
1,509

score 0 · Answer 3 · edited Nov 02 '21 at 23:22

0

COALESCE(SUM(extract (epoch from (activities.finished_at - activities.started_at)/3600)))

edited Nov 02 '21 at 23:22

Tyler2P

2,324
26
22
31

answered Nov 02 '21 at 16:20

Khaireddine

11
2

7

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Tyler2P Nov 02 '21 at 16:44

Find and sum difference between timestamps in seconds in PostgreSQL using JOOQ

3 Answers3

Linked