Type to to_char(d, 'J')?

On Wed, Jul 31, 2013 at 11:09:22AM +0200, Marc Dahn wrote:

Dear list,

Section 9.8 of the postgres (9.1) documentation says, on the
patterns for to_char(timestamp, pattern),:

J Julian Day (days since November 24, 4714 BC at midnight)

This leaves open the question of what's actually returned. At least
in astronomy, it is customary to have fractional days in JDs, whereas
postgres appears to always return an integer. Is that guaranteed
behaviour?

The reason I'm asking is that I'd like to use the expression

to_char($1, 'J')::double precision 
+ to_char($1,'ssss')::double_precision/86400 
- 2400001

to compute the modified julian date (MJD) from a postgres timestamp
in some software that may be around for longer. If postgres at some
point decided to return fractional days, that would blow up.

If integers are guaranteed, might I suggest to change the
documentation to read

J Chronological Julian Day (integer number of days since November 24,
4714 BC at midnight)

Done and backpatched to 9.3. I went with a simpler:

Julian Day (integer days since November 24, 4714 BC at midnight)

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general