From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Eisentraut <peter.eisentraut@enterprisedb.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: Thu, 22 Apr 2021 16:26:53 -0400
Message-ID: <1197050.1619123213@sss.pgh.pa.us> (raw)
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>
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
Attachments:document-julian-dates-better-1.patch (text/x-diff, 4.0K) downloaddiff --git a/doc/src/sgml/datetime.sgml b/doc/src/sgml/datetime.sgmlindex 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.sgmlindex 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>
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@enterprisedb.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: <1197050.1619123213@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