Timestamp with Time Zone data type in Oracle has this weird feature where it retains the format of timezone from insertion when we select it without any formatting;
With TZR
UPDATE X SET COLUMN_A = TO_TIMESTAMP_TZ('19-JUL-18 01.53.16.381566000 PM UTC',
'DD-MON-RR HH.MI.SSXFF AM TZR') ...
If I select this column directly;
> SELECT COLUMN_A WHERE ...
19-JUL-18 01.53.16.381566000 PM UTC
With TZH:TZM
UPDATE X SET COLUMN_A = TO_TIMESTAMP_TZ('19-JUL-18 01.53.16.381566000 PM +00:00',
'DD-MON-RR HH.MI.SSXFF AM TZH:TZM') ...
If I select this column directly;
> SELECT COLUMN_A WHERE ...
19-JUL-18 01.53.16.381566000 PM +00:00
What is the reason behind this feature, and is there a way to control this such a way that, I can set all such values to TZH:TZM formatting for example. I am not talking about session specific NLS_TIMESTAMP_TZ_FORMAT, even though it is pretty useful for SELECT cases
Time zone UTC is different to time zone +00:00. You just get what you have inserted before.
Maybe the answer is more clear when I say: "Time zone Europe/Zurich is different to time zone +02:00." Currently both are 2 hour ahead UTC, in that manner they are equal. However in winter season this will change.
Neither UTC nor +00:00 apply daylight saving times, so the difference is not so obvious but internally they are different.