SQL & PL/SQL

vpolasa wrote:

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

    CREATE TABLE MI_DTWKHIST_TST (DATE_D DATE, ENTY_KEY NUMBER);
    
    INSERT INTO MI_DTWKHIST_TST VALUES (TO_DATE('26-MAR-2000 04:01 28','DD-MON-YYYY HH:MI SS'), 101);
    
        SELECT to_char(DATE_D, 'dd-mon-yyyy hh:mi ssam'),
    FROM_TZ(TO_TIMESTAMP(ds_tbl.DATE_D), 'Asia/Baku'),
                DS_TBL.ENTY_KEY
                   FROM MI_DTWKHIST_TST ds_tbl
                   WHERE ds_tbl.enty_key = 101;

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.

Using Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
Windows 7

Thanks..

what is "DS_TBL" in posted code above?

It's just an alias to the table. I removed it.

Hi,

Review the syntax of TO_TIMESTAMP

vpolasa wrote:

TO_TIMESTAMP(ds_tbl.DATE_D)

The 1st argument is supposed to be a character string but you're calling it with a DATE.

Although it's legal to call TO_TIMESTAMP with just 1 argument, it's simply asking for trouble.  Always pass at least 2 arguments.

Also review the syntax of FROM_TZ.

Maybe you want something like:

SELECT  TO_CHAR (date_d, 'dd-mon-yyyy hh:mi ssam')  AS dt
,       FROM_TZ ( CAST (ds_tbl.date_d AS TIMESTAMP)
                , '4:00'
                )                                   AS tm
,       ds_tbl.enty_key 
FROM    mi_dtwkhist_tst  ds_tbl 
WHERE   ds_tbl.enty_key  = 101
; 

As Frank said, you shouldn't be using TO_TIMESTAMP. Use CAST instead. But you will still get same error. Why? Daylight savings time. March 26, 2000 4:00 AM clock in Baku was changed to 5:00 am, so there was no 26-MAR-2000 04:01 28 in Azerbaijan.

SY.

I'm too lazy to check if it causes the error, but since it's good practice I'll mention it anyway: You were missing an alias:

SELECT to_char(DATE_D, 'dd-mon-yyyy hh:mi ssam'), -- <<= Here you miss ds_tbl
       FROM_TZ(TO_TIMESTAMP(ds_tbl.DATE_D), 'Asia/Baku'), 
       DS_TBL.ENTY_KEY 
FROM MI_DTWKHIST_TST ds_tbl 
WHERE ds_tbl.enty_key = 101; 

Either use aliases everywhere, or don't use them at all. Don't mix it.

I would also not mix capitals and lowercase like you are doing, but rather write everything using lowercase. That makes it much more readable.

Regards,

Stefan

Edit: never mind, just noticed you've removed the alias. The communities are slow today behind my customer's firewall (or maybe it's just me )

Thanks Frank and Solomon..

Could you please assist me on how we can resolve this? As I noticed there are few records falling between 4am and 5am.

One resolution I can think of is catch these records in the exception block and add 1 hour and apply the from_tz.

Hi,

vpolasa wrote:

Thanks Frank and Solomon..

Could you please assist me on how we can resolve this? As I noticed there are few records falling between 4am and 5am.
One resolution I can think of is catch these records in the exception block and add 1 hour and apply the from_tz.

What, exactly, do you want to do?

Post a little sample data (CREATE TABLE and INSERT statements) and the output you want from that sample data.  Do this whenever you have any problem.  See the Forum FAQ:

If you're unsure about the exact output you want, post a couple of possibilities, and explain.

Frank..

I updated my original post. I have few questions:

1. This question might be for Solomon: I tried with different times and it works if the time doesn't fall between 4-5. How would we know if this issue was due to daylight saving?

2. How can we make the select statement work for enty_key = 101? I'm looking for output similar to enty_key = 102.

3. Shouldn't Oracle internally adjust the time so it doesn't fall between the missing hour?

Thanks..

vpolasa wrote:

1. This question might be for Solomon: I tried with different times and it works if the time doesn't fall between 4-5. How would we know if this issue was due to daylight saving?
2. How can we make the select statement work for enty_key = 101? I'm looking for output similar to enty_key = 102.
3. Shouldn't Oracle internally adjust the time so it doesn't fall between the missing hour?

1 & 3: Date datatype (same as TIMESTAMP) has no time zone, therefore Oracle has no way of saying/checking if value is valid. For example, March 26, 2000 4:01 AM is valid in New York, London or Paris but not in Baku:

SQL> SELECT FROM_TZ(TIMESTAMP '2000-03-26 04:01:00','Asia/Baku') TS FROM DUAL;

SELECT FROM_TZ(TIMESTAMP '2000-03-26 04:01:00','Asia/Baku') TS FROM DUAL

              *

ERROR at line 1:

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

SQL> SELECT FROM_TZ(TIMESTAMP '2000-03-26 04:01:00','America/New_York') TS FROM DUAL;

TS

---------------------------------------------------------------------------

26-MAR-00 04.01.00.000000000 AM AMERICA/NEW_YORK

SQL> SELECT FROM_TZ(TIMESTAMP '2000-03-26 04:01:00','Europe/London') TS FROM DUAL;

TS

---------------------------------------------------------------------------

26-MAR-00 04.01.00.000000000 AM EUROPE/LONDON

SQL> SELECT FROM_TZ(TIMESTAMP '2000-03-26 04:01:00','Europe/Paris') TS FROM DUAL;

TS

---------------------------------------------------------------------------

26-MAR-00 04.01.00.000000000 AM EUROPE/PARIS

SQL>

And for your second question - what output do you expect? Same as for 102?

SY.

Yes Solomon.. I'm trying to get the output similar to (not same as) 102.

I'm trying to get  a valid output without error if I pass a time between the timezone.

I'm still confused, if Oracle knows to throw an error if the timezone falls between the daylight saving time, why wouldn't Oracle adjust the time implicitly?

vpolasa wrote:

Yes Solomon.. I'm trying to get the output similar to (not same as) 102.
I'm trying to get  a valid output without error if I pass a time between the timezone.
I'm still confused, if Oracle knows to throw an error if the timezone falls between the daylight saving time, why wouldn't Oracle adjust the time implicitly?

1. We don't have crystal balls - what means "similar"? Please provide exact output you expect.

2. Oracle has no crystal ball either - you are telling it to make a timestamp with time zone having value 26-MAR-00 04.01.00.000000000 AM Asia/Baku which doesn't exist.

SY.

Thanks Solomon, but I'm still confused.

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

Because this is not just about DST. Few countries have time change (irrelevant to DST). Example: India in 1941, Oct 01 had +36 minutes.

SELECT FROM_TZ(TIMESTAMP '1941-10-01 00:01:00','Asia/Calcutta') TS FROM DUAL;

I'd like to have your expert opinion:

How would you resolve this?: A table has a timestamp field with 10 records. One of the record is above mentioned time. How would you convert all the records to India time zone (including the above record)?

Thanks.

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

i explained this once to you but here it is again. Edit: can't see post anymore, deleted or something else?

Tell oracle how much you want to move the clock. If you use DST name say "Asia/Calcutta" then it does not work but instead give an exact value for conversion.

alter session set time_zone = "-01:00"

Because this is not just about DST. Few countries have time change (irrelevant to DST). Example: India in 1941, Oct 01 had +36 minutes. 

SELECT sys_extract_utc( from_tz(timestamp '1941-10-01 00:01:00', '+00:36') ) utc from dual;


UTC                         
-----------------------------
30.09.1941 23:25:00,000000000

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.

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?

  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.