Can you help clear confusion of ORACLE Timestamp with Local Time Zone? Below is a description of ORACLE TIMESTAMP WITH LOCAL TIME ZONE


  ...data stored in the database is normalized to the database time zone, and the time zone offset is not stored as part of the column data. When users retrieve the data, Oracle returns it in the users' local session time zone.


What does data stored in the database is normalized to the database time zone means ? Normalized...how ? 

If it is normalized, why isn't it returned accordingly with Server's time zone instead of the users' local session time zone... How is it "session" and not "server" ?
Normalized means that the timezone information is not stored. When storing such data, the timestamp value is automatically adjusted to be in the same time zone as the database server. When retreiving such data, the timestamp value is automatically adjusted to be in the same time zone as the client.

For example, I simulate that my client is in a different time zone:

$ export TZ=America/Los_Angeles
$ sqlplus user/password@orcl


Create a table to store timestamp values:

SQL&gt; create table t1 (t timestamp with local time zone);

Table created.


Now notice how my local (client) time is different (current_timestamp) than the server time (systimestamp):

SQL&gt; select current_timestamp, systimestamp from dual;

CURRENT_TIMESTAMP                   SYSTIMESTAMP
----------------------------------- -----------------------------------
29-DEC-16 06.36.09.364628 AM -08:00 29-DEC-16 03.36.09.364623 PM +01:00


Now I insert my local time:

SQL&gt; insert into t1 (t) values (current_timestamp);

1 row created.

SQL&gt; commit;

Commit complete.

SQL&gt; select t from t1;

T
---------------------------------------------------------------------------
29-DEC-16 06.36.32.100571 AM


Just what I expected. But if I examine the real contents:

SQL&gt; select dump(t) from t1;

DUMP(T)
----------------------------------------------------
Typ=231 Len=11: 120,116,12,29,16,37,33,5,254,151,120


TIMESTAMP is stored as:


1st byte: First two digits of the year: 20 + 100 = 120
2nd byte: Last two digits in the year: 16 + 100 = 116
3rd byte: Month: 12
4th byte: Day: 29
5th byte: Hour: 15 + 1 = 16
6th byte: Minute: 36 + 1 = 37
7th byte: Second: 32 + 1 = 33
8th-11th bytes: fractions of second


The timezone information would be in the 12th and 13th byte, but it is not stored with this datatype.

So the actual data stored is 2016-12-29 15:36:32:..., because my database is in the CET timezone.

If I pretend to be in another different timezone, my query will return a timestamp adjusted to that timezone:

$ export TZ=Asia/Hong_Kong
$ sqlplus user/password@orcl


SQL&gt; select t from t1;

T
---------------------------------------------------------------------------
29-DEC-16 10.36.32.100571 PM