Shulou(Shulou.com)06/01 Report--

Original link: https://www.modb.pro/db/22143?cyn

Summary: describe the storage structure of TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE types

This article gives a brief description of the storage formats for TIMESTAMP WITH LOCAL TIME ZONE and TIMESTAMP WITH TIME ZONE types.

SQL > CREATE TABLE TEST_TIMESTAMP (TIME1 TIMESTAMP (9), TIME2 TIMESTAMP (6) WITH LOCAL TIME ZONE, 2 TIME3 TIMESTAMP (4) WITH TIME ZONE); table created. SQL > INSERT INTO TEST_TIMESTAMP VALUES (SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP); 1 line has been created. SQL > SELECT * FROM TEST_TIMESTAMP TIME1---TIME2---TIME3- -- November-January-05 11.08.15.027000pm 11-January-05 11.08.15.027000 PM 11-January-05 11.08.15.0270 PM + 08:00SQL > 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 and 69. Brecronomy 18pr 9pje 10pr 1dje Len=11: 78pr 69pr 1pr brech 18pr 9pr 10pr 1pr 9pr c0TypEng 181 Len=13: 78pr 69pr 1pr 10LI 9LI 10LY 1BJE 9b recorder 9b recorder c0Typpen 1c

As you can see, if the client and database have the same time zone, then TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE store exactly the same data.

TIMESTAMP WITH TIME ZONE, on the other hand, is slightly different. It stores the time of the 0 time zone and the time zone information.

Modify the time zone of the client host from East 8 (+ 8) to 0.

SQL > INSERT INTO TEST_TIMESTAMP VALUES (SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP); 1 line has been created.

Modify the time zone of the client host to West Zone 5 (- 5 time zone).

SQL > INSERT INTO TEST_TIMESTAMP VALUES (SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP); 1 line has been created.

Modify the time zone of the client host to West Zone 12 (- 12 time zone).

SQL > INSERT INTO TEST_TIMESTAMP VALUES (SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP); 1 line has been created.

Modify the time zone of the client host to East 13 (+ 13:00).

SQL > INSERT INTO TEST_TIMESTAMP VALUES (SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP); 1 line has been created.

Modify the time zone of the client host to West 3.5 (- 3.5 time zone).

SQL > INSERT INTO TEST_TIMESTAMP VALUES (SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP); 1 line has been created.

Modify the time zone of the client host to East 9.5 (+ 9.5 time zone).

SQL > INSERT INTO TEST_TIMESTAMP VALUES (SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP); 1 line has been created. SQL > COMMIT; submission completed.

Modify the time zone of the client host and change it back to East 8 (+ 8 time zone).

SQL > SELECT * FROM TEST_TIMESTAMP TIME1---TIME2---TIME3 Afternoon + 0015 0011-January-05 10.14.08.98700000 AM 11-January-05 11.14.08.987000 PM 11-January-05 10.14.08.9870 AM-0515. 08.9870 AM 11-January-05 03.15.01.732000AM 11-January-05 11.15.01.732000 PM 11-January-05 03.15.01.7320 AM-12 Jan 0012-January-05 04.20.21 .522000000 11-January-05 11.20.21.522000 p.m.-January-05 04.20.21.5220 AM + 13Ru 0011-January-05 02.16.567000pm 12-January-05 01.45.16.567000 AM 11-January-05 02.15.16.5670 PM-03Jan 3012-January-05 03.16.54.992000000 AM-January-05 01.46.54.992000 AM 12-January-05 03.16.54.9920 am + 09:30 7 rows have been selected. 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 and 69. Len=11: 78pjr. C0Typhammer 231 Len=13: 78pjr. 9pr. 1cTyphammer 231 Len=11: 78pjr. 6cjr. 231 Len=11: 78pr. 61pr., 2cje, 8crec, 2crec, 2c, 2c,., and, C0Typ=181 Len=13: 78,69,1,b,10,f,9,3a,d4,6c,c0,f,3cTyp=180 Len=11: 78,69,1,b,4,10,2,2b,a1,6f,0Typ=231 Len=11: 78,69,1,b,18,10,2,2b,a1,6f,0Typ=181 Len=13: 78,69,1,b,10,10,2,2b,a1,6f,0,8,3cTyp=180 Len=11: 78,69,1,c,5,15,16,1f,1d,16,80Typ=231 Len=11: 78,69,1,b,18,15,16,1f,1d,16,80Typ=181 Len=13: 78,69,1,b,10,15,16,1f,1d,16,80,21,3cTyp=180 Len=11: 78,69,1,b,f,10,11,21,cb,bb,c0Typ=231 Len=11: 78,69,1,c,2,2e,11,21,cb,bb,c0Typ=181 Len=13: 78,69,1,b,12,2e, 11meme 21 Len=11 b0pje 11pje Typpole 180 Len=11: 78pje 69pje 4pje 37pje 231 Len=11: 78pr 6pr 0Typpole 231 pr: 78pr 2fp37pl 3b 20pr 0Typhammer 181 Len=13: 78pr 69pr 0Typpen 181 Len=13: 78pr 69pr 1pr 12pr 37pr 37pr 20bpl 1dJ 5a SQL > SELECT TO_NUMBER ('1Cmate,' XXX'), TO_NUMBER ('3Craft,' XXX') TO_NUMBER ('1C') TO_NUMBER ('3C')-- 28 60SQL > SELECT TO_NUMBER ('14, 'XXX'), TO_NUMBER (' 3C'), TO_NUMBER ('143C,' XXXXXXX') FROM DUAL TO_NUMBER ('14C') TO_NUMBER ('3C')-- 20 60SQL > SELECT TO_NUMBER ('3C'), TO_NUMBER ('1E', 'XXX'), TO_NUMBER (' 5A') 'XXX') FROM DUAL TO_NUMBER ('3C) TO_NUMBER ('1E)) TO_NUMBER ('5A)-60 30 90

As you can see, changing the time zone results in a change in the system TIMESTAMP time, but for the TIMESTAMP WITH LOCAL TIME ZONE type, the system time is always converted to the time zone on the database server for storage.

TIMESTAMP WITH TIME ZONE saves the corresponding time when the current time is converted to the 0 time zone, and saves the time zone information through the last two digits.

The first bit represents the hour portion of the time zone. The 0 time zone is represented by 0x14. The east n area adds n on this basis, and the west n area subtracts n on this basis. The East 8 area where we are located is denoted as 0x1C. West Zone 5 is represented as 0xF.

The second bit represents the minute portion of the time zone. The standard is 0x3C, or 60 minutes. For the half of the eastern time zone, add 30 minutes to this basis, and if it is the western time zone, subtract 30 minutes.

More classic case analysis of Oracle: https://www.modb.pro/u/379?cyn

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.