Re: Strange (?) behaviour with SYSTIMESTAMP

From: Steve Baldwin <stbaldwin_at_multiservice.com>
Date: Mon, 25 Oct 2010 10:35:45 +1100
Message-ID: <AANLkTimQkF06R4=wurWGB0s-arxYFF2JGezE6H7G8u61_at_mail.gmail.com>



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 <stbaldwin_at_multiservice.com>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.
--
http://www.freelists.org/webpage/oracle-l
Received on Sun Oct 24 2010 - 18:35:45 CDT

Original text of this message