How is Oracle date implemented? Is it stored as milliseconds or something like that?
+6
A:
An Oracle DATE stores the date and time to the second. An Oracle TIMESTAMP stores the date and time to up to 9 digits of subsecond precision, depending on the available hardware.
Both are implemented by storing the various components of the date and the time in a packed binary format. From the Oracle Concepts Guide section on dates
Oracle uses its own internal format to store dates. Date data is stored in fixed-length fields of seven bytes each, corresponding to century, year, month, day, hour, minute, and second.
You can use the DUMP() function to see the internal representation of any particular date (or any other value for that matter), but that's probably more than you need (or want) to know.
Justin Cave
2008-10-09 14:23:25
Thanks, especially for a dump() function :)
Igor Drincic
2008-10-09 14:36:44
A:
No. DATE is a timestamp value with seconds precision. You need TIMESTAMP(3) to store milliseconds.
Chris Noe
2008-10-09 14:24:24
To make this clear: Oracle timestamp doesn't store milliseconds. It stores a fraction of a second with configurable accuracy - up to 9 decimal places. If you use timestamp(3) then you have milliseconds. If you use timestamp(6) you have microseconds. timestamp(9)? nanoseconds.
JeeBee
2008-10-09 14:31:24
Thank, see edit. (I think that on most systems you exceed the available clock resolution when you go beyond milliseconds.)
Chris Noe
2008-10-09 16:31:59
+1
A:
Apparently, not in form of millisecs.
Which actually makes sense, since they do not have any running operations on current date/time:
http://www.ixora.com.au/notes/date_representation.htm
http://infolab.stanford.edu/~ullman/fcdb/oracle/or-time.html
Mostlyharmless
2008-10-09 14:25:37