By Halfwarr


2012-11-26 15:43:05 8 Comments

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)

3 comments

@Lalit Kumar B 2016-03-17 11:46:08

How are dates stored in Oracle?

The two data types 12 and 13 are for two different purposes.

  • Type 12 - Dates stored in table
  • Type 13 - Date returned by internal date functions like SYSDATE/CURRENT_DATE, also when converting a string literal into date using TO_DATE or ANSI Date literal DATE 'YYYY-MM-DD'.

Test cases:

Basic table setup for type 12:

SQL> CREATE TABLE t(col DATE);

Table created.

SQL> INSERT INTO t SELECT SYSDATE FROM dual;

1 row created.

SQL> COMMIT;

Commit complete.

Check the different cases:

SQL> SELECT DUMP(col) FROM t;

DUMP(COL)
--------------------------------------------------------------------------------
Typ=12 Len=7: 120,116,3,17,18,6,55

SQL> SELECT DUMP(SYSDATE) FROM dual;

DUMP(SYSDATE)
--------------------------------------------------------------------------------
Typ=13 Len=8: 224,7,3,17,17,5,54,0

SQL> SELECT DUMP(CURRENT_DATE) FROM dual;

DUMP(CURRENT_DATE)
--------------------------------------------------------------------------------
Typ=13 Len=8: 224,7,3,17,17,14,20,0

SQL> SELECT DUMP(TO_DATE('17-DEC-1980 12:12:12','DD-MON-YYYY HH24:MI:SS'))  FROM dual;

DUMP(TO_DATE('17-DEC-198012:12:12','
------------------------------------
Typ=13 Len=8: 188,7,12,17,12,12,12,0

Using ANSI Date literal, just like TO_DATE:

SQL> SELECT DUMP(DATE '2016-03-17') FROM dual;

DUMP(DATE'2016-03-17')
--------------------------------
Typ=13 Len=8: 224,7,3,17,0,0,0,0

SQL> INSERT INTO t SELECT to_date('17-DEC-1980 12:13:14','DD-MON-YYYY HH24:MI:SS') FROM dual;

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT DUMP(col) FROM t;

DUMP(COL)
--------------------------------------------------------------------------------
Typ=12 Len=7: 120,116,3,17,18,6,55
Typ=12 Len=7: 119,180,12,17,13,14,15

SQL>

As you can see, while storing a date in the table, it uses type 12. The second type 13 is used when converting a string literal into date using date functions or when date returned by internal date functions like SYSDATE/CURRENT_DATE.

@Robert Merkwürdigeliebe 2012-11-26 15:52:28

The 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

@a_horse_with_no_name 2012-11-26 15:46:22

From the manual at http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements001.htm#sthref151

For each DATE value, Oracle stores the following information: year, month, day, hour, minute, and second

So apparently it's not storing an epoch value which is also confirmed by this chapter of the manual:

The database stores dates internally as numbers. Dates are stored in fixed-length fields of 7 bytes each, corresponding to century, year, month, day, hour, minute, and second

@a_horse_with_no_name 2012-11-26 16:01:28

@Halfwarr: see my edit (and the second link to the manual)

@Halfwarr 2012-11-26 16:04:39

Very interesting, Thank You! I was sorta hoping it used Julian date for storing the date. This method probably makes more sense anyway!

@a_horse_with_no_name 2012-11-26 17:06:30

@Halfwarr: why do you care how it's stored? That does not matter at all.

@Halfwarr 2012-11-26 18:29:41

Just wanted to know, no real reason. I was just curious how they are stored because I was working with them. You can never have enough random knowledge ;)

Related Questions

Sponsored Content

42 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

49 Answered Questions

[SOLVED] How to format a JavaScript date

36 Answered Questions

[SOLVED] Get current time and date on Android

  • 2011-03-20 16:12:25
  • M7M
  • 1236385 View
  • 981 Score
  • 36 Answer
  • Tags:   android date time

20 Answered Questions

[SOLVED] Get list of all tables in Oracle?

  • 2008-10-15 17:54:49
  • vitule
  • 2036207 View
  • 1021 Score
  • 20 Answer
  • Tags:   sql oracle

43 Answered Questions

[SOLVED] How do I get the current date in JavaScript?

  • 2009-10-07 11:39:02
  • Suresh
  • 2226995 View
  • 1941 Score
  • 43 Answer
  • Tags:   javascript date

35 Answered Questions

[SOLVED] Compare two dates with JavaScript

37 Answered Questions

[SOLVED] How do you get a timestamp in JavaScript?

39 Answered Questions

[SOLVED] Detecting an "invalid date" Date instance in JavaScript

  • 2009-08-30 11:34:40
  • orip
  • 657261 View
  • 1239 Score
  • 39 Answer
  • Tags:   javascript date

35 Answered Questions

[SOLVED] Where can I find documentation on formatting a date in JavaScript?

15 Answered Questions

[SOLVED] Calculate difference between two dates (number of days)?

  • 2009-10-22 13:47:15
  • leora
  • 991369 View
  • 940 Score
  • 15 Answer
  • Tags:   c# date

Sponsored Content