我想在oracle中为Difference将时间精简成特定的格式。

Question

当我取这两种时间的差异时，我需要显示它是积极的还是消极的。

select a.jobname,
       round((to_date(to_char(a.Lastupdatedatetime, 'DD/MM/YYYY HH:MI:SS AM'),
                      'DD/MM/YYYY HH:MI:SS AM') - 
              to_date(to_char(a.Creationdatetime, 'DD/MM/YYYY HH:MI:SS AM'),
                      'DD/MM/YYYY HH:MI:SS AM'))*24*60,2) as duration_Staging,
       round((to_date(to_char(b.Lastupdatedatetime, 'DD/MM/YYYY HH:MI:SS AM'),
                      'DD/MM/YYYY HH:MI:SS AM') -
              to_date(to_char(b.Creationdatetime,'DD/MM/YYYY HH:MI:SS AM'),
                      'DD/MM/YYYY HH:MI:SS AM'))*24*60,2) as duration_Prod,
       substr(((a.lastupdatedatetime - a.creationdatetime) - 
               (b.lastupdatedatetime - b.creationdatetime)), 11, 10) as Difference
  from production.scheduledjob a, 
       production.scheduledjob b
 where a.jobstatuscv='C' 
   and b.jobstatuscv='C' 
   and a.effectivedate=to_char(sysdate-3,'DD-MON-YYYY')
   and b.effectivedate=to_char(sysdate-4,'DD-MON-YYYY')
   and a.jobname=b.jobname order by Difference desc;

上面的查询输出是：

JOBNAME                 DURATION_STAGING    DURATION_PROD       DIFFERENCE
PershingStarToPace               90.43        145.4        -000000000 00:54:57.79
PershingUpdateMarketValue        15.05        54.53        -000000000 00:39:28.87

我需要如下产出：

JOBNAME                     DURATION_STAGING  DURATION_PROD   DIFFERENCE
PershingStarToPace               90.43        145.4           -00:54:57.79
PershingUpdateMarketValue        15.05        54.53           -00:39:28.87

表描述

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SCHEDULEDJOBID                            NOT NULL NUMBER(22)
 EFFECTIVEDATE                             NOT NULL DATE
 JOBSTATUSCV                                        VARCHAR2(10)
 CREATIONDATETIME                          NOT NULL TIMESTAMP(6)
 CREATEDBY                                          NUMBER(22)
 LASTUPDATEDATETIME                        NOT NULL TIMESTAMP(6)
 LASTUPDATEDBY                                      NUMBER(22)
 JOBID                                              NUMBER(22)
 SCHEMANAME                                NOT NULL VARCHAR2(100)
 MESSAGE                                            VARCHAR2(1000)
 JOBNAME                                            VARCHAR2(100)
 SERVERNAME                                         VARCHAR2(100)
 JOBGROUP                                           VARCHAR2(100)
 WORKGROUPID                                        NUMBER(22)
 JOBGROUPID                                         NUMBER(22)

Answer 1

看起来，TO_CHAR()对于INTERVAL数据类型并不是真正可用的。因此，您必须手动执行，例如：

SELECT 
    REGEXP_SUBSTR(((a.lastupdatedatetime - a.creationdatetime) - (b.lastupdatedatetime - b.creationdatetime)), '^-?')
    || REGEXP_SUBSTR(((a.lastupdatedatetime - a.creationdatetime) - (b.lastupdatedatetime - b.creationdatetime)), '\d{2}:\d{2}:\d{2}\.\d{2}')
FROM ...

提取不起作用，因为前面的"-“是在每个组件之前添加的，即

SELECT
    EXTRACT(HOUR FROM ((a.lastupdatedatetime - a.creationdatetime) - (b.lastupdatedatetime - b.creationdatetime)) || ':' ||
    EXTRACT(MINUTE FROM ((a.lastupdatedatetime - a.creationdatetime) - (b.lastupdatedatetime - b.creationdatetime)) || ':' ||
    EXTRACT(SECOND FROM ((a.lastupdatedatetime - a.creationdatetime) - (b.lastupdatedatetime - b.creationdatetime))
FROM ...

例如，返回0:-54:-57.79。