Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <pgsql-hackers-owner+archive@lists.postgresql.org>)
	id 1lZfuh-0002bu-MI
	for pgsql-hackers@arkaria.postgresql.org; Thu, 22 Apr 2021 20:27:03 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-hackers-owner+archive@lists.postgresql.org>)
	id 1lZfug-0002ml-EA
	for pgsql-hackers@arkaria.postgresql.org; Thu, 22 Apr 2021 20:27:02 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1lZfuf-0002me-Ks
	for pgsql-hackers@lists.postgresql.org; Thu, 22 Apr 2021 20:27:01 +0000
Received: from sss.pgh.pa.us ([66.207.139.130])
	by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1lZfud-0007KD-CM
	for pgsql-hackers@postgresql.org; Thu, 22 Apr 2021 20:27:00 +0000
Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1])
	by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 13MKQrxs1197051;
	Thu, 22 Apr 2021 16:26:53 -0400
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
cc: David Steele <david@pgmasters.net>,
        Pavel Stehule <pavel.stehule@gmail.com>,
        Thomas Munro <thomas.munro@gmail.com>,
        Petr Fedorov <petr.fedorov@phystech.edu>,
        pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when
 extracting epoch
In-reply-to: <226163.1618847845@sss.pgh.pa.us>
References: 
 <CA+hUKGK+K8NyS57tU841m1tSF6ha3W0udN2dw_Hbc=D_0OPbmA@mail.gmail.com>
 <ee683994-8f7c-aded-be43-543e66e68b94@2ndquadrant.com>
 <a3be61d9-f44b-7fce-3dc8-d700fdfb6f48@2ndquadrant.com>
 <CAFj8pRC8cgoiQMT235cky4Pa40gj=s0m3PftmaJLSt9MAiwj_Q@mail.gmail.com>
 <be30c07c-3f7b-7c86-f1c5-d0858046b26f@2ndquadrant.com>
 <1551357.1599435992@sss.pgh.pa.us>
 <097a4242-0748-98e3-06ab-ded1d366cd76@2ndquadrant.com>
 <1893468.1599658711@sss.pgh.pa.us>
 <38fc499a-ae52-efb6-aad6-82f812d6782d@2ndquadrant.com>
 <20200917055327.GU2873@paquier.xyz> <20200930071543.GL1996@paquier.xyz>
 <2fb99632-9302-6509-78ce-3aaed3fd7f40@2ndquadrant.com>
 <3ff4be02-1e2b-564f-9b53-3dc561da8218@pgmasters.net>
 <3013402.1615829712@sss.pgh.pa.us>
 <8fae8673-0d5b-12d9-7342-daed0d590594@2ndquadrant.com>
 <901bcfb8-36c8-27b2-b148-042392992eeb@2ndquadrant.com>
 <4158031.1616184417@sss.pgh.pa.us>
 <75db3efe-8023-58ef-9ee4-8a6a3101c7ca@2ndquadrant.com>
 <226163.1618847845@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Mon, 19 Apr 2021 11:57:25 -0400"
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <1197007.1619123143.0@sss.pgh.pa.us>
Date: Thu, 22 Apr 2021 16:26:53 -0400
Message-ID: <1197050.1619123213@sss.pgh.pa.us>
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Archived-At: 
 <https://www.postgresql.org/message-id/1197050.1619123213%40sss.pgh.pa.us>
Precedence: bulk

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1197007.1619123143.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable

I wrote:
> So I don't think there's any code change required (unless you are still
> worried about speed).  What we do need is documentation fixes:
> * clarify the above bit about local vs UTC midnight
> * document the existence of the julian field for date_part/extract
> * fix this bit in the to_char docs to agree with reality,
> ie s/UTC/local time/:
>         <entry>Julian Day (integer days since November 24, 4714 BC at mi=
dnight UTC)</entry>
> Perhaps it'd be worth documenting that you can get the standard
> astronomical definition of Julian date by transposing to time zone UTC-1=
2
> before converting.  But I think trying to change PG's behavior at this
> point would be a bad idea.

Here's a concrete documentation proposal covering this.

			regards, tom lane


------- =_aaaaaaaaaa0
Content-Type: text/x-diff; name="document-julian-dates-better-1.patch";
	charset="us-ascii"
Content-ID: <1197007.1619123143.2@sss.pgh.pa.us>
Content-Description: document-julian-dates-better-1.patch
Content-Transfer-Encoding: quoted-printable

