FAQ
Can someone please tell me what I'm missing here ...

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE

+11:00

SQL> select sysdate, systimestamp, systimestamp - sysdate diff from dual;

SYSDATE SYSTIMESTAMP DIFF
------------------ -----------------------------------
----------------------------
25-OCT-10 25-OCT-10 10.22.02.244603 AM +11:00 +000000000

00:00:00.244603

SQL> alter session set time_zone = '+10:00';

Session altered.

SQL> select sysdate, systimestamp, systimestamp - sysdate diff from dual;

SYSDATE SYSTIMESTAMP DIFF
------------------ -----------------------------------
----------------------------
25-OCT-10 25-OCT-10 10.22.13.089965 AM +11:00 -000000000

00:59:59.910035

SQL> alter session set time_zone = '+12:00';

Session altered.

SQL> select sysdate, systimestamp, systimestamp - sysdate diff from dual;

SYSDATE SYSTIMESTAMP DIFF
------------------ -----------------------------------
----------------------------
25-OCT-10 25-OCT-10 10.22.19.962058 AM +11:00 +000000000

01:00:00.962058

As you can see, setting my session time zone makes no difference to the
value returned by SYSTIMESTAMP but makes a considerable difference to any
arithmetic performed using SYSTIMESTAMP.

Is this expected behaviour and if so, can someone please explain why?

Thanks a lot.

Steve

This email is intended solely for the use of the addressee and may
contain information that is confidential, proprietary, or both.
If you receive this email in error please immediately notify the
sender and delete the email.

Search Discussions

  • Steve Baldwin at Oct 24, 2010 at 11:35 pm
    Sorry to reply to my own post but I think I have a workaround ...

    SQL> alter session set time_zone = '+10:00';

    Session altered.

    SQL> select sysdate, systimestamp, (cast (systimestamp as timestamp)) -
    sysdate diff from dual;

    SYSDATE SYSTIMESTAMP DIFF
    ------------------ -----------------------------------
    ----------------------------

    25-Oct-10 10:32:36 25-OCT-10 10.32.36.472272 AM +11:00 +000000000
    00:00:00.472272

    SQL> alter session set time_zone = '+12:00';

    Session altered.

    SQL> select sysdate, systimestamp, (cast (systimestamp as timestamp)) -
    sysdate diff from dual;

    SYSDATE SYSTIMESTAMP DIFF
    ------------------ -----------------------------------
    ----------------------------

    25-Oct-10 10:32:41 25-OCT-10 10.32.41.376392 AM +11:00 +000000000
    00:00:00.376392

    It seems that if you are doing arithmetic with SYSTIMESTAMP you need to cast
    it as a TIMESTAMP to remove the time zone bit.

    Still not clear as to why but at least I have a 'solution'.

    Cheers,

    Steve

    On Mon, Oct 25, 2010 at 10:23 AM, Steve Baldwin
    wrote:
    Can someone please tell me what I'm missing here ...

    SQL> select sessiontimezone from dual;

    SESSIONTIMEZONE
    --------------------
    +11:00

    SQL> select sysdate, systimestamp, systimestamp - sysdate diff from dual;

    SYSDATE SYSTIMESTAMP DIFF
    ------------------ -----------------------------------
    ----------------------------
    25-OCT-10 25-OCT-10 10.22.02.244603 AM +11:00 +000000000
    00:00:00.244603

    SQL> alter session set time_zone = '+10:00';

    Session altered.

    SQL> select sysdate, systimestamp, systimestamp - sysdate diff from dual;

    SYSDATE SYSTIMESTAMP DIFF
    ------------------ -----------------------------------
    ----------------------------
    25-OCT-10 25-OCT-10 10.22.13.089965 AM +11:00 -000000000
    00:59:59.910035

    SQL> alter session set time_zone = '+12:00';

    Session altered.

    SQL> select sysdate, systimestamp, systimestamp - sysdate diff from dual;

    SYSDATE SYSTIMESTAMP DIFF
    ------------------ -----------------------------------
    ----------------------------
    25-OCT-10 25-OCT-10 10.22.19.962058 AM +11:00 +000000000
    01:00:00.962058

    As you can see, setting my session time zone makes no difference to the
    value returned by SYSTIMESTAMP but makes a considerable difference to any
    arithmetic performed using SYSTIMESTAMP.

    Is this expected behaviour and if so, can someone please explain why?

    Thanks a lot.

    Steve
    This email is intended solely for the use of the addressee and may
    contain information that is confidential, proprietary, or both.
    If you receive this email in error please immediately notify the
    sender and delete the email.
  • Andy Klock at Oct 26, 2010 at 8:15 pm
    When you cast to a timestamp, you have to explicitly tell it to include the
    time zone:

    andy_at_TEST>select systimestamp - cast(systimestamp as timestamp with time
    zone) from dual;

    SYSTIMESTAMP-CAST(SYSTIMESTAMPASTIMESTAMPWITHTIMEZONE)

    +000000000 00:00:00.000000

    On Tue, Oct 26, 2010 at 3:59 PM, Steve Baldwin
    wrote:
    Does this strike anyone as weird ?

    SQL> select sessiontimezone from dual;

    SESSIONTIMEZONE
    --------------------------------------------------------------------------------
    +11:00

    SQL> select systimestamp - cast(systimestamp as timestamp) from dual;

    SYSTIMESTAMP-CAST(SYSTIMESTAMPASTIMESTAMP)
    ---------------------------------------------------------------------------
    +000000000 00:00:00.000000

    SQL> alter session set time_zone = '+10:00';

    Session altered.

    SQL> select systimestamp - cast(systimestamp as timestamp) from dual;

    SYSTIMESTAMP-CAST(SYSTIMESTAMPASTIMESTAMP)
    ---------------------------------------------------------------------------
    -000000000 01:00:00.000000

    Steve

    On Tue, Oct 26, 2010 at 9:44 PM, Peter Hitchman
    wrote:
    Hi,
    What about using localtimestamp instead?

    Regards
    Pete
    --
    http://www.freelists.org/webpage/oracle-l


    ---------------------------------------------------------------------------------------
    This email is intended solely for the use of the addressee and may
    contain information that is confidential, proprietary, or both.
    If you receive this email in error please immediately notify the
    sender and delete the email.

    ---------------------------------------------------------------------------------------

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 24, '10 at 11:23p
activeOct 26, '10 at 8:15p
posts3
users2
websiteoracle.com

2 users in discussion

Steve Baldwin: 2 posts Andy Klock: 1 post

People

Translate

site design / logo © 2022 Grokbase