Difference between TIMESTAMP, TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE-CodePudding

I ran the same statements in two different databases: my Local DB and Oracle Live SQL.

CREATE TABLE test(
    timestamp TIMESTAMP DEFAULT SYSDATE,
    timestamp_tmz TIMESTAMP WITH TIME ZONE DEFAULT SYSDATE,
    timestamp_local_tmz TIMESTAMP WITH LOCAL TIME ZONE DEFAULT SYSDATE
);

INSERT INTO test VALUES (DEFAULT, DEFAULT, DEFAULT);

SELECT * FROM test;

(all statements were executed at approximately the same time - 09:35 AM CET)

Results from my Local DB:

TIMESTAMP: 10-JAN-23 09.35.32.000000000 AM
TIMESTAMP WITH TIME ZONE: 10-JAN-23 09.35.32.000000000 AM EUROPE/BERLIN
TIMESTAMP WITH LOCAL TIME ZONE: 10-JAN-23 09.35.32.000000000 AM

Results from Oracle Live:

TIMESTAMP: 10-JAN-23 08.35.44.000000 AM 
TIMESTAMP WITH TIME ZONE: 10-JAN-23 08.35.44.000000 AM US/PACIFIC   
TIMESTAMP WITH LOCAL TIME ZONE: 10-JAN-23 08.35.44.000000 AM

After seeing the results, my questions are:

Why is Oracle Live's TIMESTAMP showing date in a different time zone (8.35 AM instead of 9.35 AM)?
Why does Oracle Live's TIMESTAMP WITH TIME ZONE return US/PACIFIC as time zone?
Is there any difference between TIMESTAMP and TIME STAMP WITH LOCAL TIME ZONE?

CodePudding user response：

The different data types are described in the documentation.

The TIMESTAMP data type is an extension of the DATE data type. It stores year, month, day, hour, minute, and second values. It also stores fractional seconds, which are not stored by the DATE data type.

TIMESTAMP WITH TIME ZONE is a variant of TIMESTAMP that includes a time zone region name or time zone offset in its value.

TIMESTAMP WITH LOCAL TIME ZONE is another variant of TIMESTAMP. It differs from TIMESTAMP WITH TIME ZONE as follows: 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 Database returns it in the users' local session time zone.

You are seeing a difference because you have different timezones, and you are defaulting the values to SYSDATE, which is the system DATE.

In your local database the system time zone (select dbtimezone from dual) seems to be based on CET, while the Live SQL database seems to be based on UTC, as Oracle recommends. As CET is an hour ahead of UTC/GMT, that explains the one-hour difference.

The TIMESTAMP value is just a simple cast, i.e. cast(SYSDATE as TIMESTAMP), so you get the same value you would if you queried SYSDATE directly, with zero fractional seconds added.

For the TIMESTAMP WITH TIME ZONE it has to store a time zone, and it has to get that from somewhere, and by default it uses your session time zone, not the database time zone. In your local DB that also seems to be CET, but Live SQL is defaulting the session time zone to US Pacific time - not unreasonable, given where Oracle is based. So now it's effectively doing from_tz(cast(SYSDATE as TIMESTAMP), SESSIONTIMEZONE) for that value, where for you SESSIONTIMEZONE is CET in one database and US/Pacific in the other.

For the TIMESTAMP WITH LOCAL TIME ZONE it is doing the same, but then normalising that back to the database time zone for storage (effectively cast(from_tz(cast(SYSDATE as TIMESTAMP), SESSIONTIMEZONE) at time zone DBTIMEZONE as TIMESTAMP) - not actually that internally, but gives you the idea), and converting back from the database time zone to your session time zone again when it is queried.

In both databases, if you alter session set time_zone = ... before inserting, and again to a different value before querying, then you'll see different results - the displayed time portion will stay the same for the first two columns, but the time zone will change for the WITH TIME ZONE, and the time will change for the WITH LOCAL TIME ZONE.

fiddle with different session time zones.

You can read more about all of this behaviour in the documentation I already linked to above.

If you use SYSTIMESTAMP instead of SYSDATE as the default for all of your columns then you will avoid the implicit conversion to your session time zone for the WITH TIME ZONE value, and that will always show the database time zone. The LOCAL column will still display in your session time zone, but they will all represent the same time. You will also still see the one-hour difference between the two databases, because they have different database time zones. You could consider defaulting the plain timestamp to sys_extract_utc(SYSTIMESTAMP), or defaulting them all (or at least the first two) to SYSTIMESTAMP at time zone 'UTC'.

fiddle with UTC-normalised values.