CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
dude
dude

Reputation: 33

Oracle DST Time Conversion Error ORA-01878

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

but for some rows i am getting error due to DST

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

i want to write a query like

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

Upvotes: 1

Views: 6110

Answers (3)

Dennis Löhmann
Dennis Löhmann

Reputation: 59

I got this error message for one of these two reasons:

Most likely: you are trying to convert a local time that does not exist for reasons of DST switch, e.g. '28-MAR-21 02:34' does not exist in Germany (timezone Berlin/Europe), because clocks jump from 1:59 AM to 3:00 AM during night. Solution: add one hour, UTC will then reflect the correct time.

Less likely: typo in timezone string: "Europe/ Berlin" or "Berlin/Europe" - both is wrong, correct is "Europe/Berlin" (pattern is Continent/City)

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191285

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

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.

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.

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.

Upvotes: 2

George Joseph
George Joseph

Reputation: 5922

You can create a custom function and checks if its a valid timestamp with time zone and use that function in the where clause of your query as follows for example.

create table t(x varchar(100));

insert into t 
  select '21-FEB-2009 18:00:00'
    from dual
  union all  
  select '31-FEB-2009 18:00:00' /*Junk date here..*/
    from dual;

create or replace function fn_test(dt in varchar2)
return int
as
l_timestamp timestamp with time zone;
begin
   l_timestamp :=from_tz(to_timestamp(dt,'DD-MON-YYYY hh24:mi:ss'), 'US/Eastern') at time zone 'UTC';
return 1;   
exception
 when others then    
    return null;
end;
/

select from_tz(to_timestamp(x,'DD-MON-YYYY hh24:mi:ss'),'US/Eastern') at time zone 'UTC'
  from t
where fn_test(x) is not null

Upvotes: 1

Related Questions