5

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.

Community
  • 1
  • 1
Radu Gheorghiu
  • 20,049
  • 16
  • 72
  • 107
  • 1
    Please, check this answer: http://stackoverflow.com/a/13568348/1803777 – Ulugbek Umirov Jul 31 '15 at 09:51
  • For your underlying issue, have a look at your execution plan and see if it's doing `sys_extract_utc` on your base column; and try running that directly on the column to see if you get the same error. – Alex Poole Jul 31 '15 at 10:04
  • @UlugbekUmirov I have look at the answer, but it has information for only two types of timestamp (12 and 13), while I have a different (188) timestamp type. Also, looked at other details from their answers and couldn't find details regarding my type. – Radu Gheorghiu Jul 31 '15 at 10:38
  • 1
    @RaduGheorghiu that answer is about dates rather than timestamps; but the date portion of timestamps (and the time, down to second precision) is interpreted the same way. So what it says about type 13 applies to types 187 and 188 too (and others). – Alex Poole Jul 31 '15 at 10:41

1 Answers1

5

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.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318