使用JOOQ在PostgreSQL中以秒为单位的时间戳之间的差异求和 [英] Find and sum difference between timestamps in seconds in PostgreSQL using JOOQ

问题描述

这是我的另一个问题(

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

我想对TIMETRACKS.ENDTIME和TIMETRACKS.STARTTIME的差异求和，以秒为单位.可能会发生数据库中没有任何TIMETRACKS的情况.在这种情况下，数据库应该返回0.

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.

这就是TIMETRACKS表的样子:

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)

但是合并不适用于间隔类型: org.postgresql.util.PSQLException: ERROR: COALESCE types interval and integer cannot be matched

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

另一种解决方案确实适用于我，但似乎有点复杂"，因为我必须编写一些原始的SQL:

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)

是否有不需要我编写原始SQL的有效解决方案?

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

推荐答案

目前唯一可行的解​​决方案是诉诸于这里.

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)

这篇关于使用JOOQ在PostgreSQL中以秒为单位的时间戳之间的差异求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！