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

Commit79a5928

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 parent38c9a59 commit79a5928

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+
extract
915+
------------------------------
916+
2459389.95833333333333333333
917+
(1 row)
918+
=&gt; SELECT extract(julian from '2021-06-23 8:00:00-04'::timestamptz at time zone 'UTC-12');
919+
extract
920+
--------------------------------------
921+
2459390.0000000000000000000000000000
922+
(1 row)
923+
=&gt; SELECT extract(julian from date '2021-06-24');
924+
extract
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
@@ -7539,7 +7539,8 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
75397539
</row>
75407540
<row>
75417541
<entry><literal>J</literal></entry>
7542-
<entry>Julian Day (integer days since November 24, 4714 BC at midnight UTC)</entry>
7542+
<entry>Julian Date (integer days since November 24, 4714 BC at local
7543+
midnight; see <xref linkend="datetime-julian-dates"/>)</entry>
75437544
</row>
75447545
<row>
75457546
<entry><literal>Q</literal></entry>
@@ -9609,6 +9610,25 @@ SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');
96099610
</listitem>
96109611
</varlistentry>
96119612

9613+
<varlistentry>
9614+
<term><literal>julian</literal></term>
9615+
<listitem>
9616+
<para>
9617+
The <firstterm>Julian Date</firstterm> corresponding to the
9618+
date or timestamp (not applicable to intervals). Timestamps
9619+
that are not local midnight result in a fractional value. See
9620+
<xref linkend="datetime-julian-dates"/> for more information.
9621+
</para>
9622+
9623+
<screen>
9624+
SELECT EXTRACT(JULIAN FROM DATE '2006-01-01');
9625+
<lineannotation>Result: </lineannotation><computeroutput>2453737</computeroutput>
9626+
SELECT EXTRACT(JULIAN FROM TIMESTAMP '2006-01-01 12:00');
9627+
<lineannotation>Result: </lineannotation><computeroutput>2453737.50000000000000000000</computeroutput>
9628+
</screen>
9629+
</listitem>
9630+
</varlistentry>
9631+
96129632
<varlistentry>
96139633
<term><literal>microseconds</literal></term>
96149634
<listitem>

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp