SQL & PL/SQL

Hi,

According to the docs "SYSTIMESTAMP returns the system date, including fractional seconds and time zone, of the system on which the database resides." and "CURRENT_TIMESTAMP returns the current date and time in the session time zone".

Here's the behaviour that strikes me as strange ...

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Oct 26 09:33:57 2010

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

SQL> select dbtimezone from dual;

DBT
---
GMT

SQL> select sessiontimezone from dual;

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

SQL> select systimestamp - current_timestamp from dual;

SYSTIMESTAMP-CURRENT_TIMESTAMP
---------------------------------------------------------------------------
-000000000 00:00:00.000005

SQL> select sysdate - systimestamp from dual;

SYSDATE-SYSTIMESTAMP
---------------------------------------------------------------------------
-000000000 00:00:00.015974

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

Session altered.

SQL> select systimestamp - current_timestamp from dual;

SYSTIMESTAMP-CURRENT_TIMESTAMP
---------------------------------------------------------------------------
-000000000 00:00:00.000004

SQL> select sysdate - systimestamp from dual;

SYSDATE-SYSTIMESTAMP
---------------------------------------------------------------------------
+000000000 00:59:59.355370

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

SYSTIMESTAMP                        CURRENT_TIMESTAMP                   DIFF
----------------------------------- ----------------------------------- ----------------------------
26-OCT-10 09.45.19.425892 AM +11:00 26-OCT-10 08.45.19.425899 AM +10:00 -000000000 00:00:00.000007

As you can see, to start with, the difference between SYSDATE and SYSTIMESTAMP is merely the fractional part of the second - as expected. Similarly, the difference between SYSTIMESTAMP and CURRENT_TIMESTAMP is effectively zero. Again, since my session timezone defaults to the system timezone, that is what I expect.

However, when I adjust my session time zone by one hour I would have expected the difference between SYSTIMESTAMP and SYSDATE to remain as < 1 second and the difference between SYSTIMESTAMP and CURRENT_TIMESTAMP to be one hour. In fact the opposite is what I see.

The last select is really weird.

Is this a bug?

If not, why not?

Thanks,

Steve