Re: 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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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