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

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