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