Strange (?) behaviour with SYSTIMESTAMP

From: Steve Baldwin <stbaldwin_at_multiservice.com>
Date: Mon, 25 Oct 2010 10:23:21 +1100
Message-ID: <AANLkTi=2vrcaqkNVhQC4ZO0tjNm23sd_DL8qRX_Ovara_at_mail.gmail.com>

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.

---

--

http://www.freelists.org/webpage/oracle-l Received on Sun Oct 24 2010 - 18:23:21 CDT