Question

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)

Answer 1

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