Oracle timestamp with local time zone values transparent translation

Question

As far as I know, TIMESTAMP WITH LOCAL TIME ZONE values are transparently translated to and from the user's session time zone. But my values read from the database aren't the same as previously inserted. Is there a database or session parameter I can tweak to fix this?

Here is my test case:

select systimestamp(0) from dual;

-- SYSTIMESTAMP   15/03/2017 19:01:13 +03:00

select dbtimezone from dual;

-- DBTIMEZONE     -07:00

create table test_timestamps
(
    id number generated by default on null as identity,
    systimestamp_col timestamp(0) with local time zone default on null systimestamp,
    sysdate_col timestamp(0) with local time zone default on null sysdate,
    current_timestamp_col timestamp(0) with local time zone default on null current_timestamp(0),
    date_col timestamp(0) with local time zone
);

alter session set time_zone='0:00';

insert into test_timestamps(date_col)
values (timestamp '2017-03-15 19:02:00');

select * from test_timestamps;

-- ID                                 1
-- SYSTIMESTAMP_COL                   15/03/2017 9:02:19
-- SYSDATE_COL                        15/03/2017 12:02:18
-- CURRENT_TIMESTAMP_COL              15/03/2017 9:02:19
-- DATE_COL                           15/03/2017 12:02:00

delete from test_timestamps;

alter session set time_zone='+3:00';

insert into test_timestamps(date_col)
values (timestamp '2017-03-15 19:05:00');

select * from test_timestamps;

-- ID                                 2
-- SYSTIMESTAMP_COL                   15/03/2017 12:05:43
-- SYSDATE_COL                        15/03/2017 12:05:43
-- CURRENT_TIMESTAMP_COL              15/03/2017 12:05:43
-- DATE_COL                           15/03/2017 12:05:00

I'm particularly confused about the DATE_COL value. As far as I know, the value I read from DATE_COL should be the same as I inserted no matter what the current session's TIME_ZONE is (as long as it's not changed between my insert and select).

I'm also puzzled with SYSTIMESTAMP default values.

SELECT SYSTIMESTAMP FROM DUAL always returns my server's timestamp with '+3:00' time zone, regardless of the current user session's time zone. But if I use SYSTIMESTAMP as the default value for the column, it gets translated.

What I'd like to see is this:

-- for a user in my time zone
alter session set time_zone='+3:00';

insert into test_timestamps(date_col)
values (timestamp '2017-03-15 19:15:00');

select id, systimestamp_col, date_col from test_timestamps;

-- ID                                 3
-- SYSTIMESTAMP_COL                   15/03/2017 19:15:00
-- DATE_COL                           15/03/2017 19:15:00

-- same data from a GMT user's point of view
alter session set time_zone='+0:00';

select id, systimestamp_col, date_col from test_timestamps;

-- ID                                 3
-- SYSTIMESTAMP_COL                   15/03/2017 16:15:00
-- DATE_COL                           15/03/2017 16:15:00

Is that possible or am I missing something?

UPD. Here is my LiveSQL script. It looks like it should work like I described, so I guess something might be wrong with my database setup.

Answer 1

TIMESTAMP WITH LOCAL TIME ZONE works like this: When you have to work with time zones in your application then a common approach is

Store all times internally as UTC and convert them to current user local time zone on application level.

That's exactly how TIMESTAMP WITH LOCAL TIME ZONE works - the only difference is

Store all times internally as DBTIMEZONE and convert them to current user local time zone on application level.

For that reason you cannot change DBTIMEZONE (with ALTER DATABASE SET TIME_ZONE='...';) on your database anymore if the database contains a table with a TIMESTAMP WITH LOCAL TIME ZONE column and the column contains data.

SYSTIMESTAMP is retured in time zone of database server's operating system. DBTIMEZONE is not the time zone of SYSTIMESTAMP or SYSDATE.

DBTIMEZONE defines the internal storage format of TIMESTAMP WITH LOCAL TIME ZONE data type columns. Forget this, I cannot imagine any use-case where you would need it.

Actually your table is equivalent to this select:

select 
   CAST(systimestamp AS timestamp(0) with local time zone) as SYSTIMESTAMP_COL,
   CAST(sysdate AS timestamp(0) with local time zone) as SYSDATE_COL,
   CAST(current_timestamp AS timestamp(0) with local time zone) as CURRENT_TIMESTAMP_COL,
   CAST(timestamp '2017-03-15 19:02:00' AS timestamp(0) with local time zone) as DATE_COL
from dual;

When you make CAST({time without time zone} with local time zone) then you try to convert a date/time value without any time zone information to a date/time value with time zone. In principle this is not possible because Oracle lacks the time zone information, so Oracle assumes a time zone. If you make such cast then Oracle always considers {time without time zone} as given in SESSIONTIMEZONE (in the moment of conversion).

So CAST(sysdate AS timestamp(0) with local time zone) is equivalent to

CAST(FROM_TZ(TO_TIMESTAMP(SYSDATE), SESSIONTIMEZONE) AS TIMESTAMP(0) WITH LOCAL TIME ZONE)` 

resp. CAST(timestamp '2017-03-15 19:02:00' AS timestamp(0) with local time zone) means

CAST(FROM_TZ(TIMESTAMP '2017-03-15 19:02:00', SESSIONTIMEZONE) AS TIMESTAMP(0) WITH LOCAL TIME ZONE)

For SYSDATE this is actually wrong, because SYSDATE is given in time zone of database server's operating system not in SESSIONTIMEZONE. For the second one it depends on your intention whether result is correct or not.

SYSTIMESTAMP returns value TIMESTAMP WITH TIME ZONE, it is always independent from your current SESSIONTIMEZONE. But if you convert to TIMESTAMP WITH LOCAL TIME ZONE it gets converted to your current local time zone, of course. You can also use CURRENT_TIMESTAMP or SYSTIMESTAMP AT LOCAL which does more or less the same.

This code

select systimestamp(0) from dual;

-- SYSTIMESTAMP   15/03/2017 19:01:13 +03:00

alter session set time_zone='0:00';

insert into test_timestamps(date_col)
values (timestamp '2017-03-15 19:02:00');

select * from test_timestamps;

-- ID                                 1
-- SYSTIMESTAMP_COL                   15/03/2017 9:02:19
-- SYSDATE_COL                        15/03/2017 12:02:18
-- CURRENT_TIMESTAMP_COL              15/03/2017 9:02:19
-- DATE_COL                           15/03/2017 12:02:00

seems to be wrong. Result should be

-- SYSTIMESTAMP_COL                   15/03/2017 16:01:14
-- SYSDATE_COL                        15/03/2017 19:01:14
-- CURRENT_TIMESTAMP_COL              15/03/2017 16:01:14
-- DATE_COL                           15/03/2017 19:02:00

The differences look as it should be but the absolute values seems to be "faked" (or there is a real issue with your database).

Answer 2

Our DBA have found the reason for this behavior. We use a multitenant container database (Oracle 12c CDB). The issue appears when the root database's DBTIMEZONE differs from the pluggable database's (PDB) DBTIMEZONE.

In our case, we had:

• root DBTIMEZONE — "0:00"
• PDB DBTIMEZONE — "-7:00"

Once the DBA had all DBTIMEZONEs set to the same value, the issue's gone. As far as I understand, he'd changed the root DBTIMEZONE to "-7:00". Now my test case runs exactly the same as on the livesql.oracle.com sandbox and the selected timestamp is the same as inserted.