Re: Strange (?) behaviour with SYSTIMESTAMP
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-lReceived on Sun Oct 24 2010 - 18:35:45 CDT