Menu
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)
317
For each DATE value, Oracle Database stores the following information: century, year, month, date, hour, minute, and second. You can specify a date value by: Specifying the date value as a literal. Converting a character or numeric value to a date value with the TO_DATE function.
MySQL comes with the following data types for storing a date or a date/time value in the database: DATE - format YYYY-MM-DD. DATETIME - format: YYYY-MM-DD HH:MI:SS. TIMESTAMP - format: YYYY-MM-DD HH:MI:SS.
The DATE datatype stores date and time information. Although date and time information can be represented in both character and number datatypes, the DATE datatype has special associated properties. For each DATE value, Oracle stores the following information: century, year, month, date, hour, minute, and second.
TO_DATE converts char of CHAR , VARCHAR2 , NCHAR , or NVARCHAR2 datatype to a value of DATE datatype. The fmt is a datetime model format specifying the format of char . If you omit fmt , then char must be in the default date format. If fmt is J , for Julian, then char must be an integer.
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
79
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
