Oracle日期对应java,Oracle和Java中日期的日期差异

这里的问题是你认为两列都是DATE列，其中至少有两列是TIMESTAMP列。当您从另一个日期中提取一个日期时，您会得到一个数字。但是当你从时间戳中提取日期时，反之亦然，你得到一个INTERVAL。

一个例子

包含DATE和TIMESTAMP的表：

SQL> create table mytable (close_date,create_date)

2 as

3 select date '2010-11-01', systimestamp from dual union all

4 select date '2010-11-11', systimestamp from dual union all

5 select date '2010-12-01', systimestamp from dual

6 /

Table created.

SQL> desc mytable

Name Null? Type

----------------------------------------- -------- ----------------------------

CLOSE_DATE DATE

CREATE_DATE TIMESTAMP(6) WITH TIME ZONE

从DATE列中提取TIMESTAMP，导致INTERVAL：

SQL> select close_date - create_date

2 from mytable

3 /

CLOSE_DATE-CREATE_DATE

---------------------------------------------------------------------------

+000000130 11:20:11.672623

+000000140 11:20:11.672623

+000000160 11:20:11.672623

3 rows selected.

而且没有必要摆弄TO_NUMBER和SUBSTR。只需使用EXTRACT函数从间隔中获取所需的组件：

SQL> select extract(day from (close_date - create_date))

2 from mytable

3 /

EXTRACT(DAYFROM(CLOSE_DATE-CREATE_DATE))

----------------------------------------

130

140

160

3 rows selected.

问候，

抢。

这是一个带有两个TIMESTAMPS的示例，它显示INTERVAL是截断的，而不是舍入的：

SQL> create table mytable (close_date,create_date)

2 as

3 select to_timestamp('2010-11-01','yyyy-mm-dd'), trunc(systimestamp,'dd') + interval '6' hour from dual union all

4 select to_timestamp('2010-11-11','yyyy-mm-dd'), trunc(systimestamp,'dd') + interval '12' hour from dual union all

5 select to_timestamp('2010-12-01','yyyy-mm-dd'), trunc(systimestamp,'dd') + interval '18' hour from dual

6 /

Table created.

SQL> desc mytable

Name Null? Type

----------------------------------------- -------- ----------------------------

CLOSE_DATE TIMESTAMP(9)

CREATE_DATE DATE

SQL> select close_date - create_date

2 from mytable

3 /

CLOSE_DATE-CREATE_DATE

---------------------------------------------------------------------------

+000000130 18:00:00.000000000

+000000140 12:00:00.000000000

+000000160 06:00:00.000000000

3 rows selected.

SQL> select extract(day from (close_date - create_date))

2 from mytable

3 /

EXTRACT(DAYFROM(CLOSE_DATE-CREATE_DATE))

----------------------------------------

130

140

160

3 rows selected.