Movatterモバイル変換

[0]ホーム
URL:

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

 Sign up
Appearance settings

Commitec5bab9

Browse files
committed
Doc: document EXTRACT(JULIAN ...), improve Julian Date explanation.
For some reason, the "julian" option for extract()/date_part() hasnever gotten listed in the manual. Also, while Appendix B mentionedin passing that we don't conform to the usual astronomical definitionthat a Julian date starts at noon UTC, it was kind of vague about whatwe do instead. Clarify that, and add an example showing how to getthe 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 parent8019fcb commitec5bab9

File tree

2 files changed

+61
-7
lines changed

2 files changed

+61
-7
lines changed

‎doc/src/sgml/datetime.sgml

Lines 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>

‎doc/src/sgml/func.sgml

Lines 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)
[8]ページ先頭
©2009-2025 Movatter.jp