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

Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)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 Date).
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 observations,
and therefore a date runs from noon UTC to the next noon UTC, rather than
from midnight to midnight: JD 0 designates the 24 hours from noon UTC on
24 November 4714 BC to noon UTC on 25 November 4714 BC.
</para>

<para>
Although <productname>PostgreSQL</productname> supports Julian Date notation 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=# show timezone;
TimeZone
------------------
America/New_York
(1 row)

regression=# select date_part('julian', '2021-04-19 00:00:01-04'::timestamptz);
date_part
-------------------
2459324.000011574
(1 row)

regression=# select date_part('julian', '2021-04-19 23:59:00-04'::timestamptz);
date_part
--------------------
2459324.9993055556
(1 row)

regression=# 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 midnight 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