Oracle基本資料型別儲存格式淺析（三）——日期型別（四）
Oracle基本資料型別儲存格式淺析（三）——日期型別（四）
本文對TIMESTAMP WITH LOCAL TIME ZONE和TIMESTAMP WITH TIME ZONE型別的儲存格式進行簡單的說明。
SQL> CREATE TABLE TEST_TIMESTAMP(TIME1 TIMESTAMP(9), TIME2 TIMESTAMP(6) WITH LOCAL TIME ZONE,
2 TIME3 TIMESTAMP(4) WITH TIME ZONE);
表已建立。
SQL> INSERT INTO TEST_TIMESTAMP VALUES (SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP);
已建立 1 行。
SQL> SELECT * FROM TEST_TIMESTAMP;
TIME1
----------------------------------------------------
TIME2
----------------------------------------------------
TIME3
----------------------------------------------------
11-1月 -05 11.08.15.027000000 下午
11-1月 -05 11.08.15.027000 下午
11-1月 -05 11.08.15.0270 下午 +08:00
SQL> SELECT DUMP(TIME1, 16), DUMP(TIME2, 16), DUMP(TIME3, 16) FROM TEST_TIMESTAMP;
DUMP(TIME1,16)
-------------------------------------------------------------
DUMP(TIME2,16)
-------------------------------------------------------------
DUMP(TIME3,16)
-------------------------------------------------------------
Typ=180 Len=11: 78,69,1,b,18,9,10,1,9b,fc,c0
Typ=231 Len=11: 78,69,1,b,18,9,10,1,9b,fc,c0
Typ=181 Len=13: 78,69,1,b,10,9,10,1,9b,fc,c0,1c,3c
可以發現，如果客戶端和資料庫中的時區是一致的，那麼TIMESTAMP和TIMESTAMP WITH LOCAL TIME ZONE儲存的資料是完全一樣的。
TIMESTAMP WITH TIME ZONE則略有不同，它儲存的是0時區的時間，和所處的時區資訊。
修改客戶端主機的時區，由東8區（+8區）改為0時區。
SQL> INSERT INTO TEST_TIMESTAMP VALUES (SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP);
已建立 1 行。
修改客戶端主機的時區，改為西5區（-5時區）。
SQL> INSERT INTO TEST_TIMESTAMP VALUES (SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP);
已建立 1 行。
修改客戶端主機的時區，改為西12區（-12時區）。
SQL> INSERT INTO TEST_TIMESTAMP VALUES (SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP);
已建立 1 行。
修改客戶端主機的時區，改為東13區（+13時區）。
SQL> INSERT INTO TEST_TIMESTAMP VALUES (SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP);
已建立 1 行。
修改客戶端主機的時區，改為西3.5區（-3.5時區）。
SQL> INSERT INTO TEST_TIMESTAMP VALUES (SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP);
已建立 1 行。
修改客戶端主機的時區，改為東9.5區（+9.5時區）。
SQL> INSERT INTO TEST_TIMESTAMP VALUES (SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP);
已建立 1 行。
SQL> COMMIT;
提交完成。
修改客戶端主機的時區，改回東8區（+8時區）。
SQL> SELECT * FROM TEST_TIMESTAMP;
TIME1
-----------------------------------------------
TIME2
-----------------------------------------------
TIME3
-----------------------------------------------
11-1月 -05 11.08.15.027000000 下午
11-1月 -05 11.08.15.027000 下午
11-1月 -05 11.08.15.0270 下午 +08:00
11-1月 -05 03.11.43.746000000 下午
11-1月 -05 11.11.43.746000 下午
11-1月 -05 03.11.43.7460 下午 +00:00
11-1月 -05 10.14.08.987000000 上午
11-1月 -05 11.14.08.987000 下午
11-1月 -05 10.14.08.9870 上午 -05:00
11-1月 -05 03.15.01.732000000 上午
11-1月 -05 11.15.01.732000 下午
11-1月 -05 03.15.01.7320 上午 -12:00
12-1月 -05 04.20.21.522000000 上午
11-1月 -05 11.20.21.522000 下午
12-1月 -05 04.20.21.5220 上午 +13:00
11-1月 -05 02.15.16.567000000 下午
12-1月 -05 01.45.16.567000 上午
11-1月 -05 02.15.16.5670 下午 -03:30
12-1月 -05 03.16.54.992000000 上午
12-1月 -05 01.46.54.992000 上午
12-1月 -05 03.16.54.9920 上午 +09:30
已選擇7行。
SQL> SELECT DUMP(TIME1, 16), DUMP(TIME2, 16), DUMP(TIME3, 16) FROM TEST_TIMESTAMP;
DUMP(TIME1,16)
-------------------------------------------------------------
DUMP(TIME2,16)
-------------------------------------------------------------
DUMP(TIME3,16)
-------------------------------------------------------------
Typ=180 Len=11: 78,69,1,b,18,9,10,1,9b,fc,c0
Typ=231 Len=11: 78,69,1,b,18,9,10,1,9b,fc,c0
Typ=181 Len=13: 78,69,1,b,10,9,10,1,9b,fc,c0,1c,3c
Typ=180 Len=11: 78,69,1,b,10,c,2c,2c,77,e,80
Typ=231 Len=11: 78,69,1,b,18,c,2c,2c,77,e,80
Typ=181 Len=13: 78,69,1,b,10,c,2c,2c,77,e,80,14,3c
Typ=180 Len=11: 78,69,1,b,b,f,9,3a,d4,6c,c0
Typ=231 Len=11: 78,69,1,b,18,f,9,3a,d4,6c,c0
Typ=181 Len=13: 78,69,1,b,10,f,9,3a,d4,6c,c0,f,3c
Typ=180 Len=11: 78,69,1,b,4,10,2,2b,a1,6f,0
Typ=231 Len=11: 78,69,1,b,18,10,2,2b,a1,6f,0
Typ=181 Len=13: 78,69,1,b,10,10,2,2b,a1,6f,0,8,3c
Typ=180 Len=11: 78,69,1,c,5,15,16,1f,1d,16,80
Typ=231 Len=11: 78,69,1,b,18,15,16,1f,1d,16,80
Typ=181 Len=13: 78,69,1,b,10,15,16,1f,1d,16,80,21,3c
Typ=180 Len=11: 78,69,1,b,f,10,11,21,cb,bb,c0
Typ=231 Len=11: 78,69,1,c,2,2e,11,21,cb,bb,c0
Typ=181 Len=13: 78,69,1,b,12,2e,11,21,cb,bb,c0,11,1e
Typ=180 Len=11: 78,69,1,c,4,11,37,3b,20,b8,0
Typ=231 Len=11: 78,69,1,c,2,2f,37,3b,20,b8,0
Typ=181 Len=13: 78,69,1,b,12,2f,37,3b,20,b8,0,1d,5a
SQL> SELECT TO_NUMBER('1C', 'XXX'), TO_NUMBER('3C', 'XXX') FROM DUAL;
TO_NUMBER('1C','XXX') TO_NUMBER('3C','XXX')
--------------------- ---------------------
28 60
SQL> SELECT TO_NUMBER('14', 'XXX'), TO_NUMBER('3C', 'XXX'), TO_NUMBER('143C', 'XXXXXXX') FROM DUAL;
TO_NUMBER('14','XXX') TO_NUMBER('3C','XXX')
--------------------- ---------------------
20 60
SQL> SELECT TO_NUMBER('3C', 'XXX') , TO_NUMBER('1E', 'XXX'), TO_NUMBER('5A', 'XXX') FROM DUAL;
TO_NUMBER('3C','XXX') TO_NUMBER('1E','XXX') TO_NUMBER('5A','XXX')
--------------------- --------------------- -------------------
60 30 90
可以看出，修改時區會導致系統TIMESTAMP時間發生變化，但是對於TIMESTAMP WITH LOCAL TIME ZONE型別，總是將系統的時間轉化到資料庫伺服器上時區的時間進行儲存。
TIMESTAMP WITH TIME ZONE儲存的是當前時間轉化到0時區的對應的時間，並透過最後兩位來儲存時區資訊。
第一位表示時區的小時部分。0時區用0x14表示。東n區在這個基礎上加n，西n區在這個基礎上減n。我們所處的東8區表示為0x1C。西5區表示為0xF。
第二位表示時區的分鐘部分。標準是0x3C，即60分鐘。對於東時區的半區，在這個基礎上加上30分鐘，如果是西時區，則減去30分鐘。
SQL> CREATE TABLE TEST_TIMESTAMP(TIME1 TIMESTAMP(9), TIME2 TIMESTAMP(6) WITH LOCAL TIME ZONE,
2 TIME3 TIMESTAMP(4) WITH TIME ZONE);
表已建立。
SQL> INSERT INTO TEST_TIMESTAMP VALUES (SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP);
已建立 1 行。
SQL> SELECT * FROM TEST_TIMESTAMP;
TIME1
----------------------------------------------------
TIME2
----------------------------------------------------
TIME3
----------------------------------------------------
11-1月 -05 11.08.15.027000000 下午
11-1月 -05 11.08.15.027000 下午
11-1月 -05 11.08.15.0270 下午 +08:00
SQL> SELECT DUMP(TIME1, 16), DUMP(TIME2, 16), DUMP(TIME3, 16) FROM TEST_TIMESTAMP;
DUMP(TIME1,16)
-------------------------------------------------------------
DUMP(TIME2,16)
-------------------------------------------------------------
DUMP(TIME3,16)
-------------------------------------------------------------
Typ=180 Len=11: 78,69,1,b,18,9,10,1,9b,fc,c0
Typ=231 Len=11: 78,69,1,b,18,9,10,1,9b,fc,c0
Typ=181 Len=13: 78,69,1,b,10,9,10,1,9b,fc,c0,1c,3c
可以發現，如果客戶端和資料庫中的時區是一致的，那麼TIMESTAMP和TIMESTAMP WITH LOCAL TIME ZONE儲存的資料是完全一樣的。
TIMESTAMP WITH TIME ZONE則略有不同，它儲存的是0時區的時間，和所處的時區資訊。
修改客戶端主機的時區，由東8區（+8區）改為0時區。
SQL> INSERT INTO TEST_TIMESTAMP VALUES (SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP);
已建立 1 行。
修改客戶端主機的時區，改為西5區（-5時區）。
SQL> INSERT INTO TEST_TIMESTAMP VALUES (SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP);
已建立 1 行。
修改客戶端主機的時區，改為西12區（-12時區）。
SQL> INSERT INTO TEST_TIMESTAMP VALUES (SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP);
已建立 1 行。
修改客戶端主機的時區，改為東13區（+13時區）。
SQL> INSERT INTO TEST_TIMESTAMP VALUES (SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP);
已建立 1 行。
修改客戶端主機的時區，改為西3.5區（-3.5時區）。
SQL> INSERT INTO TEST_TIMESTAMP VALUES (SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP);
已建立 1 行。
修改客戶端主機的時區，改為東9.5區（+9.5時區）。
SQL> INSERT INTO TEST_TIMESTAMP VALUES (SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP);
已建立 1 行。
SQL> COMMIT;
提交完成。
修改客戶端主機的時區，改回東8區（+8時區）。
SQL> SELECT * FROM TEST_TIMESTAMP;
TIME1
-----------------------------------------------
TIME2
-----------------------------------------------
TIME3
-----------------------------------------------
11-1月 -05 11.08.15.027000000 下午
11-1月 -05 11.08.15.027000 下午
11-1月 -05 11.08.15.0270 下午 +08:00
11-1月 -05 03.11.43.746000000 下午
11-1月 -05 11.11.43.746000 下午
11-1月 -05 03.11.43.7460 下午 +00:00
11-1月 -05 10.14.08.987000000 上午
11-1月 -05 11.14.08.987000 下午
11-1月 -05 10.14.08.9870 上午 -05:00
11-1月 -05 03.15.01.732000000 上午
11-1月 -05 11.15.01.732000 下午
11-1月 -05 03.15.01.7320 上午 -12:00
12-1月 -05 04.20.21.522000000 上午
11-1月 -05 11.20.21.522000 下午
12-1月 -05 04.20.21.5220 上午 +13:00
11-1月 -05 02.15.16.567000000 下午
12-1月 -05 01.45.16.567000 上午
11-1月 -05 02.15.16.5670 下午 -03:30
12-1月 -05 03.16.54.992000000 上午
12-1月 -05 01.46.54.992000 上午
12-1月 -05 03.16.54.9920 上午 +09:30
已選擇7行。
SQL> SELECT DUMP(TIME1, 16), DUMP(TIME2, 16), DUMP(TIME3, 16) FROM TEST_TIMESTAMP;
DUMP(TIME1,16)
-------------------------------------------------------------
DUMP(TIME2,16)
-------------------------------------------------------------
DUMP(TIME3,16)
-------------------------------------------------------------
Typ=180 Len=11: 78,69,1,b,18,9,10,1,9b,fc,c0
Typ=231 Len=11: 78,69,1,b,18,9,10,1,9b,fc,c0
Typ=181 Len=13: 78,69,1,b,10,9,10,1,9b,fc,c0,1c,3c
Typ=180 Len=11: 78,69,1,b,10,c,2c,2c,77,e,80
Typ=231 Len=11: 78,69,1,b,18,c,2c,2c,77,e,80
Typ=181 Len=13: 78,69,1,b,10,c,2c,2c,77,e,80,14,3c
Typ=180 Len=11: 78,69,1,b,b,f,9,3a,d4,6c,c0
Typ=231 Len=11: 78,69,1,b,18,f,9,3a,d4,6c,c0
Typ=181 Len=13: 78,69,1,b,10,f,9,3a,d4,6c,c0,f,3c
Typ=180 Len=11: 78,69,1,b,4,10,2,2b,a1,6f,0
Typ=231 Len=11: 78,69,1,b,18,10,2,2b,a1,6f,0
Typ=181 Len=13: 78,69,1,b,10,10,2,2b,a1,6f,0,8,3c
Typ=180 Len=11: 78,69,1,c,5,15,16,1f,1d,16,80
Typ=231 Len=11: 78,69,1,b,18,15,16,1f,1d,16,80
Typ=181 Len=13: 78,69,1,b,10,15,16,1f,1d,16,80,21,3c
Typ=180 Len=11: 78,69,1,b,f,10,11,21,cb,bb,c0
Typ=231 Len=11: 78,69,1,c,2,2e,11,21,cb,bb,c0
Typ=181 Len=13: 78,69,1,b,12,2e,11,21,cb,bb,c0,11,1e
Typ=180 Len=11: 78,69,1,c,4,11,37,3b,20,b8,0
Typ=231 Len=11: 78,69,1,c,2,2f,37,3b,20,b8,0
Typ=181 Len=13: 78,69,1,b,12,2f,37,3b,20,b8,0,1d,5a
SQL> SELECT TO_NUMBER('1C', 'XXX'), TO_NUMBER('3C', 'XXX') FROM DUAL;
TO_NUMBER('1C','XXX') TO_NUMBER('3C','XXX')
--------------------- ---------------------
28 60
SQL> SELECT TO_NUMBER('14', 'XXX'), TO_NUMBER('3C', 'XXX'), TO_NUMBER('143C', 'XXXXXXX') FROM DUAL;
TO_NUMBER('14','XXX') TO_NUMBER('3C','XXX')
--------------------- ---------------------
20 60
SQL> SELECT TO_NUMBER('3C', 'XXX') , TO_NUMBER('1E', 'XXX'), TO_NUMBER('5A', 'XXX') FROM DUAL;
TO_NUMBER('3C','XXX') TO_NUMBER('1E','XXX') TO_NUMBER('5A','XXX')
--------------------- --------------------- -------------------
60 30 90
可以看出，修改時區會導致系統TIMESTAMP時間發生變化，但是對於TIMESTAMP WITH LOCAL TIME ZONE型別，總是將系統的時間轉化到資料庫伺服器上時區的時間進行儲存。
TIMESTAMP WITH TIME ZONE儲存的是當前時間轉化到0時區的對應的時間，並透過最後兩位來儲存時區資訊。
第一位表示時區的小時部分。0時區用0x14表示。東n區在這個基礎上加n，西n區在這個基礎上減n。我們所處的東8區表示為0x1C。西5區表示為0xF。
第二位表示時區的分鐘部分。標準是0x3C，即60分鐘。對於東時區的半區，在這個基礎上加上30分鐘，如果是西時區，則減去30分鐘。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12801008/viewspace-607647/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle基本資料型別儲存格式淺析——RAW型別Oracle資料型別
- ORACLE日期型別Oracle型別
- [20191219]oracle timestamp資料型別的儲存.txtOracle資料型別
- mysql儲存日期使用什麼型別MySql型別
- (三)Python基本資料型別Python資料型別
- 基本資料型別與字串型別資料型別字串
- js資料型別之基本資料型別和引用資料型別JS資料型別
- [20241009]oracle timestamp with time zone資料型別的儲存.txtOracle資料型別
- JavaScript中的資料型別-儲存差別JavaScript資料型別
- redis-4.資料儲存型別Redis型別
- Redis(一)：基本資料型別與底層儲存結構Redis資料型別
- 基本資料型別資料型別
- Oracle 資料型別Oracle資料型別
- Oracle資料型別對應Java型別Oracle資料型別Java
- JAVA中基本資料型別和引用資料型別Java資料型別
- Python基本資料型別：布林型別(Boolean)Python資料型別Boolean
- Java基本資料型別Java資料型別
- JavaScript基本資料型別JavaScript資料型別
- python基本資料型別Python資料型別
- 003基本資料型別資料型別
- MySQL基本資料型別MySql資料型別
- Java 基本資料型別Java資料型別
- Java中的基本資料型別與引用資料型別Java資料型別
- js基本語法之 值型別(資料型別)(變數型別)JS資料型別變數
- 淺析number型別的值型別
- 基本資料型別與API引用型別的使用資料型別API
- 【轉】ORACLE資料型別Oracle資料型別
- Oracle anydata資料型別Oracle資料型別
- ORACLE NUMBER資料型別Oracle資料型別
- Redis資料型別基本操作Redis資料型別
- Java的基本資料型別Java資料型別
- java Atomic 基本資料型別Java資料型別
- 基本資料型別，for迴圈資料型別
- Python的基本資料型別Python資料型別
- 3. 基本資料型別資料型別
- 基本資料型別之字串資料型別字串
- 基本資料型別轉化資料型別
- Python語言基礎（三）：基本資料型別Python資料型別
- Python基本資料型別之浮點型Python資料型別