sql - Oracle 和 Teradata 之间的日期差异

Question

我在计算 Oracle(11.2.0.3.0) 和 Teradata(13.10.07.15) 中两个日期之间的差异时遇到了一个奇怪的问题。Oracle 中的 NLS_CALENDAR 参数设置为 GREGORIAN。

Oracle                                                              Teradata
------------------------------------------------------------------------------------------------------------
select trunc(sysdate) - date'1800-01-01' diff from dual;            select date - date'1800-01-01' diff;    

DIFF                                                                diff    
----------                                                          ----------- 
78224                                                               78224

到目前为止没有区别。

select trunc(sysdate) - date'1500-01-01' diff from dual;            select date - date'1500-01-01' diff;

DIFF                                                                diff
----------                                                          -----------
187788                                                              187797

相差 9 天。

select trunc(sysdate) - date'1000-01-01' diff from dual;            select date - date'1000-01-01' diff;

      DIFF                                                          diff
----------                                                          -----------
    370413                                                          370418

相差 5 天。

select trunc(sysdate) - date'0500-01-01' diff from dual;            select date - date'0500-01-01' diff;

      DIFF                                                          diff
----------                                                          -----------
    553038                                                          553039

相差 1 天。

select trunc(sysdate) - date'0001-01-01' diff from dual;            select date - date'0001-01-01' diff;

      DIFF                                                          diff
----------                                                          -----------
    735297                                                          735295

相差 2 天。

有谁知道为什么会这样？

Answer 1

很简单，Oracle 错了:-)

select
   date '1582-10-04'+1, 
   date '1582-10-05'+1, 
   date '1582-10-14'+1, 
   date '1582-10-15'+1 
from dual;

|             DATE'1582-10-04'+1 |             DATE'1582-10-05'+1 |             DATE'1582-10-14'+1 |             DATE'1582-10-15'+1 |
|--------------------------------|--------------------------------|--------------------------------|--------------------------------|
| October, 15 1582 00:00:00+0000 | October, 16 1582 00:00:00+0000 | October, 25 1582 00:00:00+0000 | October, 16 1582 00:00:00+0000 |

对于日期差异，Teradata 使用标准 SQL 日历实现，这是一种公历，而 Oracle 使用一种公历(即使 NLS 称之为 GREGORIAN)。

参见 Gulutzan/Pelzer 的论文:http://www.orafaq.com/papers/dates_o.doc