时间戳

首页 > 解决方案 > 在JOOQ中获取两个日期的差的总和

postgresql - 在JOOQ中获取两个日期的差的总和

问题描述

我正在使用 JOOQ 在我的 java 代码中编写 SQL。我将以下查询写入 PostgreSQL 数据库：' 查询：获取检查任务的总数和完成任务所花费的总时间。一项任务的总时间是通过执行（endtime-starttime）从表“workevents”计算出来的。但这里我正在获取花费在所有任务上的总时间。

      with taskdata as (
        select taskid from unittest.tasks 
        where projectname='test'and status='checked'
        ),

      workevents as(
        select (endtime-starttime) diff ,unittest.workevents.taskid as 
        workeventtaskid from unittest.workevents ,taskdata 
        where taskdata.taskid=unittest.workevents.taskid
         )

      select sum(workevents.diff),count(distinct workeventtaskid) from 
      workevents;

我已将其转换为下面的 jooQ AS：

最终字符串 sql =

            with(TASK_INFO_WRAPPER)              
            .as(select(TASK_ID).from(TASK_TABLE)                
            .where(PROJECT_NAME.eq(param()).and(TASK_STATUS.eq("checked"))))

            .with(WORKEVENT_INFO_WRAPPER)                            
           .as(select(TASK_END_TIME.sub(TASK_START_TIME).as("diff"),
           WORKEVENT_TASK_ID.as("workeventtaskid"))
                    .from(WORKEVENT_TABLE, table(name(TASK_INFO_WRAPPER)))
                    .where("workeventinfo.taskid=taskinfo.taskid"))
            .select().getSQL(ParamType.INDEXED);

但我无法获得“diff”的总和（日期的差异）。JOOQ 中是否有任何函数可以将 sql 语句“select sum(workevents.diff)”转换为 JOOQ。

我已经尝试过 sum(field) 函数，但它给出了编译时间错误，因为 sum 用于数字。在这里我正在计算两个日期（diff）之差的累积和。

标签： postgresqljooq

解决方案

在使用运算符实现日期差异时，所有 RDBMS 的行为都略有不同-，这就是为什么通常建议使用 jOOQDSL.dateDiff()或DSL.timestampDiff()代替的原因。

关于使用的附注WITH

WITH通常用于在 SQL 中将问题分解为更小的问题。但是在某些时候，这种分解会导致比必要的更复杂的查询，就像您的情况一样。尤其是在使用 jOOQ 时，通常建议避免使用公用表表达式 ( WITH) 或派生表，不仅因为它们在 jOOQ 中表达起来有点困难，而且因为它们并没有真正为您的查询增加价值。

您的查询可以这样写：

select 
  sum(e.endtime - e.starttime),
  count(distinct e.taskid)
from unittest.tasks t
join unittest.workevents e on t.taskid = e.taskid
where t.projectname = 'test' and t.status = 'checked'

这显然更容易转化为 jOOQ。

推荐阅读