如何选择'HH24:MI:SSxFF6'格式的 varchar2 字段作为间隔小时到秒(6)？

Question

以下查询成功：

SELECT INTERVAL '00:00:00.000000' HOUR TO SECOND(6)
FROM DUAL;

以下操作失败：

SELECT INTERVAL time_field HOUR TO SECOND(6) -- time_field is a VARCHAR2(15)
FROM some_table;

带有以下错误：

ORA-00923: FROM keyword not found where expected

如何修复我的第二个查询以拉回一小时到第二个间隔？

当我尝试时：

SELECT cast(time_field AS INTERVAL HOUR TO SECOND(6))
FROM some_table

我得到以下信息：

ORA-00963: unsupported interval type

测试设置：

CREATE TABLE some_table (
   time_field VARCHAR2(50)
);
INSERT ALL
INTO some_table (time_field) VALUES ('10:00:00.000000')
INTO some_table (time_field) VALUES ('12:00:00.000000')
INTO some_table (time_field) VALUES ('15:00:00.000000')
INTO some_table (time_field) VALUES ('17:00:00.000000')
INTO some_table (time_field) VALUES ('20:00:00.000000')
INTO some_table (time_field) VALUES (NULL)
SELECT * FROM DUAL;

Answer 1

您可以使用

TO_DSINTERVAL 函数;此处的示例使用 CTE 复制表：

WITH some_table AS (SELECT '00:00:00.000000' AS time_field FROM dual)
SELECT TO_DSINTERVAL('0 ' || time_field)
FROM some_table;
TO_DSINTERVAL('0'||TIME_FIELD)
---------------------------------------------------------------------------
+000000000 00:00:00.000000000

请注意，要使格式成为函数识别的内容，您必须提供一个"days"部分，因此虚拟'0 '字符串的前缀。

固定值查询返回的格式略有不同(或至少显示略有不同;间隔 daa 类型的格式模型与日期不同，因此不确定如何准确表达(：

SELECT INTERVAL '00:00:00.000000' HOUR TO SECOND(6)
FROM DUAL;
INTERVAL'00:00:00.000000'HOURTOSECOND(6)
---------------------------------------------------------------------------
+00 00:00:00.000000

要复制它，如果需要，可以cast它：

WITH some_table AS (SELECT '00:00:00.000000' AS time_field FROM dual)
SELECT CAST(TO_DSINTERVAL('0 ' || time_field) AS INTERVAL DAY TO SECOND(6))
FROM some_table;
CAST(TO_DSINTERVAL('0'||TIME_FIELD)ASINTERVALDAYTOSECOND(6))
---------------------------------------------------------------------------
+00 00:00:00.000000

。或只是：

WITH some_table AS (SELECT '00:00:00.000000' AS time_field FROM dual)
SELECT CAST('0 ' || time_field AS INTERVAL DAY TO SECOND(6))
FROM some_table;
CAST('0'||TIME_FIELDASINTERVALDAYTOSECOND(6))
---------------------------------------------------------------------------
+00 00:00:00.000000

。这几乎是@catcall最初建议的，但这也需要预先'0 '，否则你会得到一个ORA-01867。(或者，如果您尝试使用 HOUR TO SECOND 、 ORA-00963 ，即使使用前置的日期值(。但是，我猜(这只是一个猜测，尽管稍微受过教育(它正在做隐式TO_DSINTERVAL或类似的事情，我想我更喜欢使用显式的，所以我确定发生了什么。不过，那可能只是我...

---

使用您的示例数据，我也得到了一个ORA-01867，但这是由空值引起的。您可以使用case将其保留为结果中的 null：

SELECT CASE WHEN time_field IS NULL THEN null
    ELSE CAST('0 ' || time_field AS INTERVAL DAY TO SECOND(6)) END
FROM some_table;
CASEWHENTIME_FIELDISNULLTHENNULLELSECAST('0'||TIME_FIELDASINTERVALDAYTOSECO
---------------------------------------------------------------------------
+00 10:00:00.000000
+00 12:00:00.000000
+00 15:00:00.000000
+00 17:00:00.000000
+00 20:00:00.000000

6 rows selected.

Answer 2

我认为这个(未经测试的(演员阵容是需要的。

SELECT cast (time_field as interval hour to second(6))
FROM some_table;

您的列必须是与强制转换为间隔兼容的类型，并且必须包含可解释为间隔的数据。

适用于 CAST 的甲骨文 11g 文档

---

示例代码

create table test (
  interval_type interval day to second,
  varchar_type varchar2(35) 
);
insert into test values (
  (current_timestamp - to_date(current_date)),
  (current_timestamp - to_date(current_date))
);
select 
  interval_type, 
  varchar_type
from test;
INTERVAL_TYPE                                                                              VARCHAR_TYPE
{"Bytes":[-128,0,0,0,78,95,84,-102,-92,-83,-64],"ShareBytes":null,"Length":11,"Stream":{}} +000000000 18:35:24.000000
select 
  interval_type, 
  cast (varchar_type as interval day to second)
from test;
INTERVAL_TYPE                                                                               CAST(VARCHAR_TYPEASINTERVALDAYTOSECOND)
{"Bytes":[-128,0,0,0,78,95,84,-102,-92,-83,-64],"ShareBytes":null,"Length":11,"Stream":{}}  {"Bytes":[-128,0,0,0,78,95,84,-128,0,0,0],"ShareBytes":null,"Length":11,"Stream":{}}