oracle11g - Meaning of Oracle's dump(systimestamp) bytes

Question

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.

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

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.

Any help is appreciated.

Answer 1

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                                 

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

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

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?

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