Search

OakieTags

Who's online

There are currently 0 users and 28 guests online.

Recent comments

Affiliations

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);
commit;
select * from time_size2;
select
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"
from
time_size2;
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:
c_time_size2

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.

mwf