没有时间存储日期会使用较少的字节吗? [英] Does storing date without time use less bytes?

问题描述

我看到了这个链接， 此:

如果您指定不带时间成分的日期值，则默认 时间是午夜.如果您指定不带日期的日期值，则 默认日期是当月的第一天.

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列始终包含日期和时间字段.如果 您的查询使用没有时间部分的日期格式，那么您必须 确保将DATE列中的时间字段设置为午夜.

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.

解决方案是在插入或更新表中的行时对日期数据类型的列施加约束，并创建触发器(使用TRUNC()).

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.

如果使用此解决方案，我是否可以保证Oracle在没有时间的情况下确实存储较少的字节?

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

Oracle使用此标准日期时间类型创建歧义.创建日期类型(仅包含日期)是如此困难吗?这是我的看法(我来自MSSQL).

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).

推荐答案

不，您没有任何担保...无论发生什么情况，Oracle都将存储午夜的事实.您不能没有时间存储日期.

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.

如果您创建下表:

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

，然后运行以下查询:

select dt, dump(dt) from a

SQL提琴

返回的值是:

+-----------------------------+------------------------------------+
|             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() 返回数据类型，长度以字节为单位以及数据的内部表示形式.

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

换句话说，带时间的日期和已被截断的日期都具有7个字节.它们的长度相同.

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.

这篇关于没有时间存储日期会使用较少的字节吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！