How are dates stored in Oracle?
date, oracle
Solution
There are two types 12 and 13
http://oraclesniplets.tumblr.com/post/1179958393/my-oracle-support-oracle-database-69028-1
Type 13
select dump(sysdate) from dual;
Typ=13 Len=8: 220,7,11,26,16,41,9,0
The format of the date datatype is
Byte 1 - Base 256 year modifier : 220
2 - Base 256 year : 256 * 7 = 1792 + 220 = 2012
3 - Month : 11
4 - Day : 26
5 - Hours : 16
6 - Minutes : 41
7 - Seconds : 09
8 - Unused
2012-11-26 16:41:09
Type 12
select dump(begindate) from tab;
Typ=12 Len=7: 100,112,2,7,1,1,1
The format of the date datatype is
byte 1 - century (excess 100) 100 - 100 = 00
byte 2 - year (excess 100) 112 - 100 = 12
byte 3 - month = 2
byte 4 - day = 7
byte 5 - hour (excess 1) 1 - 1 = 0
byte 6 - minute (excess 1) 1 - 1 = 0
byte 7 - seconds (excess 1) 1 - 1 = 0
0012-02-07 00:00:00
Problem
How are dates stored in Oracle? For example I know most systems use Epoch time to determine what time it is. By calculating how many seconds away from January 1st 1970. Does Oracle do this as well? The reason I am asking this is I noticed if you take two dates in Oracle and subtract them you get a floating point of how many days are between. Example ``` (Sysdate - dateColumn) ``` would return something like this (depending on the time) ``` 3.32453703703703703703703703703703703704 ``` Now is Oracle doing the conversion and spitting that format out, or does Oracle store dates with how many days it is away from a certain time frame? (Like Epoch time)