How To Convert Number Format Of A Calculated Column To Time (hh:mm:ss) Format, Sql Query

First I'm sorry for the poor English but I'll try to be as understandable as possible. I'm trying to make a Oracle Database SQL Query which subtracts the datetime value of two exis

Solution 1:

A function makes it prettier. It returns number of days formatted as dd:hh:mi:ss.

SQL>createor replace function f_days2ddhhmiss (par_broj_dana in number)
  2return varchar2
  3is4     l_broj_dana  number := par_broj_dana;
  5     retval       varchar2 (20);
  6begin7with podaci
  8as (select trunc (l_broj_dana) broj_dana,
  9                     round (mod (l_broj_dana *24, 24), 2) broj_sati,
 10                     round (mod (l_broj_dana *24*60, 60), 2) broj_minuta,
 11                     round (mod (l_broj_dana *24*60*60, 60), 2)
 12                        broj_sekundi
 13from dual)
 14select    lpad (p.broj_dana, 2, '0')
 15||':'16|| lpad (trunc (p.broj_sati), 2, '0')
 17||':'18|| lpad (trunc (p.broj_minuta), 2, '0')
 19||':'20|| lpad (trunc (p.broj_sekundi), 2, '0')
 21into retval
 22from podaci p;
 2324return retval;
 25end f_days2ddhhmiss;
 26/Function created.

Example:

Without it, you get decimal number:

SQL>select to_date('07.08.2020 14:25', 'dd.mm.yyyy hh24:mi:ss')
  2- to_date('03.08.2020 13:20', 'dd.mm.yyyy hh24:mi:ss') result3from dual;

    RESULT----------4,04513889

With it, you get what you wanted:

SQL>select f_days2ddhhmiss(to_date('07.08.2020 14:25', 'dd.mm.yyyy hh24:mi:ss')
  2- to_date('03.08.2020 13:20', 'dd.mm.yyyy hh24:mi:ss')
  3                        ) result4from dual;

RESULT--------------------------------------------------------------------------------04:01:05:00SQL>

---

Yes, such (or similar) code can be used directly in SQL, but it makes a SELECT statement kind of ugly and difficult to read.

Your query would then be

SELECT m.numos,
       m.codfuncos,
       m.dtinicioos,
       m.dtfimseparacao,
       f_days2ddhhmiss (m.dtfimseparacao - m.dtinicioos) AS tempo
  FROM pcmovendpend m
 WHERE     dtfimseparacao ISNOT NULL
       AND data >= SYSDATE - 1

See if it helps.

Solution 2:

With Littlefoots help I managed a way to convert the column to the desired format, just as follows:

CREATEOR REPLACE FUNCTION WMS_CORINGA(TEMPO NUMBER)
RETURN VARCHAR2 IS
HORA NUMBER;
MINUTO NUMBER(2);
SEGUNDO NUMBER(2);
TEMPOABS NUMBER;
BEGIN
IF TEMPO <>0THEN
TEMPOABS :=ABS(TEMPO);
HORA := TRUNC(TEMPOABS*24);
MINUTO := TRUNC(((TEMPOABS*24)-HORA) *60);
SEGUNDO := ((((TEMPOABS*24)-HORA) *60) - MINUTO) *60;
RETURN TO_CHAR(HORA, 'FM9999900') ||':'|| TO_CHAR(MINUTO, 'FM00') ||':'|| TO_CHAR (SEGUNDO, 'FM00');
ELSERETURN'';
END IF;
END;

SELECT  M.NUMOS, M.CODFUNCOS, M.DTINICIOOS, M.DTFIMSEPARACAO,
WMS_CORINGA(M.DTFIMSEPARACAO - M.DTINICIOOS) AS TEMPO_EM_SEPARACAO  
FROM PCMOVENDPEND M
WHERE DTFIMSEPARACAO ISNOTNULLAND DATA>= SYSDATE-1

But I couldn't make the PowerBI connection, the output is Details: "This native database query isn't currently supported."

Anyways, learned a lot.

Solution 3:

You can cast dates to timestamps before subtracting. The result of the subtraction is an interval represented as "days hh24:mi:ss.fff". If necessary you can then use the extract function to pull out the individual components. That way you have neither an ugly query nor a a need to create a function.

with the_dates( dt1, dt2) as 
   ( select to_date('07.08.2020 14:25', 'dd.mm.yyyy hh24:mi:ss')
          , to_date('03.08.2020 13:20', 'dd.mm.yyyy hh24:mi:ss')  
       from dual
   ) 
select dt1, dt2, cast(dt1 astimestamp) -cast(dt2 astimestamp) resultfrom the_dates;