Postgresql 中文操作指南

B.7. Julian Dates #

Julian Date 系统是一种对天数进行编号的方法。它与儒略历无关，尽管其名称与该历法有类似的含义。儒略日系统是由法国学者约瑟夫·朱斯图斯·斯卡利格（1540-1609 年）发明的，它的名字可能取自斯卡利格的父亲，意大利学者朱利叶斯·凯撒·斯卡利格（1484-1558）。

The Julian Date system is a method for numbering days. It is unrelated to the Julian calendar, though it is confusingly named similarly to that calendar. The Julian Date system was invented by the French scholar Joseph Justus Scaliger (1540–1609) and probably takes its name from Scaliger’s father, the Italian scholar Julius Caesar Scaliger (1484–1558).

在儒略日系统中，每一天都有一个顺序号，从 JD 0 开始（有时候称为 the 儒略日）。在儒略历中，JD 0 对应于公元前 4713 年 1 月 1 日，或在公历中对应于公元前 4714 年 11 月 24 日。天文学家最常使用儒略日计数系统对他们的夜间观测进行标记，因此一个日期从协调世界时中午到下一个协调世界时中午算起，而不是从午夜到午夜：JD 0 表示公元前 4714 年 11 月 24 日协调世界时中午到公元前 4714 年 11 月 25 日协调世界时中午的 24 小时。

In the Julian Date system, each day has a sequential number, starting from JD 0 (which is sometimes called the Julian Date). JD 0 corresponds to 1 January 4713 BC in the Julian calendar, or 24 November 4714 BC in the Gregorian calendar. Julian Date counting is most often used by astronomers for labeling their nightly observations, and therefore a date runs from noon UTC to the next noon UTC, rather than from midnight to midnight: JD 0 designates the 24 hours from noon UTC on 24 November 4714 BC to noon UTC on 25 November 4714 BC.

尽管 PostgreSQL 支持儒略日符号用于日期的输入和输出（并且也使用儒略日进行一些内部日期时间计算），但它却没有将日期从中午到中午的考究性纳入考虑。PostgreSQL 将一个儒略日当作从当地午夜到当地午夜来处理，与一个普通日期相同。

Although PostgreSQL supports Julian Date notation for input and output of dates (and also uses Julian dates for some internal datetime calculations), it does not observe the nicety of having dates run from noon to noon. PostgreSQL treats a Julian Date as running from local midnight to local midnight, the same as a normal date.

然而，此定义提供了一种在你需要时获取天文定义的方法：在 UTC+12 时区进行算术运算。例如，

This definition does, however, provide a way to obtain the astronomical definition when you need it: do the arithmetic in time zone UTC+12. For example,

=> SELECT extract(julian from '2021-06-23 7:00:00-04'::timestamptz at time zone 'UTC+12');
           extract
------------------------------
 2459388.95833333333333333333
(1 row)
=> SELECT extract(julian from '2021-06-23 8:00:00-04'::timestamptz at time zone 'UTC+12');
               extract
--------------------------------------
 2459389.0000000000000000000000000000
(1 row)
=> SELECT extract(julian from date '2021-06-23');
 extract
---------
 2459389
(1 row)