Skip to content

Commit b391db4

Browse files
tglsfdctglsfdc
committed
Doc: document EXTRACT(JULIAN ...), improve Julian Date explanation.
For some reason, the "julian" option for extract()/date_part() has never gotten listed in the manual. Also, while Appendix B mentioned in passing that we don't conform to the usual astronomical definition that a Julian date starts at noon UTC, it was kind of vague about what we do instead. Clarify that, and add an example showing how to get the astronomical definition if you want it. It's been like this for ages, so back-patch to all supported branches. Discussion: https://postgr.es/m/1197050.1619123213@sss.pgh.pa.us
1 parent 6cb7dcd commit b391db4

File tree

2 files changed

+61
-7
lines changed

2 files changed

+61
-7
lines changed

‎doc/src/sgml/datetime.sgml

+40-6
Original file line numberDiff line numberDiff line change
@@ -785,9 +785,6 @@
785785
<indexterm zone="datetime-units-history">
786786
<primary>Gregorian calendar</primary>
787787
</indexterm>
788-
<indexterm zone="datetime-units-history">
789-
<primary>Julian date</primary>
790-
</indexterm>
791788

792789
<para>
793790
The SQL standard states that <quote>Within the definition of a
@@ -890,14 +887,27 @@ $ <userinput>cal 9 1752</userinput>
890887
festivals.
891888
</para>
892889

890+
</sect1>
891+
892+
<sect1 id="datetime-julian-dates">
893+
<title>Julian Dates</title>
894+
895+
<indexterm zone="datetime-julian-dates">
896+
<primary>Julian date</primary>
897+
</indexterm>
898+
893899
<para>
894-
The <firstterm>Julian Date</firstterm> system is another type of
895-
calendar, unrelated to the Julian calendar though it is confusingly
900+
The <firstterm>Julian Date</firstterm> system is a method for
901+
numbering days. It is
902+
unrelated to the Julian calendar, though it is confusingly
896903
named similarly to that calendar.
897904
The Julian Date system was invented by the French scholar
898905
Joseph Justus Scaliger (1540-1609)
899906
and probably takes its name from Scaliger's father,
900907
the Italian scholar Julius Caesar Scaliger (1484-1558).
908+
</para>
909+
910+
<para>
901911
In the Julian Date system, each day has a sequential number, starting
902912
from JD 0 (which is sometimes called <emphasis>the</> Julian Date).
903913
JD 0 corresponds to 1 January 4713 BC in the Julian calendar, or
@@ -913,7 +923,31 @@ $ <userinput>cal 9 1752</userinput>
913923
input and output of dates (and also uses Julian dates for some internal
914924
datetime calculations), it does not observe the nicety of having dates
915925
run from noon to noon. <productname>PostgreSQL</> treats a Julian Date
916-
as running from midnight to midnight.
926+
as running from local midnight to local midnight, the same as a normal
927+
date.
928+
</para>
929+
930+
<para>
931+
This definition does, however, provide a way to obtain the astronomical
932+
definition when you need it: do the arithmetic in time
933+
zone <literal>UTC-12</literal>. For example,
934+
<programlisting>
935+
=&gt; SELECT extract(julian from '2021-06-23 7:00:00-04'::timestamptz at time zone 'UTC-12');
936+
date_part
937+
--------------------
938+
2459389.9583333335
939+
(1 row)
940+
=&gt; SELECT extract(julian from '2021-06-23 8:00:00-04'::timestamptz at time zone 'UTC-12');
941+
date_part
942+
-----------
943+
2459390
944+
(1 row)
945+
=&gt; SELECT extract(julian from date '2021-06-24');
946+
date_part
947+
-----------
948+
2459390
949+
(1 row)
950+
</programlisting>
917951
</para>
918952

919953
</sect1>

‎doc/src/sgml/func.sgml

+21-1
Original file line numberDiff line numberDiff line change
@@ -6002,7 +6002,8 @@ SELECT regexp_matches('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
60026002
</row>
60036003
<row>
60046004
<entry><literal>J</literal></entry>
6005-
<entry>Julian Day (integer days since November 24, 4714 BC at midnight UTC)</entry>
6005+
<entry>Julian Date (integer days since November 24, 4714 BC at local
6006+
midnight; see <xref linkend="datetime-julian-dates">)</entry>
60066007
</row>
60076008
<row>
60086009
<entry><literal>Q</literal></entry>
@@ -7656,6 +7657,25 @@ SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');
76567657
</listitem>
76577658
</varlistentry>
76587659

7660+
<varlistentry>
7661+
<term><literal>julian</literal></term>
7662+
<listitem>
7663+
<para>
7664+
The <firstterm>Julian Date</firstterm> corresponding to the
7665+
date or timestamp (not applicable to intervals). Timestamps
7666+
that are not local midnight result in a fractional value. See
7667+
<xref linkend="datetime-julian-dates"> for more information.
7668+
</para>
7669+
7670+
<screen>
7671+
SELECT EXTRACT(JULIAN FROM DATE '2006-01-01');
7672+
<lineannotation>Result: </lineannotation><computeroutput>2453737</computeroutput>
7673+
SELECT EXTRACT(JULIAN FROM TIMESTAMP '2006-01-01 12:00');
7674+
<lineannotation>Result: </lineannotation><computeroutput>2453737.5</computeroutput>
7675+
</screen>
7676+
</listitem>
7677+
</varlistentry>
7678+
76597679
<varlistentry>
76607680
<term><literal>microseconds</literal></term>
76617681
<listitem>

0 commit comments

Comments
 (0)