Some stuff about dates and timestamps in Oracle

set null ~ pagesize 40 linesize 132;
column id format 90;
column datetrunc format a30;
column dateuntrunc format a30;
column timetrunc format a30;
column timeuntrunc format a30;
create table time_size2
id number,
datetrunc date,
dateuntrunc date,
timetrunc timestamp,
timeuntrunc timestamp
insert into time_size2
select rownum, trunc(sysdate), sysdate,trunc(sysdate),sysdate
from dual;
insert into time_size2 values (2,null,null,null,null);
select * from time_size2;
to_char(datetrunc, 'YYYY MM DD HH24:MI:SS') "datetrunc",
to_char(dateuntrunc,'YYYY MM DD HH24:MI:SS') "dateuntrunc",
to_char(timetrunc, 'YYYY MM DD HH24:MI:SS.FF9') "timetrunc",
to_char(timeuntrunc,'YYYY MM DD HH24:MI:SS.FF9') "timeuntrunc"
select vsize(datetrunc),vsize(dateuntrunc),vsize(timetrunc),vsize(timeuntrunc) from time_size2;
-- Notice that vsize reports NULL as the physical length for NULLs in accordance with the standard
-- (so don't blame Oracle for the stupidity that the physical length of the NULL is known but not accurately reported
-- to date Oracle has ignored enhancement requests for an rsize function that reports the real size in all cases
select dump(datetrunc),dump(dateuntrunc),dump(timetrunc),dump(timeuntrunc) from time_size2;
-- dump on the other hand could be more informative if Oracle wanted, since that is not part of the standard.
-- and the .FF9 format just doesn't seem to work for dates as a convenience that would show zeroes anyway
-- so remember you can't just use a standard fractional second format model for dates and timestamps
-- if you want to simulate a matching format you can tack a decimal point and literal zeroes onto a date
select to_char(dateuntrunc,'YYYY MM DD HH24:MI:SS.FF9') "dateuntrunc" from time_size2;
select to_char(dateuntrunc,'YYYY MM DD HH24:MI:SS')||'.00000000' "dateuntrunc" from time_size2;
-- okay, you need a little fix-up to make that an accurate simulation for NULLs
select to_char(dateuntrunc,'YYYY MM DD HH24:MI:SS')||decode(dateuntrunc,null,'','.00000000') "dateuntrunc" from time_size2;

Will show you some interesting details about dates and timestamps in Oracle. A sample run on r11 is here:

Of course if you run on a release prior to timestamps, this will fail horribly. If Oracle ever listens to my enhancement requests for the rsize function and adding .FF [1..9]
as a legal format, the bit about that work-around for a constant date and timestamp output format will be obsolete.



About rsiz

Father and Husband, Oracle Technology Scientist and Consultant, planning to end poverty for citizens and legal US residents Lebanon, NH · See my wife's puzzles at
This entry was posted in Oracle and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s