pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  

help / color / mirror / Atom feed
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: Tue, 27 Apr 2021 11:56:22 -0400
Message-ID: <2658907.1619538982@sss.pgh.pa.us> (raw)
In-Reply-To: <1197050.1619123213@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>
	<1197050.1619123213@sss.pgh.pa.us>

I wrote:
>> 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.

BTW ... I'd first thought that the way to do this was to rotate to
time zone UTC+12.  I convinced myself on two separate days that UTC-12
was correct instead, but now I'm thinking I was right the first time.
In particular, the results I'm getting with UTC-12 don't square with
the example on Wikipedia [1], which says "the Julian Date for
00:30:00.0 UT January 1, 2013, is 2 456 293.520 833":

regression=# select extract(julian from '2013-01-01 00:30+00'::timestamptz at time zone 'utc-12');
           extract            
------------------------------
 2456294.52083333333333333333
(1 row)

But using UTC+12 does match:

regression=# select extract(julian from '2013-01-01 00:30+00'::timestamptz at time zone 'utc+12');
           extract            
------------------------------
 2456293.52083333333333333333
(1 row)

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?

			regards, tom lane

[1] https://en.wikipedia.org/wiki/Julian_day





view thread (47+ messages)  latest in thread

Message-ID: <2658907.1619538982@sss.pgh.pa.us>
Permalink:  ../2658907.1619538982@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/2658907.1619538982@sss.pgh.pa.us

 · 

reply
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: <2658907.1619538982@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