I want to trim the Time into specific format in oracle sql for Difference Coulm

Question

When i am taking the difference between the two timing i need to display whether it is positive or negative.

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;

Above query output is:

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

I Need output as below :

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

Table description

 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)

Can you show us the table ddl for scheduledjob? You are unnecessarily converting between date and character. — Noel, Oct 14 '14 at 06:03
May this help you: http://stackoverflow.com/questions/14030654/converting-time-difference-to-a-given-format-in-oracle — DirkNM, Oct 14 '14 at 06:28
DURATION_STAGING and DURATION_PROD are converted into minutes then taken difference. — Ajinkya Chavan, Oct 14 '14 at 06:36
The calculation of `duration_staging` and `duration_prod` can be simplified by using `cast(a.Lastupdatedatetime as date)` instead of converting a `timestamp` to a `varchar` and then back to a `date`. — , Oct 14 '14 at 06:46
See here for more solutions on how to format an `interval`: http://stackoverflow.com/q/970249/330315 — , Oct 14 '14 at 06:48

Wernfried Domscheit · Accepted Answer · 2014-10-14T07:24:35.830

1

It looks like TO_CHAR() is not really usable for INTERVAL data types. So you have to do it manually, for example like this:

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 ...

EXTRACT does not work, because the leading "-" is added before each component, i.e.

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 ...

returns 0:-54:-57.79 for instance.

edited Oct 14 '14 at 07:24

answered Oct 14 '14 at 06:37

Wernfried Domscheit

54,457
9
76
110

Can you explain this '^-?') and '\d{2}:\d{2}:\d{2}\.\d+' i am confused however your query works. – Ajinkya Chavan Oct 14 '14 at 06:52
hi Wernfried i used your first query it works fine but i it shows the zero after the time i need to trim that also please help me out -54:-57.7900000 output should be -54:-57.79 – Ajinkya Chavan Oct 14 '14 at 07:02
I edit my answer. Check [Regular Expressions in Oracle Database](http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_regexp.htm) or [Regular expression](http://en.wikipedia.org/wiki/Regular_expression) for details on regular expressions. – Wernfried Domscheit Oct 14 '14 at 07:28

I want to trim the Time into specific format in oracle sql for Difference Coulm

1 Answers1