Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit ec5bab9

Browse filesBrowse 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 8019fcb commit ec5bab9
Copy full SHA for ec5bab9

File tree

Expand file treeCollapse file tree

2 files changed

+61
-7
lines changed
Open diff view settings
Filter options
Expand file treeCollapse file tree

2 files changed

+61
-7
lines changed
Open diff view settings
Collapse file

‎doc/src/sgml/datetime.sgml‎

Copy file name to clipboardExpand all lines: doc/src/sgml/datetime.sgml
+40-6Lines changed: 40 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -763,9 +763,6 @@
763763
<indexterm zone="datetime-units-history">
764764
<primary>Gregorian calendar</primary>
765765
</indexterm>
766-
<indexterm zone="datetime-units-history">
767-
<primary>Julian date</primary>
768-
</indexterm>
769766

770767
<para>
771768
The SQL standard states that <quote>Within the definition of a
@@ -868,14 +865,27 @@ $ <userinput>cal 9 1752</userinput>
868865
festivals.
869866
</para>
870867

868+
</sect1>
869+
870+
<sect1 id="datetime-julian-dates">
871+
<title>Julian Dates</title>
872+
873+
<indexterm zone="datetime-julian-dates">
874+
<primary>Julian date</primary>
875+
</indexterm>
876+
871877
<para>
872-
The <firstterm>Julian Date</firstterm> system is another type of
873-
calendar, unrelated to the Julian calendar though it is confusingly
878+
The <firstterm>Julian Date</firstterm> system is a method for
879+
numbering days. It is
880+
unrelated to the Julian calendar, though it is confusingly
874881
named similarly to that calendar.
875882
The Julian Date system was invented by the French scholar
876883
Joseph Justus Scaliger (1540&ndash;1609)
877884
and probably takes its name from Scaliger's father,
878885
the Italian scholar Julius Caesar Scaliger (1484&ndash;1558).
886+
</para>
887+
888+
<para>
879889
In the Julian Date system, each day has a sequential number, starting
880890
from JD 0 (which is sometimes called <emphasis>the</emphasis> Julian Date).
881891
JD 0 corresponds to 1 January 4713 BC in the Julian calendar, or
@@ -891,7 +901,31 @@ $ <userinput>cal 9 1752</userinput>
891901
input and output of dates (and also uses Julian dates for some internal
892902
datetime calculations), it does not observe the nicety of having dates
893903
run from noon to noon. <productname>PostgreSQL</productname> treats a Julian Date
894-
as running from midnight to midnight.
904+
as running from local midnight to local midnight, the same as a normal
905+
date.
906+
</para>
907+
908+
<para>
909+
This definition does, however, provide a way to obtain the astronomical
910+
definition when you need it: do the arithmetic in time
911+
zone <literal>UTC-12</literal>. For example,
912+
<programlisting>
913+
=&gt; SELECT extract(julian from '2021-06-23 7:00:00-04'::timestamptz at time zone 'UTC-12');
914+
date_part
915+
--------------------
916+
2459389.9583333335
917+
(1 row)
918+
=&gt; SELECT extract(julian from '2021-06-23 8:00:00-04'::timestamptz at time zone 'UTC-12');
919+
date_part
920+
-----------
921+
2459390
922+
(1 row)
923+
=&gt; SELECT extract(julian from date '2021-06-24');
924+
date_part
925+
-----------
926+
2459390
927+
(1 row)
928+
</programlisting>
895929
</para>
896930

897931
</sect1>
Collapse file

‎doc/src/sgml/func.sgml‎

Copy file name to clipboardExpand all lines: doc/src/sgml/func.sgml
+21-1Lines changed: 21 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7404,7 +7404,8 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
74047404
</row>
74057405
<row>
74067406
<entry><literal>J</literal></entry>
7407-
<entry>Julian Day (integer days since November 24, 4714 BC at midnight UTC)</entry>
7407+
<entry>Julian Date (integer days since November 24, 4714 BC at local
7408+
midnight; see <xref linkend="datetime-julian-dates"/>)</entry>
74087409
</row>
74097410
<row>
74107411
<entry><literal>Q</literal></entry>
@@ -9452,6 +9453,25 @@ SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');
94529453
</listitem>
94539454
</varlistentry>
94549455

9456+
<varlistentry>
9457+
<term><literal>julian</literal></term>
9458+
<listitem>
9459+
<para>
9460+
The <firstterm>Julian Date</firstterm> corresponding to the
9461+
date or timestamp (not applicable to intervals). Timestamps
9462+
that are not local midnight result in a fractional value. See
9463+
<xref linkend="datetime-julian-dates"/> for more information.
9464+
</para>
9465+
9466+
<screen>
9467+
SELECT EXTRACT(JULIAN FROM DATE '2006-01-01');
9468+
<lineannotation>Result: </lineannotation><computeroutput>2453737</computeroutput>
9469+
SELECT EXTRACT(JULIAN FROM TIMESTAMP '2006-01-01 12:00');
9470+
<lineannotation>Result: </lineannotation><computeroutput>2453737.5</computeroutput>
9471+
</screen>
9472+
</listitem>
9473+
</varlistentry>
9474+
94559475
<varlistentry>
94569476
<term><literal>microseconds</literal></term>
94579477
<listitem>

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.