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

From: Thomas Munro <thomas(dot)munro(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Peter Eisentraut <peter(dot)eisentraut(at)enterprisedb(dot)com>, David Steele <david(at)pgmasters(dot)net>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, Petr Fedorov <petr(dot)fedorov(at)phystech(dot)edu>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
Date: 2021-04-28 05:43:27
Message-ID: CA+hUKGLcLH1k0tK9nRm=B-0jWKCfr2STS4bbqTYxGjNZgvXcRw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

On Wed, Apr 28, 2021 at 3:56 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Of course Wikipedia has been known to contain errors, but now
> I'm inclined to think I blew this. Anyone want to check my work?

I tried a couple of examples not from Wikipedia. First, from the
definition of Julian days as used by astronomers[1], counting from
noon on 4713-01-01 BC Julian AKA 4714-11-24 BC Gregorian, days 0 and 1
look right with 'utc+12':

postgres=# select extract(julian from '4714-11-24 11:00:00+00
BC'::timestamptz at time zone 'utc+12');
ERROR: timestamp out of range
postgres=# select extract(julian from '4714-11-24 12:00:00+00
BC'::timestamptz at time zone 'utc+12');
extract
--------------------------------
0.0000000000000000000000000000
(1 row)

postgres=# select extract(julian from '4714-11-25 11:00:00+00
BC'::timestamptz at time zone 'utc+12');
extract
------------------------
0.95833333333333333333
(1 row)

postgres=# select extract(julian from '4714-11-25 12:00:00+00
BC'::timestamptz at time zone 'utc+12');
extract
--------------------------------
1.0000000000000000000000000000
(1 row)

Next I found a worked example in an aerospace textbook[1] and it agrees, too:

postgres=# select extract(julian from '2004-05-12
14:45:30+00'::timestamptz at time zone 'utc+12');
extract
------------------------------
2453138.11493055555555555556
(1 row)

[1] http://curious.astro.cornell.edu/people-and-astronomy/125-observational-astronomy/timekeeping/calendars/763-how-was-the-starting-point-for-the-julian-date-system-chosen-advanced
[2] https://www.sciencedirect.com/topics/engineering/julian-day-number

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Noah Misch 2021-04-28 05:44:15 Re: BUG #16939: Plural interval for negative singular
Previous Message RekGRpth 2021-04-28 05:37:15 Re: BUG #16985: ModifyWaitEvent function does not have pgsocket fd and void *user_data arguments

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2021-04-28 05:51:43 pg_hba.conf.sample wording improvement
Previous Message Dilip Kumar 2021-04-28 05:33:38 Re: PATCH: logical_work_mem and logical streaming of large in-progress transactions