Oracle timestamp with local time zone values transparent translation
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
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).