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

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.