Oracle Database Discussions

SQL> create table tz_table
2 (time_stamp_tz TIMESTAMP WITH TIME ZONE,
3 time_stamp_ltz TIMESTAMP WITH LOCAL TIME ZONE);

Table created.

SQL> insert into tz_table values (systimestamp,systimestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> select time_stamp_tz, time_stamp_ltz from tz_table;

TIME_STAMP_TZ
---------------------------------------------------------------------------
TIME_STAMP_LTZ
---------------------------------------------------------------------------
26-OCT-10 11.31.16.431815 +01:00
26-OCT-10 11.31.16.431815


SQL> set linesize 400
SQL> select time_stamp_tz, time_stamp_ltz from tz_table;

TIME_STAMP_TZ TIME_STAMP_LTZ
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
26-OCT-10 11.31.16.431815 +01:00 26-OCT-10 11.31.16.431815

SQL> alter session set time_zone='UTC';

Session altered.

SQL> select time_stamp_tz, time_stamp_ltz from tz_table;

TIME_STAMP_TZ TIME_STAMP_LTZ
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
26-OCT-10 11.31.16.431815 +01:00 26-OCT-10 10.31.16.431815

SQL> select time_stamp_tz, time_stamp_ltz from tz_table;

TIME_STAMP_TZ TIME_STAMP_LTZ
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
26-OCT-10 11.31.16.431815 +01:00 26-OCT-10 10.31.16.431815

SQL> select dbtimezone,sessiontimezone from dual;

DBTIME SESSIONTIMEZONE
------ ---------------------------------------------------------------------------
+00:00 UTC

SQL> select dbtimezone,sessiontimezone,systimestamp from dual;

DBTIME SESSIONTIMEZONE SYSTIMESTAMP
------ --------------------------------------------------------------------------- ---------------------------------------------------------------------------
+00:00 UTC 26-OCT-10 11.33.56.074744 +01:00

SQL> set linesize 500
SQL> select dbtimezone,sessiontimezone,systimestamp from dual;

DBTIME SESSIONTIMEZONE SYSTIMESTAMP
------ --------------------------------------------------------------------------- ---------------------------------------------------------------------------
+00:00 UTC 26-OCT-10 11.34.14.047663 +01:00

SQL>
SQL> select * from nls_database_parameters
2 ;

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8MSWIN1252
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 11.2.0.1.0

20 rows selected.



My questions are why dbtimezone and systimestamp are different. Our developers expect systimestamp to see as +00:00.
Also time_stamp_ltz TIMESTAMP WITH LOCAL TIME ZONE does not output zone "26-OCT-10 10.31.16.431815"

Do I need to change any database parameters to view systimestamp same as dbtimezone and what datatype to use i.e TIMESTAMP WITH LOCAL TIME ZONE or TIMESTAMP WITH TIME ZONE when inserting the data?

thanks