Oracle Date与Timestamp类型的使用与区别

1.DATE数据类型
DATE数据类型的问题在于它表示两个事件发生时间间隔的度量粒度是秒。可以使用TO_CHAR函数把DATE类型数据进行格式化输出。

SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "Date" from dual;
Date
-------------------
2020-10-30 17:24:08

2.TIMESTAMP 数据类型

DATE数据类型的主要问题是它粒度不能足够区别出两个事件哪个先发生。ORACLE已经在DATE数据类型上扩展出来了TIMESTAMP数据类型，包括了小数秒的信息，变得更加细粒度。如果你想把 DATE类型转换成TIMESTAMP类型，就使用CAST函数。

SQL> select cast(sysdate as timestamp) "Date" from dual;

Date
---------------------------------------------------------------------------
30-OCT-20 05.25.45.000000 PM

正如你看到的，在转换后的时间段尾部有了一段“.000000”。这是因为从date转换过来的时候，没有小数秒的信息，缺省为0。而且显示格式是按照参数NLS_TIMESTAMP_FORMAT定的缺省格式显示。当你把一个表中date类型字段的数据移到另一个表的timestamp类型字段中去的时候，oracle会自动为你做转换。

 SELECT TO_CHAR(time1,'YYYY-MM-DD HH24:MI:SS') "Date" FROM date_table 
 Date 
 ------------------- 
 2020-10-30 17:25:08 

TIMESTAMP数据的格式化显示和DATE 数据一样。注意，to_char函数支持date和timestamp，trunc不支持TIMESTAMP数据类型。

 SELECT TO_CHAR(time1,'YYYY-MM-DD HH24:MI:SS:FF3') "Date" FROM date_table
 Date 
 ----------------------- 
 2020-10-30 17:35:11:000 

3.系统日期和时间

为了得到系统时间，返回成date数据类型。你可以使用sysdate函数。

1 SQL> SELECT SYSDATE FROM DUAL;

为了得到系统时间，返回成timestamp数据类型。你可以使用systimpstamp函数。

1 SQL> SELECT SYSTIMESTAMP FROM DUAL;

4.date和timestamp 的区别

date类型是Oracle常用的日期型变量，他的时间间隔是秒。两个日期型相减得到是两个时间的间隔，注意单位是“天”。

select to_date('2022-10-30 03:12:00','yyyy-mm-dd hh24:mi:ss')-sysdate from dual
TO_DATE('2022-10-3003:12:00','YYYY-MM-DDHH24:MI:SS')-SYSDATE
------------------------------------------------------------
                                                  729.402535

timestamp是DATE类型的扩展，可以精确到小数秒。两个timestamp相减的话，不能直接的得到天数，而是得到，多少天，多少小时，多少秒等.

select to_timestamp('2022-10-30 03:12:00','yyyy-mm-dd hh24:mi:ss')-systimestamp from dual
TO_TIMESTAMP('2022-10-3003:12:00','YYYY-MM-DDHH24:MI:SS')-SYSTIMESTAMP
---------------------------------------------------------------------------
+000000729 09:39:08.524019000

5.date和timestamp之间的相互转换

timestamp->date：

 select to_date(to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') from dual

date ->timestamp：

 select to_timestamp(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') from dual