sql - 在 oracle 中转换为 DD-MON-YYYY 格式

Question

我有一个 varchar 列，其值为 2020-07-05T00:00:00.000+0000，我希望在 oracle 的 DD-MON-YYYY 中转换相同的值，即 2020 年 7 月 5 日。

提前致谢。

标签： sqloracle

Answer 1

您的日期不是日期，而是带有时区的时间戳。不要使用TO_DATE，因为您将丢弃大量信息，并且在丢弃信息时可能会得到错误的日期。改为使用TO_TIMESTAMP_TZ。

SELECT TO_TIMESTAMP_TZ( your_column, 'YYYY-MM-DD"T"HH24:MI:SS.FF3TZHTZM' )
FROM   your_table

然后，如果您真的想天真地将时间戳转换为字符串并丢弃时区信息而不将其应用于日期，那么只需使用TO_CHAR：

SELECT TO_CHAR(
         TO_TIMESTAMP_TZ( your_column, 'YYYY-MM-DD"T"HH24:MI:SS.FF3TZHTZM' ),
         'DD-MON-YYYY',
         'NLS_DATE_LANGUAGE=AMERICAN'
       )
FROM   your_table

但是，您可能想要规范化时区并将所有时间戳转换为公共时区（即 UTC），并且需要使用AT TIME ZONE 'UTC'：

SELECT TO_CHAR(
         TO_TIMESTAMP_TZ( your_column, 'YYYY-MM-DD"T"HH24:MI:SS.FF3TZHTZM' )
           AT TIME ZONE 'UTC',
         'DD-MON-YYYY',
         'NLS_DATE_LANGUAGE=AMERICAN'
       )
FROM   your_table

所以，对于一些测试数据：

CREATE TABLE your_table ( your_column ) AS
SELECT '2020-07-05T00:00:00.000+0000' FROM DUAL UNION ALL
SELECT '2020-07-05T00:00:00.000+0100' FROM DUAL UNION ALL
SELECT '2020-07-04T23:00:00.000-0100' FROM DUAL;

然后上面的选项：

SELECT TO_TIMESTAMP_TZ( your_column, 'YYYY-MM-DD"T"HH24:MI:SS.FF3TZHTZM' )
         AS timestamp,
       TO_CHAR(
         TO_TIMESTAMP_TZ( your_column, 'YYYY-MM-DD"T"HH24:MI:SS.FF3TZHTZM' ),
         'DD-MON-YYYY',
         'NLS_DATE_LANGUAGE=AMERICAN'
       ) AS ignore_timezone,
       TO_CHAR(
         TO_TIMESTAMP_TZ( your_column, 'YYYY-MM-DD"T"HH24:MI:SS.FF3TZHTZM' )
           AT TIME ZONE 'UTC',
         'DD-MON-YYYY',
         'NLS_DATE_LANGUAGE=AMERICAN'
       ) AS utc_date
FROM   your_table

将输出：

时间戳 | IGNORE_TIMEZONE | UTC_DATE   
:---------------------------- | :---------------- | :----------
2020-07-05T00:00:00.000+0000 | 2020 年 7 月 5 日 | 2020 年 7 月 5 日
2020-07-05T00:00:00.000+0100 | 2020 年 7 月 5 日 | 2020 年 7 月 4 日
2020-07-04T23:00:00.000-0100 | 2020 年 7 月 4 日 | 2020 年 7 月 5 日

db<>在这里摆弄