6 Replies Latest reply: Feb 8, 2013 7:37 AM by Jack Sam RSS

    Wrong ENQ_TIME

    Jack Sam
      We are using oracle 11.2.0.3.0, seems ENQ_TIME is wrong. It is 7 hours faster. It is 00:00
      in SYSTEM.AQ$_QUEUE_TABLES . Any idea why ? And how to fix this?

      Thanks,
        • 1. Re: Wrong ENQ_TIME
          phcullen-Oracle
          Hello,

          In 11.2 the times are calculated in UTC which means that depending on what you are looking at they will appear out of sync with the database time. The change was made to avoid DST issues.

          When you are querying the values are you using the AQ$QUEUE_TABLE view? If not you should be.

          Is anything not working as a result of this? Or is the case that you have just observed this?

          Thanks
          Peter
          • 2. Re: Wrong ENQ_TIME
            Jack Sam
            Hi Pete,

            You are correct the enc_time matches UTC time.

            It caused confusion on debugging and support. Is there any way to set ENQ_TIME DEQ_TIME
            to sysdate or local time?

            Thanks,
            • 3. Re: Wrong ENQ_TIME
              WGabriel
              Hello,

              as the Oracle Support already suggested, you can temporarily convert the database time (SYSTIMESTAMP) to
              the current UTC time, if you are for instance checking ENQ_TIME in source code:
              SELECT SYSDATE, TO_DATE( TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'DD/MM/YYYY HH24:MI:SS'), 'DD/MM/YYYY HH24:MI:SS')
              FROM DUAL;
              There was already a discussion in this AQ forum: Create AQ table with invalid timezone

              Kind regards,

              WoG
              • 4. Re: Wrong ENQ_TIME
                Jack Sam
                Hi,

                Thanks for trying to help but that wasn't my question although.

                I found an answer from http://ora-exp.blogspot.com/2007/09/convert-utc-time-to-local-time-in_05.html

                Thanks,
                • 5. Re: Wrong ENQ_TIME
                  phcullen-Oracle
                  Hello,

                  You cannot change this behaviour. As suggested if you have a need to you can CAST the time as suggested.

                  In previous versions we effectively did this within the AQ$QUEUE_TABLE view but that and the DST issues led to a re-think. This means that AQ is not affected
                  by DST changes anymore but does mean you may need to cast the values.

                  Thanks
                  Peter
                  • 6. Re: Wrong ENQ_TIME
                    Jack Sam
                    Hi Peter,

                    Yes you correct casting is all what we are going to do. On the other hand, why DST is unique to enq_time? sysdate is working fine. Now we have to write cumbersome from_tz function which is a pain in the neck.
                    Thanks,

                    Edited by: Jack Sam on Feb 8, 2013 5:35 AM

                    Edited by: Jack Sam on Feb 8, 2013 5:37 AM