This discussion is archived
5 Replies Latest reply: Mar 8, 2013 12:56 AM by chris227 RSS

Time stamp with Time zone issue

michaelrozar17 Pro
Currently Being Moderated
When i run the below query the time zone changes from -06:00 to +06:00. Can you guys please explain why? How do i retain the same time zone -06:00? Im in Oracle 10GR2.
select TO_TIMESTAMP_TZ('2013-03-08'||'03:00:00.000-06:00','YYYY-MM-DD HH24:MI:SS.FF TZH:TZM') from dual;
Output:  3/8/2013 3:00:00.000000000 AM +06:00
Edited by: michaelrozar17 on Mar 7, 2013 11:35 PM
  • 1. Re: Time stamp with Time zone issue
    jeneesh Guru
    Currently Being Moderated
    You have an extra space in your format..

    Either remove that from the format,
    select to_timestamp_tz('2013-03-08'||'03:00:00.000-06:00','YYYY-MM-DD HH24:MI:SS.FFTZH:TZM') t 
    from dual;
    
    T                                    
    --------------------------------------
    08-MAR-13 03.00.00.000000000 AM -06:00 
    Or add the space in the value, you pass
    select to_timestamp_tz('2013-03-08'||'03:00:00.000 -06:00','YYYY-MM-DD HH24:MI:SS.FF TZH:TZM') t 
    from dual;
    
    T                                    
    --------------------------------------
    08-MAR-13 03.00.00.000000000 AM -06:00 
  • 2. Re: Time stamp with Time zone issue
    Manik Expert
    Currently Being Moderated
    Try this:
    select to_timestamp_tz('2013-03-08'||'03:00:00 -06:00', 'YYYY/MM/DD HH:MI:SS TZH:TZM') tm from dual
    output
    TM
    ----
    3/8/2013 3:00:00.000000000 AM -06:00
    Cheers,
    Manik.

    Edited by: Manik on Mar 8, 2013 1:11 PM
  • 3. Re: Time stamp with Time zone issue
    michaelrozar17 Pro
    Currently Being Moderated
    Thanks much for the prompt response and the solution. I didn know that an extra single space does this difference in TZ. Im trying to compare date data type with time stamp with time zone data type. Can you please help? The below one does not seem to give me the correct result, what am i missing here?
    select 1 from dual
    where to_date('03/08/2013 01:50:00','mm/dd/yyyy hh24:mi:ss') >  tO_TIMESTAMP_TZ('2013-03-08 01.00.00.000000000 -06:00','YYYY-MM-DD HH24.MI.SS.FF TZH:TZM');
  • 4. Re: Time stamp with Time zone issue
    Paul Horth Expert
    Currently Being Moderated
    michaelrozar17 wrote:
    Thanks much for the prompt response and the solution. I didn know that an extra single space does this difference in TZ. Im trying to compare date data type with time stamp with time zone data type. Can you please help? The below one does not seem to give me the correct result, what am i missing here?
    select 1 from dual
    where to_date('03/08/2013 01:50:00','mm/dd/yyyy hh24:mi:ss') >  tO_TIMESTAMP_TZ('2013-03-08 01.00.00.000000000 -06:00','YYYY-MM-DD HH24.MI.SS.FF TZH:TZM');
    You're missing that Oracle is comparing two different data types, DATE and TIMESTAMP with TIME ZONE.

    In order to do the comparison, Oracle must change one or other of them to the data type of the other one.

    In this case it is changing the DATE to a TIMESTAMP with TIME ZONE and, in doing so, applying time zone conversion
    so that the condition is no longer true.

    Try comparing the same data types i.e. both should be TIMESTAMP with TIME ZONE.
  • 5. Re: Time stamp with Time zone issue
    chris227 Guru
    Currently Being Moderated
    michaelrozar17 wrote:
    Thanks much for the prompt response and the solution. I didn know that an extra single space does this difference in TZ. Im trying to compare date data type with time stamp with time zone data type. Can you please help? The below one does not seem to give me the correct result, what am i missing here?
    select 1 from dual
    where to_date('03/08/2013 01:50:00','mm/dd/yyyy hh24:mi:ss') >  tO_TIMESTAMP_TZ('2013-03-08 01.00.00.000000000 -06:00','YYYY-MM-DD HH24.MI.SS.FF TZH:TZM');
    You may try
    select 1 from dual
    where
    to_date('03/08/2013 01:50:00','mm/dd/yyyy hh24:mi:ss') >
    SYS_EXTRACT_UTC(tO_TIMESTAMP_TZ('2013-03-08 01.00.00.000000000 -06:00','YYYY-MM-DD HH24.MI.SS.FF TZH:TZM'))
     

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points