Results 1 to 5 of 5
  1. #1
    Join Date
    May 2002
    Posts
    34

    Unanswered: Issue with timezone conversion using 'AT TIME ZONE' clause

    Hello dear Oracle gurus!

    I run a test query on two physically different Oracle 11g R2 instances and getting two different results, to be exact conversion does happen on one db and does not on another.

    Environment:
    OS - Cent OS 5.10
    DB - Oracle 11g R2
    sessiontimezone for db1 and db2 = 'America/Los_Angeles'

    Test query:
    Code:
    SELECT TO_TIMESTAMP('05/01/2014 06:00:00', 'MM/DD/YYYY HH24:MI:SS') AS DT_ORIG, 
    TO_TIMESTAMP('05/01/2014 06:00:00', 'MM/DD/YYYY HH24:MI:SS') AT TIME ZONE 'America/New_York' as dt_tz
    FROM DUAL;
    Results:
    db1
    Code:
    DT_ORIG				DT_TZ
    01-MAY-14 06.00.00.000000000 AM	01-MAY-14 09.00.00.000000000 AM AMERICA/NEW_YORK
    db2
    Code:
    DT_ORIG				DT_TZ
    01-MAY-14 06.00.00.000000000 AM	01-MAY-14 06.00.00.000000000 AM AMERICA/NEW_YORK
    As you see in case of db1 conversion happens as expected and in case of db2 it does not.

    Please advise what to check for in order to get consistent results.
    I appreciate your suggestions!

    Thank you,
    Pit.
    Last edited by PeterS; 05-01-14 at 15:10. Reason: Bad formatting

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Humor me & run modified SQL below against both DBs & post the results back here.
    Code:
    SELECT To_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'), 
           To_timestamp('01/10/2013 06:00:00', 'DD/MM/YYYY HH24:MIS') AS DT_ORIG, 
           To_timestamp('01/10/2013 06:00:00', 'DD/MM/YYYY HH24:MIS') AT TIME zone 
           'America/New_York'                                         AS dt_tz 
    FROM   dual;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2002
    Posts
    34
    Thank you for quick reply!
    Here is the result:
    db1
    Code:
    DT_CURRENT	DT_ORIG	DT_TZ
    2014-05-01 10:44:00	01-MAY-14 06.00.00.000000000 AM	01-MAY-14 09.00.00.000000000 AM AMERICA/NEW_YORK
    db2
    Code:
    DT_CURRENT	DT_ORIG	DT_TZ
    2014-05-01 11:43:50	01-MAY-14 06.00.00.000000000 AM	01-MAY-14 06.00.00.000000000 AM AMERICA/NEW_YORK
    sysdates are off by 1 hour; can it cause the issue?

    Pit.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I am in PDT & don't have any suggestions for you.

    >sysdates are off by 1 hour;
    I would speculate that the 2 DBs are in different time zones.

    Code:
    SQL> SELECT TO_TIMESTAMP('05/01/2014 06:00:00', 'MM/DD/YYYY HH24:MI:SS') AS DT_ORIG, 
    TO_TIMESTAMP('05/01/2014 06:00:00', 'MM/DD/YYYY HH24:MI:SS') AT TIME ZONE 'America/New_York' as dt_tz
    FROM DUAL;  2    3  
    
    DT_ORIG
    ---------------------------------------------------------------------------
    DT_TZ
    ---------------------------------------------------------------------------
    01-MAY-14 06.00.00.000000000 AM
    01-MAY-14 06.00.00.000000000 AM AMERICA/NEW_YORK
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    May 2002
    Posts
    34
    Yes, my databases are in different timezones (dbtimezone is different), sessiontimezone is the same, however my test query returns expected results for some databases with different timezones and does not for some others.

    It looks like I found a solution:
    Code:
    select FROM_TZ(TO_TIMESTAMP('05/01/2014 06:00:00', 'DD/MM/YYYY HH24:MI:SS'), 'America/Los_Angeles') AT TIME ZONE 'America/New_York' AS DT_TZ1 from dual;
    It works deterministically across all my databases.

    However I ran into another issue . I wrote a wrapper function around select statement above.
    For my purposes I need to pass value of the first timezone which is FROM_TZ function parameter from external source.

    Code:
    CREATE OR REPLACE FUNCTION MN_FROM_TZ(P_TIMESTAMP IN TIMESTAMP, P_TO_DOC_TZ IN VARCHAR2)
    RETURN VARCHAR2
    DETERMINISTIC
    AS
    V_TIMESTAMP_TZ VARCHAR2(50);
    V_APP_TZ VARCHAR2(255);
    BEGIN
      SELECT OPTION_VALUE INTO V_APP_TZ FROM MN_DW_ETL_CONFIG_OPTIONS WHERE OPTION_NAME='APP_TIMEZONE';
      SELECT FROM_TZ(P_TIMESTAMP, V_APP_TZ) AT TIME ZONE p_TO_DOC_TZ INTO V_TIMESTAMP_TZ FROM dual;
    RETURN V_TIMESTAMP_TZ;
    END;
    /
    Function compiles with no errors.
    Parameters P_TIMESTAMP and P_TO_DOC_TZ are passed from an actual table.
    However when I call this function from select:
    SELECT MN_FROM_TZ(EFF_START_DATE, TIMEZONE) FROM my_tbl;

    I get the following errors:
    Code:
    ORA-00923: FROM keyword not found where expected
    ORA-06512: at "schema_name.MN_FROM_TZ", line 9
    00923. 00000 -  "FROM keyword not found where expected"
    *Cause:    
    *Action:

    I am sure the reason is parameter p_TO_DOC_TZ, because when I hard code its value as 'America/New_York' I get expected results. I guess I somehow need to handle single quotes around this parameter but can't figure out how.

    Thank you,
    Pit.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •