Oracle 19c中SQL Loader加载时如何正确提取时间戳列的时间部分？

环境与表结构 #

• Oracle版本：19.3.0
• 表创建语句：

CREATE TABLE test
(
  DATE_STAMP         DATE,
  TIMESTAMP          TIMESTAMP(6)
)

测试CSV数据 #

test.csv内容：

2007/05/13,16:35:24
2007/05/13,18:00:51

原SQL Loader控制文件 #

OPTIONS(skip=1)
LOAD DATA
        INFILE 'test.csv'
        BADFILE 'test.bad'
        INSERT INTO TABLE test
        FIELDS TERMINATED BY ','
        (
         date_stamp,
         timestamp      TIMESTAMP "HH24:MI:SS.FF2"
        ) 

问题现象 #

数据加载成功，但TIMESTAMP列被自动拼接了加载当天的日期（示例中为9/1/2023）：
执行查询：

select DATE_STAMP, TIMESTAMP from test;

实际结果：

13-MAY-07   9/1/2023 4:35:24.000000 PM
13-MAY-07   9/1/2023 6:00:51.000000 PM

期望结果：

13-MAY-07   4:35:24.000000 PM
13-MAY-07   6:00:51.000000 PM

原因与解决方案 #

原因 #

Oracle的TIMESTAMP类型必须包含日期和时间部分，当SQL Loader仅读取时间字符串时，会默认使用当前系统日期补全日期部分。

解决方法 #

要让TIMESTAMP列使用DATE_STAMP的日期，需在控制文件中拼接日期和时间字段，再转换为TIMESTAMP类型：

修改后的控制文件：

OPTIONS(skip=1)
LOAD DATA
        INFILE 'test.csv'
        BADFILE 'test.bad'
        INSERT INTO TABLE test
        FIELDS TERMINATED BY ','
        (
         date_stamp,
         timestamp      "TO_TIMESTAMP(CONCAT(:date_stamp, ' ', :timestamp), 'YYYY/MM/DD HH24:MI:SS')"
        ) 

可选方案 #

也可以通过DATE类型中转实现：

OPTIONS(skip=1)
LOAD DATA
        INFILE 'test.csv'
        BADFILE 'test.bad'
        INSERT INTO TABLE test
        FIELDS TERMINATED BY ','
        (
         date_stamp,
         timestamp      "CAST(TO_DATE(:date_stamp || ' ' || :timestamp, 'YYYY/MM/DD HH24:MI:SS') AS TIMESTAMP)"
        ) 

显示优化 #

如果仅需要查询时显示时间部分，可使用TO_CHAR格式化输出：

SELECT DATE_STAMP, TO_CHAR(TIMESTAMP, 'HH:MI:SS.FF6 PM') AS TIMESTAMP FROM test;

内容的提问来源于stack exchange，提问作者user13708337