时间戳

首页 > 解决方案 > Oracle DST 时间转换错误 ORA-01878

sql - Oracle DST 时间转换错误 ORA-01878

问题描述

我有一个选择语句，我在其中转换时区

Select
from_tz(cast(DATE_TIME as timestamp), 'US/Eastern') at time zone 'UTC' DATE_TIME_UTC
From Table1

但是对于某些行，由于 DST，我得到了错误

ORA-01878: specified field not found in datetime or interval

我想写一个查询

select 
if error then do something else do the time conversion from table1

标签： sqloracleoracle-sqldeveloper

解决方案

当您在 12c 上时，您可以使用增强的子查询分解来定义本地函数；可以尝试与美国/东部的转换，如果失败则回退到 -4:00。

使用您的示例数据和一些额外的行，无论如何都会转换：

alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
alter session set nls_timestamp_tz_format = 'YYYY-MM-DD HH24:MI:SS TZR TZD';

with
  function get_tstz(p_date in date) return timestamp with time zone is
    dst_exception exception;
    pragma exception_init(dst_exception, -1878);
  begin
    return from_tz(cast(p_date as timestamp), 'US/Eastern');
    exception
      when dst_exception then
        return from_tz(cast(p_date as timestamp), '-04:00');
  end get_tstz;
select date_time,
  get_tstz(date_time) as date_time_converted,
  get_tstz(date_time) at time zone 'UTC' as date_time_utc
from table1
/

DATE_TIME           DATE_TIME_CONVERTED                DATE_TIME_UTC              
------------------- ---------------------------------- ---------------------------
2018-03-11 01:59:00 2018-03-11 01:59:00 US/EASTERN EST 2018-03-11 06:59:00 UTC UTC
2018-03-11 02:06:00 2018-03-11 02:06:00 -04:00 -04:00  2018-03-11 06:06:00 UTC UTC
2018-03-11 02:08:00 2018-03-11 02:08:00 -04:00 -04:00  2018-03-11 06:08:00 UTC UTC
2018-03-11 02:21:00 2018-03-11 02:21:00 -04:00 -04:00  2018-03-11 06:21:00 UTC UTC
2018-03-11 02:48:00 2018-03-11 02:48:00 -04:00 -04:00  2018-03-11 06:48:00 UTC UTC
2018-03-11 02:06:00 2018-03-11 02:06:00 -04:00 -04:00  2018-03-11 06:06:00 UTC UTC
2018-03-11 02:33:00 2018-03-11 02:33:00 -04:00 -04:00  2018-03-11 06:33:00 UTC UTC
2018-03-11 03:00:00 2018-03-11 03:00:00 US/EASTERN EDT 2018-03-11 07:00:00 UTC UTC

我已经调整了 NLS 设置，以便您可以看到转换后的值的差异，例如 EST、EDT 或固定的 -4:00。

正如评论中提到的那样，您忽略了潜在的数据问题，最好纠正您知道错误的数据 - 假设您可以确定错误的原因以及如何安全地修复；或确认您的断言，即原始数据都应该是美国/东部的。

从根本上说，由于有些显然不是真正的美国/东部，因此相信任何数据似乎都不安全。在不知道这些特定记录如何以及为什么具有您不期望的值的情况下，您也无法确定任何其他值也是您所期望的。无论插入这些日期的任何应用程序、工具或过程，都可能（并且可能确实）插入了其他看起来不错但实际上也不是美国/东部的时间。其余的都可以无误地转换，但这并不意味着 UTC 时间一定具有代表性。

您还有一个次要问题，即您不知道您记录为 2017-11-05 01:00:00 的日期最初是 01:00 EST 还是 01:00 EDT，因为夏令时结束时该时间重复了. Oracle 只会为您选择。

推荐阅读