Oracle SQL中如何将ISO格式时间拆分为日期列与时间列？

你要处理的'2022-07-28T14:12:12.334Z'是标准ISO 8601 UTC时间格式，Oracle可以通过以下步骤拆分出日期和时间部分：

1. 先将字符串转换为带时区的时间戳 #

首先用TO_TIMESTAMP_TZ函数解析该字符串，格式模型要严格匹配字符结构：

TO_TIMESTAMP_TZ('2022-07-28T14:12:12.334Z', 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"')

其中FF3对应三位毫秒，"Z"用来匹配末尾的UTC时区标识。

2. 提取日期列 #

如果需要仅保留年月日的日期类型，有两种常用方式：

• 直接转换为DATE类型：

SELECT CAST(TO_TIMESTAMP_TZ('2022-07-28T14:12:12.334Z', 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"') AS DATE) AS date_column
FROM DUAL;

• 截断时间部分，仅保留日期：

SELECT TRUNC(TO_TIMESTAMP_TZ('2022-07-28T14:12:12.334Z', 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"')) AS date_column
FROM DUAL;

注意：如果需要将UTC时间转换为数据库本地时区的日期，可以加上AT TIME ZONE 'Asia/Shanghai'（替换为你的目标时区）再转换：

SELECT CAST(TO_TIMESTAMP_TZ('2022-07-28T14:12:12.334Z', 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"') AT TIME ZONE 'Asia/Shanghai' AS DATE) AS local_date_column
FROM DUAL;

3. 提取时间列 #

Oracle没有单独的TIME数据类型，通常用TIMESTAMP（保留时分秒毫秒）或INTERVAL DAY TO SECOND来存储时间部分：

• 提取为TIMESTAMP（仅保留时间部分）：

SELECT TO_TIMESTAMP(TO_CHAR(TO_TIMESTAMP_TZ('2022-07-28T14:12:12.334Z', 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"'), 'HH24:MI:SS.FF3'), 'HH24:MI:SS.FF3') AS time_column
FROM DUAL;

• 提取为时间间隔类型：

SELECT CAST(TO_TIMESTAMP_TZ('2022-07-28T14:12:12.334Z', 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"') AS TIMESTAMP) - TRUNC(CAST(TO_TIMESTAMP_TZ('2022-07-28T14:12:12.334Z', 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"') AS DATE)) AS time_interval
FROM DUAL;

如果需要转换为本地时区的时间，同样可以加上AT TIME ZONE：

SELECT TO_TIMESTAMP(TO_CHAR(TO_TIMESTAMP_TZ('2022-07-28T14:12:12.334Z', 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"') AT TIME ZONE 'Asia/Shanghai', 'HH24:MI:SS.FF3'), 'HH24:MI:SS.FF3') AS local_time_column
FROM DUAL;

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