From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.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: <75db3efe-8023-58ef-9ee4-8a6a3101c7ca@2ndquadrant.com>
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>
Comments: In-reply-to Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
	message dated "Thu, 01 Apr 2021 20:49:45 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <226162.1618847845.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 19 Apr 2021 11:57:25 -0400
Message-ID: <226163.1618847845@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/226163.1618847845%40sss.pgh.pa.us>
Precedence: bulk

Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> The extract(julian from timestamp) is still a bit in the slow mode, but =

> as I previously stated, it's not documented and gives the wrong result, =

> so it's not clear whether it should be fixed and what it should do.  I =

> think I'll register that part as an open item in any case, to see what =

> we should do about that.

I looked into this issue.  It's not quite true that the behavior is
entirely undocumented: Appendix B (datetime.sgml) says

   In the Julian Date system, each day has a sequential number, starting
   from JD 0 (which is sometimes called <emphasis>the</emphasis> Julian Da=
te).
   JD 0 corresponds to 1 January 4713 BC in the Julian calendar, or
   24 November 4714 BC in the Gregorian calendar.  Julian Date counting
   is most often used by astronomers for labeling their nightly observatio=
ns,
   and therefore a date runs from noon UTC to the next noon UTC, rather th=
an
   from midnight to midnight: JD 0 designates the 24 hours from noon UTC o=
n
   24 November 4714 BC to noon UTC on 25 November 4714 BC.
  </para>

  <para>
   Although <productname>PostgreSQL</productname> supports Julian Date not=
ation for
   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.
  </para>

That last bit requires clarification: we treat a Julian date as running
from *local* midnight to local midnight (ie in the active timezone, not
UTC midnight).  So far as I can see, the behavior of extract(julian) is
consistent with that definition:

regression=3D# show timezone;
     TimeZone     =

------------------
 America/New_York
(1 row)

regression=3D# select date_part('julian', '2021-04-19 00:00:01-04'::timest=
amptz);
     date_part     =

-------------------
 2459324.000011574
(1 row)

regression=3D# select date_part('julian', '2021-04-19 23:59:00-04'::timest=
amptz);
     date_part      =

--------------------
 2459324.9993055556
(1 row)

regression=3D# select date_part('julian', '2021-04-19'::date);
 date_part =

-----------
   2459324
(1 row)

I don't see that to_char's J mode differs from this, either.

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/:

       <row>
        <entry><literal>J</literal></entry>
        <entry>Julian Day (integer days since November 24, 4714 BC at midn=
ight UTC)</entry>
       </row>

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.

(We could also consider back-patching these doc fixes.)

			regards, tom lane