Oracle转储(systimestamp)字节的含义 [英] Meaning of Oracle's dump(systimestamp) bytes

问题描述

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

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;

我上面的查询的输出是:

And the output of my above query is:

+-------------------------------------+-----------------------------------------------------------------+------------------+
|            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                                 

其中，只有一个timestamp列使用链接到的文章中的内部格式，并使用当年的多余100表示​​法.

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

对于其他字节，第一个字节是基数256的修饰符，第二个字节是基数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.

这篇关于Oracle转储(systimestamp)字节的含义的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！