如何在Oracle SQL中将十进制转换为时间？

Question

在我正在查询的某个表中，时间是这样存储的：

09:30储存为9,3

14:25作为14:25储存

如何使用SQL将9,3到09:30 (hh:mm格式)转换？到目前为止，我在其他问题中唯一能找到的是那些需要将9:5转换为09:30的人(在我的例子中，9:5意味着09:50)。

现在，我正在使用PHP来转换这个。但是我的DB服务器比我的应用服务器更好。因为我需要获取大量的数据，所以最好使用SQL来解决这个问题。

如果这是个愚蠢的问题，很抱歉，但我对SQL并不熟悉。如果你需要的话，可以问更多的问题。

Answer 1

假设这些是数值，则可以使用trunc(your_column)获得小时部分，而通过100 * mod(your_column, 1)获得分钟部分。这给出了两个数字；您可以格式化这些数字并将它们连接起来，同样假设您想要一个字符串结果，例如：

to_char(trunc(your_column), 'FM00') ||':' || to_char(100 * mod(your_column, 1), 'FM00')

或者更简单地说，在一步中将整个数字格式化为单个字符串，方法是让它使用冒号作为小数分隔符(任何作为组分隔符的内容-不使用)：

to_char(your_column, 'FM00D00', 'NLS_NUMERIC_CHARACTERS=:,')

使用CTE中的示例数据进行演示：

-- CTE for sample data
with your_table (time) as (
  select 9.3 from dual
  union all select 9.03 from dual
  union all select 14.25 from dual
)
-- actual query
select time,
  to_char(trunc(time), 'FM00') ||':' || to_char(100 * mod(time, 1), 'FM00') as new_time1,
  to_char(time, 'FM00D00', 'NLS_NUMERIC_CHARACTERS=:,') as new_time2
from your_table;

      TIME NEW_TIME1 NEW_TIME2
---------- --------- ---------
       9.3 09:30     09:30    
      9.03 09:03     09:03    
     14.25 14:25     14:25    

如果您实际上想要一个间隔数据类型结果，那么您可以在trunc/mod中使用相同的拆分，但是处理这两个数字的方式不同：

select time,
  numtodsinterval(trunc(time), 'HOUR')
    + numtodsinterval(100 * mod(time, 1), 'MINUTE') as new_time
from your_table;

      TIME NEW_TIME           
---------- -------------------
       9.3 +00 09:30:00.000000
      9.03 +00 09:03:00.000000
     14.25 +00 14:25:00.000000

或者，如果您愿意，可以在名义日期转换为该时间，例如：

date '2000-01-01'
  + trunc(your_column), 'HOUR')
  + numtodsinterval(100 * mod(your_column, 1), 'MINUTE')

在某一时刻，如果您的值并不真正表示有效时间，例如9.60或25.3，您就会遇到问题。上面的任何一个查询都会在一定程度上处理这一点--至少对于第一个查询，最高可达99.99 --但并不一定会给出有用的结果。字符串会给你'09:60‘和'25:30'，而间隔会给你'0 10:00:00’和'1 01:30:00'，这也许更明智一些。不过，这是使用不适当的数据类型的危险。

保留@BobJarvis中的注释，如果您实际上想要一个日期数据类型结果，那么您可以将数字转换为没有任何分隔符的字符串，然后将其转换为日期：

to_date(to_char(your_column, '00V00'), 'HH24MI')

这会给你这个月的第一天时间。但是，这将导致9.60或25.3的错误。