Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch - Mailing list pgsql-hackers

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 midnight 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-12
> 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

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="datetime-units-history">
    <primary>Gregorian calendar</primary>
   </indexterm>
-  <indexterm zone="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="datetime-julian-dates">
+  <title>Julian Dates</title>
+
+  <indexterm zone="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–1609)
    and probably takes its name from Scaliger's father,
    the Italian scholar Julius Caesar Scaliger (1484–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 Date).
    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 internal
    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 astronomical
+   definition when you need it: do the arithmetic in time
+   zone <literal>UTC-12</literal>.  For example,
+<programlisting>
+=> SELECT extract(julian from '2021-06-23 7:00:00-04'::timestamptz at time zone 'UTC-12');
+           extract
+------------------------------
+ 2459389.95833333333333333333
+(1 row)
+=> SELECT extract(julian from '2021-06-23 8:00:00-04'::timestamptz at 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 midnight UTC)</entry>
+        <entry>Julian Date (integer days since November 24, 4714 BC at local
+        midnight; see <xref linkend="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="datetime-julian-dates"/> for more information.
+       </para>
+
+<screen>
+SELECT EXTRACT(JULIAN FROM DATE '2006-01-01');
+<lineannotation>Result: </lineannotation><computeroutput>2453737</computeroutput>
+SELECT EXTRACT(JULIAN FROM TIMESTAMP '2006-01-01 12:00');
+<lineannotation>Result: </lineannotation><computeroutput>2453737.50000000000000000000</computeroutput>
+</screen>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><literal>microseconds</literal></term>
       <listitem>