Forum Stats

  • 3,817,322 Users
  • 2,259,312 Discussions
  • 7,893,749 Comments

Discussions

How to handles daylight saving time change

0614
0614 Member Posts: 121 Red Ribbon

Hi ALL,

i am converting date from EST to CET getting error like below

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

example :-TO_DATE('08-MAR-2020 02:59:59','DD-MON-YYYY HH24:MI:SS')

please help to resolve this issue in DST and normal

Tagged:

Best Answer

  • mathguy
    mathguy Member Posts: 10,494 Blue Diamond
    Answer ✓

    So, somewhere in your data, you have a date-time of 08-MAR-2020 02:59:59, and you are told that it means a date-time in the EST5EDT time zone, and then you are asked to do something with it. (That "something" is to convert it to another time zone; but what that "something" is, doesn't even matter.)

    As you understand now, this is a BAD DATA problem. It's as if you had a date of "29 February", and you were told it's for year 2019, and you had to do something with it. You just can't - 2019 was not a leap year, so there was no 29 February.

    Now you ask: "What do I do with this?" The answer, as should always be the case with "bad data" problems, is: THIS IS NOT AN ORACLE QUESTION. It is a business issue. How to handle it should not be decided by developers, it should be decided by the business users who use this data.

    Something to figure out first - how did this data make it into the database in the first place? If the date-time was supposed to be in the EST5EDT time zone, that time simply didn't exist. How was it recorded - manually? Perhaps by a system that did not move time forward by one hour at 02:00:00 on 08-MAR-2020? Perhaps you can assume that the time recorded should have been 03:59:59. I believe that's what you mean by "see the difference from normal time to daylight saving time." The answer is clear - one hour. But that shouldn't be your decision - ask your business users about it. Ask them also to find out how the wrong time made it into the data, too - perhaps they must look at how they put a "timestamp" on transactions, or whatever those are.

    0614

Answers

  • mathguy
    mathguy Member Posts: 10,494 Blue Diamond

    Not sure why you are getting that error, since you didn't show us what code you are running. (The error is not thrown by the TO_DATE call).

    Also, what do you mean by "resolve this issue in DST and normal"? Oracle can only convert from one timezone to another; you tell Oracle what timezone to use. Some timezones are DST-aware, others aren't; Oracle should handle all conversions correctly.

    In any case - here is how you can take a date, convert it to a timestamp with time zone (by also providing the time zone), and then how you can convert from one time zone to another. The first step is to convert the date to a timestamp; sometimes Big Brother does implicit data type conversion for you, but in this case he doesn't.


    with
     sample_data(dt) as (
       select to_date('08-MAR-2020 02:59:59','DD-MON-YYYY HH24:MI:SS')
       from  dual
     )
    select from_tz(cast(dt as timestamp), 'EST') at time zone 'CET' as dt_at_cet
    from sample_data;
    
    
    DT_AT_CET
    -----------------------
    2020-03-08 08:59:59 CET
    

    (As you will notice, the output uses my session's NLS_TIMESTAMP_TZ_FORMAT setting.)

  • 0614
    0614 Member Posts: 121 Red Ribbon

    hi mathguy,

    Just modified query to EST5EDT

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

    Please check and do the needful

    with

     sample_data(dt) as (

      select to_date('08-MAR-2020 02:59:59','DD-MON-YYYY HH24:MI:SS')

      from dual

     )

    select from_tz(cast(dt as timestamp), 'EST5EDT') at time zone 'CET' as dt_at_cet

    from sample_data;

  • mathguy
    mathguy Member Posts: 10,494 Blue Diamond
    edited Nov 17, 2020 5:04PM

    I don't think Oracle recognizes 'EST5EDT'. Instead, it uses time zone regions, such as 'America/New_York'. That is definitely the preferred way to identify time zones in Oracle. (Similarly, instead of 'CET' you could use 'Europe/Paris' or whatever you are really converting to.)

    The other, bigger issue is that you seem to want to convert a timestamp that does not exist. Daylight saving time on the U.S. East Coast began on March 8, 2020, at 2 a.m. This means that the clock was moved forward at 2 a.m. - if you measure in one second increments, the time right after 01:59:59 was 03:00:00. Your attempt to convert the time-of-day of 02:59:59 is similar to trying to use a date of 36 May, or a "month number" of 14. May only has 31 days, and the year has only 12 months, and similarly, the date 2020-03-08 in time zone "America East Coast" does not have a time-of-day of 02:59:59.

    Below I show you how you should do this with time zone names to specify the time zone - but this too will fail for the exact time you requested. Not sure why you think you need to convert a non-existent timestamp with time zone; what's up with that?


    /*   GOOD   */ 
    
    with
     sample_data(dt) as (
       select to_date('08-MAR-2020 03:59:59','DD-MON-YYYY HH24:MI:SS')
       from   dual
     )
    select from_tz(cast(dt as timestamp), 'America/New_York') at time zone 'Europe/Paris' as ts_at_paris
    from   sample_data;
    
    TS_AT_PARIS                       
    -----------------------------------
    08-MAR-20 08.59.59.000 EUROPE/PARIS
    
    
    
    /*   BAD   */
    
    with
     sample_data(dt) as (
       select to_date('08-MAR-2020 02:59:59','DD-MON-YYYY HH24:MI:SS')
       from   dual
     )
    select from_tz(cast(dt as timestamp), 'America/New_York') at time zone 'Europe/Paris' as ts_at_paris
    from   sample_data;
    
    Error starting at line : 14 in command -
    with
     sample_data(dt) as (
       select to_date('08-MAR-2020 02:59:59','DD-MON-YYYY HH24:MI:SS')
       from   dual
     )
    select from_tz(cast(dt as timestamp), 'America/New_York') at time zone 'Europe/Paris' as ts_at_paris
    from   sample_data
    Error report -
    ORA-01878: specified field not found in datetime or interval
    


  • Jason_(A_Non)
    Jason_(A_Non) Member Posts: 2,082 Silver Trophy

    EST5EDT is a recognized time zone as shown by

    SELECT tzname, tzabbrev FROM V$TIMEZONE_NAMES WHERE tzname LIKE 'EST%';
    

    So when you use a valid time, this block works

    with
     sample_data(dt) as (
       select to_date('08-MAR-2020 03:59:59','DD-MON-YYYY HH24:MI:SS')
       from  dual
     )
    select from_tz(cast(dt as timestamp), 'EST5EDT') at time zone 'CET' as dt_at_cet
    from sample_data;
    
    DT_AT_CET
    --------------------------------------------------------------------------------
    08-MAR-20 08.59.59.000000 AM CET
    

    That said, I know I've read somewhere that Oracle does prefer usage of names, such as America/Buenos_Aires or America/Cancun to ensure daylight savings time is handled correctly (instead of someone using just EST when they mean America/New_York). Not sure where I read that or I'd post a link.

  • 0614
    0614 Member Posts: 121 Red Ribbon

    Hi ,

    Thanks for clarifying ,

    . Not sure why you think you need to convert a non-existent timestamp with time zone; what's up with that?i

    answer is like I have to run a procedure for past dates. it is failing at the time

    of to_date('08-MAR-2020 02:59:59','DD-MON-YYYY HH24:MI:SS')

     i got idea just do 
    to_date('08-MAR-2020 02:59:59','DD-MON-YYYY HH24:MI:SS') - interval 'hours:minutes' hour to minute ;
    
    we can apply whatever hours and minutes we see difference from normal time to day light time 
    
    Is this correct option?
    
    Please correct if any thing wrong
    
    


  • mathguy
    mathguy Member Posts: 10,494 Blue Diamond
    Answer ✓

    So, somewhere in your data, you have a date-time of 08-MAR-2020 02:59:59, and you are told that it means a date-time in the EST5EDT time zone, and then you are asked to do something with it. (That "something" is to convert it to another time zone; but what that "something" is, doesn't even matter.)

    As you understand now, this is a BAD DATA problem. It's as if you had a date of "29 February", and you were told it's for year 2019, and you had to do something with it. You just can't - 2019 was not a leap year, so there was no 29 February.

    Now you ask: "What do I do with this?" The answer, as should always be the case with "bad data" problems, is: THIS IS NOT AN ORACLE QUESTION. It is a business issue. How to handle it should not be decided by developers, it should be decided by the business users who use this data.

    Something to figure out first - how did this data make it into the database in the first place? If the date-time was supposed to be in the EST5EDT time zone, that time simply didn't exist. How was it recorded - manually? Perhaps by a system that did not move time forward by one hour at 02:00:00 on 08-MAR-2020? Perhaps you can assume that the time recorded should have been 03:59:59. I believe that's what you mean by "see the difference from normal time to daylight saving time." The answer is clear - one hour. But that shouldn't be your decision - ask your business users about it. Ask them also to find out how the wrong time made it into the data, too - perhaps they must look at how they put a "timestamp" on transactions, or whatever those are.

    0614
  • 0614
    0614 Member Posts: 121 Red Ribbon

    hi,

    appreciate for ur quick response and help,

    Apologies - while doing debug we got to know there is "bad data" got inserted into table .

    Thank you so much

  • 0614
    0614 Member Posts: 121 Red Ribbon

    and sorry for wasting your time on this

  • mathguy
    mathguy Member Posts: 10,494 Blue Diamond

    Hey, no worries - you didn't waste my time in any way. Good luck on your project!

    0614