1

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" ?

2 Answers 2

7

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> 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> 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> insert into t1 (t) values (current_timestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> 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> 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> select t from t1;

T
---------------------------------------------------------------------------
29-DEC-16 10.36.32.100571 PM
5
  • SYSTIMESTAMP is returned in time zone of database server's operating system. Time zone of TIMESTAMP WITH LOCAL TIME ZONE is DBTIMEZONE - which can be different. Dec 29, 2016 at 15:33
  • @Balazs Actually you lost me when you started the explanation. Added to that what Wernfried just said.... Anyway, does it mean the actual data stored in the database is never seen BUT ORACLE will adjust it to the user's session time zone when the value is queried ?
    – Jason Krs
    Dec 29, 2016 at 15:35
  • Well, you can display actual data by CAST(... AS TIMESTAMP) or ... AT TIME ZONE DBTIMEZONE. Otherwise Oracle will adjust it to the user's session time zone when the value is queried. Dec 29, 2016 at 15:44
  • @Wernfried Domscheit Indeed. I have just never seen a database that used a different timezone on purpose. I had tried CAST(... AS TIMESTAMP) before I posted my answer, it did not display the actual data, that is why I used DUMP. Dec 29, 2016 at 15:58
  • @BalazsPapp, that surprises me - however I did not test it. Yes, normally DBTIMEZONE and database server's operating system time zone are the same. However, database operating system time zone may change any time (and typically it does changes twice a year for daylight saving time) but DBTIMEZONE cannot change once you use it, i.e. once you have TIMESTAMP WITH LOCAL TIME ZONE value stored in a table. Dec 29, 2016 at 16:17
1

Many times when you have to work with time zone data a common approach is:

Store all times internally at UTC time and display them on application level according to local user settings.

This is exactly what TIMESTAMP WITH LOCAL TIME ZONE is doing but on SQL level. The only difference is:

Store all times internally at DBTIMEZONE time and display them on application (i.e. at SQL) level according to local user settings (i.e. SESSIONTIMEZONE).

Once you have created a table with a TIMESTAMP WITH LOCAL TIME ZONE column and this table contains any data you cannot change DBTIMEZONE anymore - which sounds logical when you consider the statements above.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.