Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Rohan.

Asked: January 17, 2017 - 9:46 am UTC

Last updated: May 18, 2022 - 1:16 pm UTC

Version: Oracle 12c

Viewed 1000+ times

You Asked

Hello Tom,

I have one column INTERVAL_TIME with datatype as TIMESTAMP(0) and stores data in 15 mins interval time.
Since there was DST on 13 March 2016 , the interval was store at 1:45 AM and next interval at 3:00 AM.

But when i am applying to_char on the column , the result is as below (compare and check between 2 AM to 3 AM).
Is to_char considering DST and manipulating the date accordingly ?

TO_CHAR(INTERVAL_TIME,'MM/DD/YYYYHH24:MI:SS') ~~~ INTERVAL_TIME
3/13/2016 0:00 ~~~ 13-MAR-16 12.00.00.000000000 AM
3/13/2016 0:15 ~~~ 13-MAR-16 12.15.00.000000000 AM
3/13/2016 0:30 ~~~ 13-MAR-16 12.30.00.000000000 AM
3/13/2016 0:45 ~~~ 13-MAR-16 12.45.00.000000000 AM
3/13/2016 1:00 ~~~ 13-MAR-16 01.00.00.000000000 AM
3/13/2016 1:15 ~~~ 13-MAR-16 01.15.00.000000000 AM
3/13/2016 1:30 ~~~ 13-MAR-16 01.30.00.000000000 AM
3/13/2016 1:45 ~~~ 13-MAR-16 01.45.00.000000000 AM
3/13/2016 2:00 ~~~ 13-MAR-16 03.00.00.000000000 AM
3/13/2016 2:15 ~~~ 13-MAR-16 03.15.00.000000000 AM
3/13/2016 2:30 ~~~ 13-MAR-16 03.30.00.000000000 AM
3/13/2016 2:45 ~~~ 13-MAR-16 03.45.00.000000000 AM
3/13/2016 3:00 ~~~ 13-MAR-16 03.00.00.000000000 AM
3/13/2016 3:15 ~~~ 13-MAR-16 03.15.00.000000000 AM
3/13/2016 3:30 ~~~ 13-MAR-16 03.30.00.000000000 AM
3/13/2016 3:45 ~~~ 13-MAR-16 03.45.00.000000000 AM
3/13/2016 4:00 ~~~ 13-MAR-16 04.00.00.000000000 AM
3/13/2016 4:15 ~~~ 13-MAR-16 04.15.00.000000000 AM
3/13/2016 4:30 ~~~ 13-MAR-16 04.30.00.000000000 AM
3/13/2016 4:45 ~~~ 13-MAR-16 04.45.00.000000000 AM
3/13/2016 5:00 ~~~ 13-MAR-16 05.00.00.000000000 AM
3/13/2016 5:15 ~~~ 13-MAR-16 05.15.00.000000000 AM
3/13/2016 5:30 ~~~ 13-MAR-16 05.30.00.000000000 AM
3/13/2016 5:45 ~~~ 13-MAR-16 05.45.00.000000000 AM

and Chris said...

This may be an issue with time zones. Daylight savings only started on 13 March in some parts of the world! For example, here in the UK it started on 27 March 2016:

https://www.timeanddate.com/time/dst/2016.html

Your session time zone affects when this kicks in:

alter session set NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24.MI';
alter session set time_zone = 'Europe/London';

with rws as (
  select timestamp'2016-03-13 01:00:00' +
           numtodsinterval((rownum-1)*15, 'minute') dt 
  from dual connect by level <= 12
)
  select dt  from rws;

DT                 
13-MAR-2016 01.00  
13-MAR-2016 01.15  
13-MAR-2016 01.30  
13-MAR-2016 01.45  
13-MAR-2016 02.00  
13-MAR-2016 02.15  
13-MAR-2016 02.30  
13-MAR-2016 02.45  
13-MAR-2016 03.00  
13-MAR-2016 03.15  
13-MAR-2016 03.30  
13-MAR-2016 03.45 

create table t (
  dt timestamp with time zone
);

insert into t
with rws as (
  select timestamp'2016-03-13 01:00:00' +
           numtodsinterval((rownum-1)*15, 'minute') dt 
  from dual connect by level <= 12
)
  select dt  from rws;

12 rows inserted.

alter session set time_zone = 'US/Eastern';

insert into t
with rws as (
  select timestamp'2016-03-13 01:00:00' +
           numtodsinterval((rownum-1)*15, 'minute') dt 
  from dual connect by level <= 12
)
  select dt  from rws;

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


So: how exactly are you generating and storing your timestamps? And which time zone is your client in? You can find your current time zone with:

select sessiontimezone from dual;

SESSIONTIMEZONE  
Europe/London   

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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

A reader, May 17, 2022 - 3:25 am UTC

Greetings !

One user reported this error when they ran the query from SQL Developer tool.

Error message:

ORA-12801: error signaled in parallel query server P005, instance p101.int.com:MGPRD1 (1)

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

12801. 00000 - "error signaled in parallel query server %s"

*Cause: A parallel query server reached an exception condition.

*Action: Check the following error message for the cause, and consult

your error manual for the appropriate action.

*Comment: This error can be turned off with event 10397, in which

case the server's actual error is signaled instead.



Query:

select * from GES2.RM_WFB_C where gender_code='Female Global' and for_year=2021;

GES2.RM_WFB_C ==> is the database view
for_year ==> is the column extracted from the extract function inside the view as

EXTRACT (YEAR FROM P.EFF_DATE_START)

EFF_DATE_START is the TIMETAMP(6) datatype. No timezone data is stored.


I ran the same query in US EST Time Zone, I am not able to reproduce the error and user who reported is in Israel and it seems he is getting this error sporadically (not all the time).


Just wondering, what could be causing this error. Can it be user's local NLS / timestamp settings on the client side causing this error Or its underlying data issue.

Appreciate your insights / assistance on this to troubleshoot this issue further.
Chris Saxon
May 17, 2022 - 3:41 pm UTC

This is a date conversion error, so there are almost certainly implicit conversions somewhere in the query. Meaning setting NLS settings will kick in and affect the process.

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

A reader, May 17, 2022 - 8:13 pm UTC

>> This is a date conversion error, so there are almost certainly implicit conversions somewhere in the query. Meaning setting NLS settings will kick in and affect the process.

Could you please explain this little bit more.

If this is NLS settings issue, any suggestions on how to troubleshoot this further.


Thanks!





Chris Saxon
May 18, 2022 - 1:16 pm UTC

Ultimately you have to dig through the code and data to see where the error occurs. Capturing the exact state of the user's session when the error occurs is a good start - NLS settings, search values, etc. Then use the exact same settings when debugging.

Check all expressions involving date, timestamp, or interval values anywhere in the query - SELECT, WHERE, ORDER BY, etc. Any time you have a function or comparison with these data types you could have an implicit conversion.