Introduction and example of several types of timestamp in Oracle _oracle

Comparison of several types of timestamp

TIMESTAMP

The timestamp type, unlike date, is that date cannot be accurate to milliseconds, and timestamp can be accurate to milliseconds, with a millisecond of 0-9 digits and a default of 6 bits.

Sql> select TP from Timestamp_test;
TP
--------------------------------------------------------------------------------
January-March-16 09.22.33.000000 Morning

TIMESTAMP with Time ZONE

The difference between TIMESTAMP with time ZONE and TIMESTAMP is that the former output shows the database time zone in which the value was stored and the output does not carry time zone.

Sql> select Tp_tz from Timestamp_test;
Tp_tz
--------------------------------------------------------------------------------
January-March-16 09.22.33.000000 Morning +08:00

The difference between TIMESTAMP with the zone and TIMESTAMP is that the output of the former is affected by the time zone, which changes with the time zone, and the latter is not affected by the time zone when it is stored in the database. That is, the former saves data in the local time zone of the database, and the output is converted to the client time zone output.

Sql> select Tp_l_tz from Timestamp_test;
Tp_l_tz
--------------------------------------------------------------------------------
January-March-16 09.22.33.000000 Morning

Practical Exercises

# Creating Timestamp_test Test Table sql> CREATE TABLE timestamp_test (dt DATE,TP Timestamp (6), Tp_tz Timestamp (6) with time Zone,tp_l_
TZ Timestamp (6) with the local time zone);
Table created # Add data to the test table sql> insert into timestamp_test values (sysdate,sysdate,sysdate,sysdate);
1 row inserted sql> commit;
Commit Complete # View the time zone of the database and the time zone of the current session sql> select Dbtimezone,sessiontimezone from dual;
Dbtimezone Sessiontimezone-------------------------------------------------------------------------------------
+00:00 +08:00 # View current time sql> select Sysdate from dual;
Sysdate-----------2016/3/1 9: # View data from the test table sql> select * from Timestamp_test; DT TP Tp_tz Tp_l_tz--------------------- ---------------------------------------------------------------------- ----------------------------------------- --------------------------------------- ------------------------------------------------------------------------ --------2016/3/19:01-March-16 09.22.33.000000 a.m. January-March-16 09.22.33.000000 a.m. +08:00 January-March-16
09.22.33.000000 Morning # Modify the time zone for the current session sql> alter sessions set time_zone= ' +10:00 ';
Session altered # View data from the modified test table for the current conversation time zone sql> select Dbtimezone,sessiontimezone from dual;
Dbtimezone Sessiontimezone-------------------------------------------------------------------------------------
+00:00 +10:00 sql> select * from Timestamp_test; DT TP Tp_tz Tp_l_tz--------------------- ---------------------------------------------------------------------- ----------------------------------------- --------------------------------------- ------------------------------------------------------------------------                      --------2016/3/1 9:01-March-16 09.22.33.000000 a.m. January-March-16 09.22.33.000000 a.m. +08:00 January-March-16 11.22.33.000000 Morning Oracle Backup and Recovery

Summarize

The above is the entire content of this article, I hope the content of this article for everyone's study or work can bring certain help, if you have questions you can message exchange.