Oracle 11.1 错误将儒略日数转换为 DATE 或 TIMESTAMP [英] Oracle 11.1 bug converting Julian day number to DATE or TIMESTAMP

问题描述

这篇最近的帖子吸引了我忙于调查 Oracle 中的 Julian 日期转换，我遇到了我认为是 Oracle 11.1 中的错误.测试用例是:

This recent post got me busy investigating Julian date conversions in Oracle, and I've come across what I believe is a bug in Oracle 11.1. Test cases are:

案例 1.

SELECT TO_CHAR(TO_TIMESTAMP('0', 'J'), 'DD MON SYYYY') FROM DUAL

这应该按照此处的定义返回01 JAN -4713"，而是引发错误

This should return "01 JAN -4713" as defined here, but instead raises the error

ORA-01854: julian date must be between 1 and 5373484

案例 2.

SELECT TO_CHAR(TO_TIMESTAMP('1', 'J'), 'DD MON SYYYY') FROM DUAL

这应该返回02 JAN -4713"作为上述的扩展(比朱利安零日期晚一天)，而是返回01 JAN -4712"(比一年少一天).

This should return "02 JAN -4713" as an extension of the above (one day later than the Julian zero date), but instead returns "01 JAN -4712" (off by a day less than a year).

案例 3.

SELECT TO_CHAR(TO_TIMESTAMP('1721424', 'J'), 'DD MON SYYYY') FROM DUAL

返回01 JAN 0001".没关系(就目前而言).如果我们然后从上面的日期值中减去 1，我们会期望它返回前一天，即 31 DEC -0001(零年不存在)；但是，当我们执行以下操作时

returns "01 JAN 0001". That's fine (as far as it goes). If we then subtract 1 from the date value above we would expect it to return the previous day, i.e. 31 DEC -0001 (year zero does not exist); however, when we execute the following

SELECT TO_CHAR(TO_TIMESTAMP('1721423', 'J'), 'DD MON SYYYY') FROM DUAL

抛出以下错误:

ORA-01841: (full) year must be between -4713 and +9999, and not be 0

表明 Oracle 已尝试生成零年份.

indicating that Oracle has attempted to generate a year of zero.

(请注意，虽然在上面的测试用例中使用了 TO_TIMESTAMP，但使用 TO_DATE 时会出现完全相同的问题).

(Note that although TO_TIMESTAMP is used in the test cases above, the exact same problems occur when TO_DATE is used).

有人知道吗

1. Oracle 是否记录了这些问题?
2. 这些问题在 11.2 中仍然存在吗?

分享和享受.

根据 Phil 在下面的回答，这些问题在 11.2 中仍然存在.

Per Phil's answer below, these issues still occur in 11.2.

克苏鲁 fhtagn.

Cthulhu fhtagn.

10.2.0.4 中的相同错误

Same bugs in 10.2.0.4

推荐答案

当试图了解 Oracle 应该做什么时，请查看 Oracle 的 文档，儒略日数是自公元前 4712 年 1 月 1 日以来的天数."

When trying to see what Oracle is supposed to do, look at Oracle's documentation, "A Julian day number is the number of days since January 1, 4712 BC."

该措辞确实暗示 Julian 1 将是自公元前 4712 年 1 月 1 日以来的一天，即 1 月 2 日.然而，Julian 日期计算的当前实施已经存在很长时间，现有代码取决于在行为上.(我知道如果 Oracle 中实现的 Julian 定义发生变化，我们会被搞砸.)在这一点上，自公元前 4713 年 12 月 31 日以来最多只是一个文档错误.

That wording does seam to imply that Julian 1 would be one days since January 1, 4712 BC, in other words January 2. However the current implementation of Julian date calculation has been in place for a long time, with existing code depending on the behavior. (I know we'd be screwed if the definition of Julian implemented in Oracle were to change.) At this point it would be at most a documentation bug to be days since December 31, 4713 BC.

EDIT 发现了一个 Call Interface 中的参考 Julian 1 是 1 月 1 日程序员指南.不是普通数据库程序员会关注的地方.

EDIT Found a reference for Julian 1 being January 1, in Call Interface Programmer's Guide. Not someplace normal database programmers would ever look at.

以下解释了维基百科和甲骨文之间的年份差异:

The following explains the year difference between wikipedia and Oracle:

Oracle 数据库使用计算儒略的天文系统天，其中公元前 4713 年指定为 -4712.历史的相比之下，计算儒略日的系统将公元前 4713 年指定为-4713.如果您将 Oracle Julian 天数与使用历史系统计算的值进行比较，请注意考虑 365 天BC 日期的差异.有关更多信息，请参阅http://www.usno.navy.mil/USNO/astronomical-applications/astronomical-information-center/millennium.

Oracle Database uses the astronomical system of calculating Julian days, in which the year 4713 BC is specified as -4712. The historical system of calculating Julian days, in contrast, specifies 4713 BC as -4713. If you are comparing Oracle Julian days with values calculated using the historical system, then take care to allow for the 365-day difference in BC dates. For more information, see http://www.usno.navy.mil/USNO/astronomical-applications/astronomical-information-center/millennium.

案例 3 对我来说是新闻.谢谢你提出来.我不知道有任何关于该行为的参考资料.相关:

Case 3 is news to me. Thank you for bringing it up. I do not know of any reference covering that behavior. Related:

SQL> select to_date('0001-01-01', 'YYYY-MM-DD') 
    - to_date ('-0001-12-31', 'SYYYY-MM-DD') from dual;

TO_DATE('0001-01-01','YYYY-MM-DD')-TO_DATE('-0001-12-31','SYYYY-MM-DD')
-----------------------------------------------------------------------
                                                                    367

和

SQL> select months_between(to_date('0001-01-01', 'YYYY-MM-DD')
  2      , to_date ('-0001-12-31', 'SYYYY-MM-DD')) from dual;

MONTHS_BETWEEN(TO_DATE('0001-01-01','YYYY-MM-DD'),TO_DATE('-0001-12-31','SYYYY-MM-DD'))
---------------------------------------------------------------------------------------
                                                                             12.0322581

显然不存在的第 0 年是闰年.

Apparently the non existent year 0 is a leap year.

这篇关于Oracle 11.1 错误将儒略日数转换为 DATE 或 TIMESTAMP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！