1 2 Previous Next 17 Replies Latest reply on May 17, 2016 9:51 PM by Jarkko Turpeinen Go to original post
      • 15. Re: ORA-01878: specified field not found in datetime or interval
        Jarkko Turpeinen

        here's documentation that says it could be done with region name if you add TZD abbreviation. I need to read it myself before i can suggest you more

         

        http://docs.oracle.com/database/121/NLSPG/ch4datetime.htm#NLSPG266

         

        Oracle Database automatically determines whether Daylight Saving Time is in effect for a specified time zone and returns the corresponding local time. Normally, date/time values are sufficient to allow Oracle Database to determine whether Daylight Saving Time is in effect for a specified time zone. The periods when Daylight Saving Time begins or ends are boundary cases. For example, in the Eastern region of the United States, the time changes from 01:59:59 a.m. to 3:00:00 a.m. when Daylight Saving Time goes into effect. The interval between 02:00:00 and 02:59:59 a.m. does not exist. Values in that interval are invalid. When Daylight Saving Time ends, the time changes from 02:00:00 a.m. to 01:00:01 a.m. The interval between 01:00:01 and 02:00:00 a.m. is repeated. Values from that interval are ambiguous because they occur twice.

        To resolve these boundary cases, Oracle Database uses the TZR and TZD format elements. TZR represents the time zone region in datetime input strings. Examples are 'Australia/North', 'UTC', and 'Singapore'. TZD represents an abbreviated form of the time zone region with Daylight Saving Time information. Examples are 'PST' for U. S. Pacific Standard Time and 'PDT' for U. S. Pacific Daylight Time. To see a list of valid values for the TZRand TZD format elements, query the TZNAME and TZABBREV columns of the V$TIMEZONE_NAMES dynamic performance view.

        • 16. Re: ORA-01878: specified field not found in datetime or interval
          mathguy

          vpolasa asked:

           

          If Oracle knows a timestamp in timezone doesn't exist, then why not Oracle adjust time implicitly?


          I don't presume to speak for Solomon (to whom you addressed the question), but perhaps this will help till he gets a chance to respond.


          Sometimes when something isn't exactly "by the book" the intent can be guessed with high confidence. If I write 'abc' || 1 Oracle will guess I meant 'abc' || '1' - it will convert NUMBER to VARCHAR2 without warning. These are easy.


          However, in your example you are asking Oracle to make a business decision for you. If right after 3:59:59 AM on a certain date came 5:00:00 AM, and somehow you were able to enter the time 4:30:00 in your table, what do you want Oracle to do for you? Change it to 5:30:00 AM? (Pushing the time one hour forward?)  Or truncate it back to 3:59:59 AM, the last valid time before 5:00:00 AM? Or change it to 3:59:59 AM if it is up to and including 4:29:59 AM but change it to 5:00:00 AM if it is between 4:30:30 AM and 4:59:59 AM?


          Note that this shouldn't be the decision of the IT department. In an organization where this issue arises and is important, it is a business decision, made by business staff, not by IT professionals. Are you asking Oracle to just make a guess to replace your organization's business judgment?

          1 person found this helpful
          • 17. Re: ORA-01878: specified field not found in datetime or interval
            Jarkko Turpeinen

              Could you please help me understand why I'm seeing below error? How can I resolve this?

             

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

            01878. 00000 -  "specified field not found in datetime or interval"

            *Cause:    The specified field was not found in the datetime or interval.

            *Action:  Make sure that the specified field is in the datetime or interval.

            Well i had to study this to understand time zones better myself.

             

            So in simplest form of example i try to explain why it happens and how you can handle it.

             

            Why?


            "The periods when Daylight Saving Time begins or ends are boundary cases." That means the moment when time resets one hour in particular day and time.


            Below example shows that day and time when that happened in Helsinki. Actually there is no such time as 03:00:00 because it jumps from 02:59:59 to 04:00:00, That is why it cannot be created by DST automatic that Europe/Helsinki time_zone region name imply. It does not recognize the time! So it raises ORA-01878.

             

            select
            
            
                timestamp '2016-03-27 03:00:00 Europe/Helsinki'
            
            
            from dual
            ;
            
            
            SQL Error: ORA-01878: specified field not found in datetime or interval
            01878. 00000 -  "specified field not found in datetime or interval"
            *Cause:    The specified field was not found in the datetime or interval.
            *Action:   Make sure that the specified field is in the datetime or interval.
            
            
            
            

             

            Why the hell such values are in my database?

             

            Reason is simple, timestamp does not contain time zone information. That leads to the fact that it cannot govern inserted values comply DST rules.

             

            In other words it is possible to insert timestamp values that could ever never be handled by DST automatic particularly when it's boundary case like previous example is.


            Oracle just cannot do that



            So what happens if it would have been timestamp with time zone column and inserting such boundary case time.

             

            Well lets see.

             

            create table tz(
              tstz  timestamp with time zone
            )
            ;
            
            
            insert into tz values (timestamp '2016-03-27 03:00:00')
            ;
            
            
            drop table tz purge
            ;
            
            
            Table TZ created.
            
            
            Error starting at line : 25 in command -
            insert into tz values (timestamp '2016-03-27 03:00:00')
            Error report -
            SQL Error: ORA-01878: specified field not found in datetime or interval
            01878. 00000 -  "specified field not found in datetime or interval"
            *Cause:    The specified field was not found in the datetime or interval.
            *Action:   Make sure that the specified field is in the datetime or interval.
            
            
            Table TZ dropped.
            
            
            
            


            It does not allow such nonsense in there!

             

            Wrap-up:

             

            You get ORA-01878 because your database contains values that should not be there.

            Or they should never be converted by DST automatic.

            Automatic DST is activated when you set time_zone with region name for example "Asia/Baku".

             

             

             

            Handling it:

             

            If you need to convert timestamp or dates between summer and winter or something else then just use simple adding or subtracting for example interval '1' hour. That way it will never raise ORA-01878.

             

            Problem is the data. You probably do not know which time it is (summer, winter, etc) and which direction to change it.

            1 2 Previous Next