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
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.