diff --git a/doc/src/sgml/datetime.sgml b/doc/src/sgml/datetime.sgml
index 39fbc39cb0..c069281d1a 100644
--- a/doc/src/sgml/datetime.sgml
+++ b/doc/src/sgml/datetime.sgml
@@ -763,9 +763,6 @@
   <indexterm zone=3D"datetime-units-history">
    <primary>Gregorian calendar</primary>
   </indexterm>
-  <indexterm zone=3D"datetime-units-history">
-   <primary>Julian date</primary>
-  </indexterm>
 =

   <para>
    The SQL standard states that <quote>Within the definition of a
@@ -868,6 +865,15 @@ $ <userinput>cal 9 1752</userinput>
    festivals.
   </para>
 =

+  </sect1>
+
+  <sect1 id=3D"datetime-julian-dates">
+  <title>Julian Dates</title>
+
+  <indexterm zone=3D"datetime-julian-dates">
+   <primary>Julian date</primary>
+  </indexterm>
+
   <para>
    The <firstterm>Julian Date</firstterm> system is another type of
    calendar, unrelated to the Julian calendar though it is confusingly
@@ -876,6 +882,9 @@ $ <userinput>cal 9 1752</userinput>
    Joseph Justus Scaliger (1540&ndash;1609)
    and probably takes its name from Scaliger's father,
    the Italian scholar Julius Caesar Scaliger (1484&ndash;1558).
+  </para>
+
+  <para>
    In the Julian Date system, each day has a sequential number, starting
    from JD 0 (which is sometimes called <emphasis>the</emphasis> Julian D=
ate).
    JD 0 corresponds to 1 January 4713 BC in the Julian calendar, or
@@ -891,7 +900,26 @@ $ <userinput>cal 9 1752</userinput>
    input and output of dates (and also uses Julian dates for some interna=
l
    datetime calculations), it does not observe the nicety of having dates
    run from noon to noon.  <productname>PostgreSQL</productname> treats a=
 Julian Date
-   as running from midnight to midnight.
+   as running from local midnight to local midnight, the same as a normal
+   date.
+  </para>
+
+  <para>
+   This definition does, however, provide a way to obtain the astronomica=
l
+   definition when you need it: do the arithmetic in time
+   zone <literal>UTC-12</literal>.  For example,
+<programlisting>
+=3D&gt; SELECT extract(julian from '2021-06-23 7:00:00-04'::timestamptz a=
t time zone 'UTC-12');
+           extract            =

+------------------------------
+ 2459389.95833333333333333333
+(1 row)
+=3D&gt; SELECT extract(julian from '2021-06-23 8:00:00-04'::timestamptz a=
t time zone 'UTC-12');
+               extract                =

+--------------------------------------
+ 2459390.0000000000000000000000000000
+(1 row)
+</programlisting>
   </para>
 =

  </sect1>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 5bba13973f..105555cfb4 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -7539,7 +7539,8 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*=
)){1,1}');
        </row>
        <row>
         <entry><literal>J</literal></entry>
-        <entry>Julian Day (integer days since November 24, 4714 BC at mid=
night UTC)</entry>
+        <entry>Julian Date (integer days since November 24, 4714 BC at lo=
cal
+        midnight; see <xref linkend=3D"datetime-julian-dates"/>)</entry>
        </row>
        <row>
         <entry><literal>Q</literal></entry>
@@ -9609,6 +9610,25 @@ SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');
       </listitem>
      </varlistentry>
 =

+     <varlistentry>
+      <term><literal>julian</literal></term>
+      <listitem>
+       <para>
+        The <firstterm>Julian Date</firstterm> corresponding to the
+        date or timestamp (not applicable to intervals).  Timestamps
+        that are not local midnight result in a fractional value.  See
+        <xref linkend=3D"datetime-julian-dates"/> for more information.
+       </para>
+
+<screen>
+SELECT EXTRACT(JULIAN FROM DATE '2006-01-01');
+<lineannotation>Result: </lineannotation><computeroutput>2453737</compute=
routput>
+SELECT EXTRACT(JULIAN FROM TIMESTAMP '2006-01-01 12:00');
+<lineannotation>Result: </lineannotation><computeroutput>2453737.50000000=
000000000000</computeroutput>
+</screen>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><literal>microseconds</literal></term>
       <listitem>

------- =_aaaaaaaaaa0--