Oracle DST时间转换错误ORA-01878 [英] Oracle DST Time Conversion Error ORA-01878

问题描述

我有一条select语句，我正在其中转换时区

i have a select statement where i am converting timezones

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

但是对于某些行，由于DST而出现错误

but for some rows i am getting error due to DST

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

我想写一个类似的查询

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

推荐答案

当您使用12c时，可以使用提供了定义局部函数的增强型子查询因子.可以尝试使用美国/东部进行转换，如果失败则回落到-4:00.

As you're on 12c you can use the enhanced subquery factoring that provides to define a local function; that can attempt the conversion with US/Eastern, and fall back to -4:00 if that fails.

使用示例数据和另外两行仍会转换的行:

Using your sample data and a couple of extra rows that will convert anyway:

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.

I've adjusted my NLS settings so you can see the difference in the converted values, as either EST, EDT or a fixed -4:00.

正如评论中提到的那样，您将忽略潜在的数据问题，而更正更正已知错误的数据会更好-假设您可以确定为什么它是错误的，因此可以安全地进行修复；或确认您断言原始数据都应该是美国/东部.

As mentioend in comments, you're ignoring the underlying data issues, and it would be better to correct the data that you know is wrong - assuming you can be sure why it is wrong and therefore how it is safe to fix; or to confirm your assertion that the original data is all supposed to be US/Eastern.

从根本上讲，由于某些人显然不是真正的美国/东部人，因此信任任何数据似乎都不安全.如果不知道这些特定记录如何以及为什么具有您不期望的值，则无法确定其他任何值都是您期望的.无论插入那些日期的任何应用程序，工具或过程都可能已经插入(并且可能确实插入了)其他时间，这些时间看起来还不错，但实际上也不是美国/东部时间.其余的都可以毫无错误地进行转换，但这并不意味着UTC时间必须具有代表性.

Fundamentally, as some are clearly not really US/Eastern, it doesn't seem safe to trust any of the data. Without knowing how and why those specifc records have values you don't expect, you can't be sure that any other values are what you expect either. Whatever application, tool or process inserted those dates may have (and probably did) insert other times which look OK but are also not actually US/Eastern. The rest may all convert without error, but that doesn't mean the UTC times are necessarily representative.

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

You also have a secondary problem in that you don't know whether a date you have recorded as 2017-11-05 01:00:00 was originally 01:00 EST or 01:00 EDT, as that hour was repeated when summertime ended. Oracle will just choose for you though.

这篇关于Oracle DST时间转换错误ORA-01878的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！