Does storing date without time use less bytes?

Question

I saw this link and this:

If you specify a date value without a time component, then the default time is midnight. If you specify a date value without a date, then the default date is the first day of the current month.

Oracle DATE columns always contain fields for both date and time. If your queries use a date format without a time portion, then you must ensure that the time fields in the DATE column are set to midnight.

The solution is to put a constraint on column with the date data-type and create a trigger (with TRUNC()) when inserting or updating a row in table.

If I use this solution do I have the warranty that Oracle does store less bytes for a date without the time?

With this standard datetime type Oracle create ambiguity. It is so hard to create a date type (only containing a date)? This is my opinion (I came from MSSQL).

Answer 1

No, you do not have any warranty whatsoever... whatever happens Oracle is going to store the fact that it's midnight. You cannot store a date without a time.

If you create the following table:

create table a ( dt date);
insert into a values(sysdate);
insert into a values(trunc(sysdate));

and then run this query:

select dt, dump(dt) from a

SQL Fiddle

The values returned are:

+-----------------------------+------------------------------------+
|             DT              |              DUMP(DT)              |
+-----------------------------+------------------------------------+
| June, 12 2013 18:03:15+0000 | Typ=12 Len=7: 120,113,6,12,19,4,16 |
| June, 12 2013 00:00:00+0000 | Typ=12 Len=7: 120,113,6,12,1,1,1   |
+-----------------------------+------------------------------------+

DUMP() returns the datatype, the length in bytes and the internal representation of the data.

In other words, a date with a time, and a date that have been truncated, both have 7 bytes. They're the same length.

---

As a little aside I would recommend against destroying potentially useful data because you're worried about space.

Answer 2

Storing only dates may save space if you use table compression.

Here's an example showing that storing only dates can reduce the segment size:

create table a (dt date) compress;
create table b (dt date) compress;

--Insert 20 million rows, with time
begin
    for i in 1 .. 20 loop
        insert /*+ append */ into a
        select sysdate + numToDSInterval(level, 'second')
        from dual connect by level <= 1000000;
        commit;
    end loop;
end;
/

--Insert 20 million rows, date only
begin
    for i in 1 .. 20 loop
        insert /*+ append */ into b
        select trunc(sysdate + numToDSInterval(level, 'second'))
        from dual connect by level <= 1000000;
        commit;
    end loop;
end;
/

select segment_name, bytes/1024/1024 MB
from dba_segments
where segment_name in ('A', 'B')
order by segment_name;

SEGMENT_NAME   MB
------------   --
A              256
B              224

Oracle basic table compression only compresses entire values, and if there are fewer distinct values then compression can work better. But never fully believe any compression demo - you need to try it on your own data to be sure. This may be a best-case scenario, it is possible that compression will not help your data at all.

Table compression has many downsides - it requires enterprise edition, DML is slower, you cannot add a column to the table, etc.

Also, as Ben suggested, you should enforce the date-only rule with a check constraint instead of a trigger. It will be simpler, faster, and will not prevent direct-path writes, which are necessary to use basic table compression.