5 Replies Latest reply: Mar 8, 2013 2:56 AM by chris227 RSS

    Time stamp with Time zone issue

    michaelrozar17
      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
          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
            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
              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
                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
                  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'))