oracle 的timestamp类型

TIMESTAMP Data Type

The TIMESTAMP data type is an extension of the DATE data type. It stores the year, month, and day of the DATE data type, plus hour, minute, and second values. This data type is useful for storing precise time values and for collecting and evaluating date information across geographic regions. Specify the TIMESTAMP data type as follows:

TIMESTAMP [(fractional_seconds_precision)] 

timestamp类型是date类型的一个扩展，date类型会存储年月日时分秒信息，timestamp类型精度更高，会存储到微秒、纳秒。

SQL> create table t (id number,col1 date,col2 timestamp,col3 timestamp(9));

Table created.

SQL> set long 10000
SQL> select dbms_metadata.get_ddl('TABLE','T','SCOTT') from dual;

DBMS_METADATA.GET_DDL('TABLE','T','SCOTT')
--------------------------------------------------------------------------------

CREATE TABLE "SCOTT"."T"
( "ID" NUMBER,
"COL1" DATE,
"COL2" TIMESTAMP (6),
"COL3" TIMESTAMP (9)
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS"

SQL> insert into t values(1,sysdate,sysdate,sysdate);

1 row created.

SQL> select * from t;

ID COL1 COL2 COL3
---------- -------------------- ------------------------------ --------------------------------
1 2022-03-29 18:40:24 29-MAR-22 06.40.24.000000 PM 29-MAR-22 06.40.24.000000000 PM

可以看出，timestamp类型默认存储到微秒，指定精度timestamp(9) 时可以存储到纳秒。

SQL> select column_name,data_type,data_length,data_precision,data_scale from user_tab_cols where table_name='T';

 

 

timestamp(6), timestamp(9) 都会占用11个bytes，date占7个bytes,所以精度只要求到秒的，date类型就可以，比timestamp类型节省空间。

 

另外oracle可以为date类型或timestamp类型指定默认值 sysdate

alter table t modify col1 default sysdate; #修改某date列的默认值为sysdate。 修改以前的历史数据不会改变。

或者

alter table t add col4 date default sysdate; #增加一列，并指定默认值sysdate。