From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Cc: David Steele <david@pgmasters.net>
Cc: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Thomas Munro <thomas.munro@gmail.com>
Cc: Petr Fedorov <petr.fedorov@phystech.edu>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
Date: Mon, 19 Apr 2021 11:57:25 -0400
Message-ID: <226163.1618847845@sss.pgh.pa.us> (raw)
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>
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 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, peter.eisentraut@2ndquadrant.com, david@pgmasters.net, pavel.stehule@gmail.com, thomas.munro@gmail.com, petr.fedorov@phystech.edu, 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>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox