Oracle 中的日期是如何存储的？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13568193/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How are dates stored in Oracle?
提问by Halfwarr
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?
Oracle 中的日期是如何存储的？例如，我知道大多数系统使用 Epoch 时间来确定现在是什么时间。通过计算距 1970 年 1 月 1 日有多少秒。Oracle 是否也这样做？
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.
我问这个的原因是我注意到如果你在 Oracle 中取两个日期并将它们相减，你会得到一个浮点数，即之间有多少天。
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)
现在是 Oracle 进行转换并输出该格式，还是 Oracle 存储与某个时间范围相距多少天的日期？（像大纪元时间）
回答by Rob van Laarhoven
There are two types 12 and 13
有12和13两种
http://oraclesniplets.tumblr.com/post/1179958393/my-oracle-support-oracle-database-69028-1
http://oraclesniplets.tumblr.com/post/1179958393/my-oracle-support-oracle-database-69028-1
Type 13
类型 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
2012-11-26 16:41:09
Type 12
类型 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
0012-02-07 00:00:00
回答by a_horse_with_no_name
From the manual at http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements001.htm#sthref151
来自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
对于每个 DATE 值，Oracle 存储以下信息：年、月、日、小时、分钟和秒
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
数据库在内部将日期存储为数字。日期存储在每个7字节的定长字段中，分别对应世纪、年、月、日、时、分、秒
回答by Lalit Kumar B
How are dates stored in Oracle?
Oracle 中的日期是如何存储的？
The two data types12and 13are for two different purposes.
该两个数据类型12，并13有两种不同的目的。
- 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 usingTO_DATEor ANSI Date literalDATE 'YYYY-MM-DD'.
- 类型 12- 存储在表中的日期
- 键入13-返回的由内部日期函数日期像
SYSDATE/CURRENT_DATE，转换字符串文字为日期使用时也TO_DATE或ANSI日期文字DATE 'YYYY-MM-DD'。
Test cases:
测试用例：
Basic table setup for type 12:
类型 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:
使用ANSI 日期文字，就像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 13is used when converting a string literal into date using date functions or when date returned by internal date functions like SYSDATE/CURRENT_DATE.
如您所见，在表中存储日期时，它使用类型 12。第二种类型 13用于使用日期函数将字符串文字转换为日期或由内部日期函数（如SYSDATE/ ）返回的日期CURRENT_DATE。
