Timestamp with the local time zone type and timestamp with time zone

Timestamp with Loca time zone type syntax

tmestamp[(Fractional_seconds_precisions)] with the local zone

The biggest difference between the timestamp with the local time zone and the timesatamp with times zone is that the type converts to the database's time zone to hold the data when the user submits it to the database, that is, when the database is saved in the database native time zone. Oracle automatically converts the time to the current client when another user accesses the database.

Date type of DB: Date (no subtle time), Timestamp (excluding time zone, subtle time), Timestamp with timezone (maintain client's timezone), Timestamp with local times Zone (converts the client's timezone to the DB timezone corresponding time)

Example:

1. Create a table

CREATE TABLE Timestamp_test (
Time DATE,
TIMESTP TIMESTAMP (3),
Timestp_tz TIMESTAMP (3) with time ZONE,
Timestp_ltz TIMESTAMP (3) with local time ZONE)
2. Add Data

INSERT into Timestamp_test VALUES (sysdate,sysdate,sysdate,sysdate);

Commit

3, query the value of Dbtimezone and Sessiontimezone

Select Dbtimezone, sessiontimezone from dual;

DBTIME
------
Sessiontimezone
---------------------------------------------------------------------------
+00:00
+08:00
4, view the value of the data

Sql> SELECT * from Timestamp_test;

Time
--------------
Timestp
--------------------------------------------------------------
Timestp_tz
--------------------------------------------------------------
Timestp_ltz
--------------------------------------------------------------
February-June-10
February-June-10 11.21.10.000 a.m.
February-June-10 11.21.10.000 a.m. +08:00
February-June-10 11.21.10.000 a.m.
5, modify the Time_zone value of the session

Alter session set Time_zone= ' +10:00 ';

6, view the results

Sql> SELECT * from Timestamp_test;

Time
--------------
Timestp
-----------------------------------------------------
Timestp_tz
-----------------------------------------------------
Timestp_ltz
-----------------------------------------------------
February-June-10
February-June-10 11.21.10.000 a.m.
February-June-10 11.21.10.000 a.m. +08:00
February-June-10 01.21.10.000 pm

7, from the above experiment can see the difference between the two, when the session time zone from 8 to 10 is, the time increased by two hours

Add a record to the table
INSERT into timestamp_test values (

To_timestamp_tz (' 2010-12-01 23:12:56.788-12:44 ', ' Yyyy-mm-dd HH24:MI:SS. FF Tzh:tzm '),

To_timestamp_tz (' 2010-12-01 23:12:56.788-12:44 ', ' Yyyy-mm-dd HH24:MI:SS. FF Tzh:tzm '),

To_timestamp_tz (' 2010-12-01 23:12:56.788-12:44 ', ' Yyyy-mm-dd HH24:MI:SS. FF Tzh:tzm '),

To_timestamp_tz (' 2010-12-0123:12:56.788-12:44 ', ' Yyyy-mm-dd HH24:MI:SS. FF tzh:tzm '));

(Tzh: Hours in the time zone, TZM: points in time zones)

Here I specified the time zone when the data was added to-12:44, the result of the query is

Time
--------------
Timestp
---------------------------------------------------------------------------
Timestp_tz
---------------------------------------------------------------------------
Timestp_ltz
---------------------------------------------------------------------------
January-December-10
January-December-10 11.12.56.788 pm
January-December-10 11.12.56.788 pm -12:44
February-December-10 09.56.56.788 pm

Time
--------------
TIMESTP
------------------------------------------------------------------ ---------
Timestp_tz
---------------------------------------------------------------------------
Timestp_ltz
---------------------------------------------------------------------------
February-June -10
February-June-10 11.21.10.000 a.m.
February-June-10 11.21.10.000 a.m. +08:00
February-June-10 01.21.10.000 afternoon
specified when adding data because the current user's time zone is +10:00 Time zone dead-12:44, the difference between 22 hours and 44 minutes