如何将计算列的数字格式转换为时间（hh：mm：ss）格式，SQL查询

问题描述：

首先，我对英语不佳感到抱歉，但我会尽量让人们理解它。

First I'm sorry for the poor English but I'll try to be as understandable as possible.

我正在尝试制作一个减去日期时间的Oracle数据库SQL查询。现存的两个列的值，我设法按照下面显示的说明进行操作，但是我创建的TEMPO列以NUMBER格式返回。

I'm trying to make a Oracle Database SQL Query which subtracts the datetime value of two existing columns, I managed how to do it using the instruction shown below but the column TEMPO that I created is returning with NUMBER format.

SELECT M.NUMOS, M.CODFUNCOS, M.DTINICIOOS, M.DTFIMSEPARACAO,
M.DTFIMSEPARACAO - M.DTINICIOOS AS TEMPO 
FROM PCMOVENDPEND M
WHERE DTFIMSEPARACAO IS NOT NULL
AND DATA >= SYSDATE-1

输出已完成。

输出

我需要转换将TEMPO格式的时间格式设置为 hh：mm：ss，如果某些结果经过24小时，则它会像32:01:20一样不断添加（就像[hh]：mm：ss Excel格式一样）。

I need to convert the column TEMPO format to TIME as 'hh:mm:ss', and if some results passes through 24 hours it keeps adding like 32:01:20 (just like the [hh]:mm:ss Excel formatting).

我尝试了以下说明，但返回了 ORA-00932错误数据类型不一致，期望的时间和返回的日期DATE JULIAN （我不知道这是否是确切的错误描述，我的DBX是葡萄牙语），但是如果可以，我无法对根表进行任何更改

I tried a few things like the instruction below but it returns ORA-00932 error inconsistent data type, expected TIME and returned DATE JULIAN (I don't know if it is the exact error description, my DBX is in Portuguese), unfortunately I can't do any changes to the root tables if it would help.

SELECT M.NUMOS, M.CODFUNCOS, M.DTINICIOOS, M.DTFIMSEPARACAO,
CAST(M.DTFIMSEPARACAO - M.DTINICIOOS AS TIME) AS TEMPO 
FROM PCMOVENDPEND M
WHERE DTFIMSEPARACAO IS NOT NULL
AND DATA >= SYSDATE-1

无论如何，TEMPO列的格式非常重要，因为TIME会很重要，因为我将使用sql查询作为PowerBi Direct Query的指令，并且无法导入数据库来使用它

Anyways, it is so important that the column TEMPO format is TIME because I'll use the sql query as an instruction to PowerBi Direct Query and I can't import the database to work it with PowerQuery since it is too large data.

谢谢大家！

答

更漂亮。它返回格式为 dd：hh：mi：ss 的天数。

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

SQL> create or replace function f_days2ddhhmiss (par_broj_dana in number)
  2     return varchar2
  3  is
  4     l_broj_dana  number := par_broj_dana;
  5     retval       varchar2 (20);
  6  begin
  7     with podaci
  8          as (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
 13                from dual)
 14     select    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')
 21       into retval
 22       from podaci p;
 23
 24     return retval;
 25  end f_days2ddhhmiss;
 26  /

Function created.

示例：

没有它，您将得到十进制数字：

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') result
  3  from 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                        ) result
  4  from dual;

RESULT
--------------------------------------------------------------------------------
04:01:05:00

SQL>

是的，这样（或类似的）代码可以直接在SQL中使用，但是使得 SELECT 语句的类型为丑陋并且难以阅读。

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

您的查询将会是

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

看看是否有帮助。

如何将计算列的数字格式转换为时间（hh：mm：ss）格式，SQL查询

相关推荐