Oracle dump(systimestamp) 字节的含义

2021-12-30 00:00:00 oracle11g oracle time-format systimestamp

我试图了解我的数据库上设置的时间戳中的字节是什么意思.如何计算它们以生成更具可读性的日期?

I'm trying to understand what the bytes from the timestamp set on my DB mean. How do they get computed to generate the more readable date?

我正在使用以下查询来获取我需要的数据:

I'm using the below query to get the data that I need:

SELECT systimestamp
    ,DUMP (systimestamp)
    ,sessiontimezone
FROM dual;

我上面查询的输出是:

+-------------------------------------+-----------------------------------------------------------------+------------------+
|            systimestamp             |                       dump(systimestamp)                        | sessiontimezone  |
+-------------------------------------+-----------------------------------------------------------------+------------------+
| 31-JUL-15 08.55.06.157047000 +00:00 | Typ=188 Len=20: 223,7,7,31,8,55,6,0,216,88,92,9,0,0,5,0,0,0,0,0 | Europe/Bucharest |
+-------------------------------------+-----------------------------------------------------------------+------------------+

我在网上找到了一些资源来解释字节的含义(此处) 但规则在我的场景中不匹配.

I have found a few resources online explaining what the bytes mean (here) but the rules don't match in my scenario.

例如:223 不是世纪 + 100 等

For example: 223 is not the century + 100 etc.

我尝试这样做的原因是因为我在将 timestamp(3) 列中的值与 systimestamp 和我进行比较时遇到的问题我正在尝试编写一个脚本来验证我的问题/解决方案是否相同 如此处所述.

The reason I'm trying to do this is because of a problem I'm facing when comparing the values in a timestamp(3) column with systimestamp and I'm trying to write a script to verify if my issue/solution is the same as explained here.

感谢任何帮助.

推荐答案

有多种表面相似但内部不同的日期时间数据类型.systimestamp 是类型 188(并且有时区信息)；时间戳文字是类型 187 没有时区信息和 188 与它；一个普通的时间戳列是 180 类型:

There a various superficially similar but internally different datetime datatypes. systimestamp is type 188 (and has timezone information); a timestamp literal is type 187 without time zone info and 188 with it; and a plain timestamp column is type 180:

select dump(systimestamp) from dual;

DUMP(SYSTIMESTAMP)                                                             
--------------------------------------------------------------------------------
Typ=188 Len=20: 223,7,7,31,9,50,28,11,128,203,79,35,1,0,5,0,0,0,0,0             

select dump(timestamp '2015-07-31 08:55:06.157047 +00:00') from dual;

DUMP(TIMESTAMP'2015-07-3108:55:06.157047+00:00')              
---------------------------------------------------------------
Typ=188 Len=20: 223,7,7,31,8,55,6,0,216,88,92,9,0,0,5,0,0,0,0,0

select dump(timestamp '2015-07-31 08:55:06.157047') from dual;

DUMP(TIMESTAMP'2015-07-3108:55:06.157047')                    
---------------------------------------------------------------
Typ=187 Len=20: 223,7,7,31,8,55,6,0,216,88,92,9,0,0,3,0,0,0,0,0

create table t (ts timestamp);
insert into t (ts) values (timestamp '2015-07-31 08:55:06.157047');
select dump(ts) from t;

DUMP(TS)                                                                       
--------------------------------------------------------------------------------
Typ=180 Len=11: 120,115,7,31,9,56,7,9,92,88,216

其中，只有时间戳列使用您链接到的文章中的内部格式，使用超过 100 的年份表示法.

Of those, only a timestamp column uses the internal format in the article you linked to, using excess-100 notation for the year.

其他的，第一个字节是base-256修饰符，第二个字节是base-256年；所以你会把它解释为

For the others, the first byte is a base-256 modifier, and the second byte is the base 256 year; so you would interpret it as

223 + (7 * 256) = 2015

您可以在 My Oracle Support 文档 69028.1 中阅读有关内部存储的更多信息.那，以及链接到评论中的较早答案，是指两种日期类型，但时间戳被视为相同的秒，其余的一些可以推断为 187/188 类型 - 小数秒部分无论如何:

You can read more about the internal storage in My Oracle Support document 69028.1. That, and the earlier answer linked to in comments, refer to the two date types, but timestamps are treated the same down to the seconds, and some of the rest can be inferred for type 187/188 - the fractional-seconds part anyway:

Byte 1 - Base 256 year modifier: 223
2      - Base 256 year: 7 (256 * 7 = 1792 + 223 = 2015)
3      - Month: 7
4      - Day: 31
5      - Hours: 8
6      - Minutes: 55
7      - Seconds: 6
8      - Unused?
9      - Base 256 nanoseconds: 216
10     - Base 256 ns modifier 1: 256 * 88 = 22528
11     - Base 256 ns modifier 2: 256 * 256 * 92 = 6029312
12     - Base 256 ns modifier 3: 256 * 256 * 256 * 9 = 150994944
           => actual nanoseconds = 216 + 22528 + 6029312 + 150994944 
           => 157047000
13-20  - Time zone data?

对于类型 120，秒的小数部分相同，但字节颠倒了.

For type 120 the fractional seconds are the same but with the bytes reversed.

相关文章