This discussion is archived
6 Replies Latest reply: Feb 8, 2013 5:37 AM by 988414 RSS

Wrong ENQ_TIME

988414 Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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
    988414 Newbie
    Currently Being Moderated
    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
    user648708 Explorer
    Currently Being Moderated
    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
    988414 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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
    988414 Newbie
    Currently Being Moderated
    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

Legend

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