Postgres version: 8.0.6
Operating system: Ubuntu GNU/Linux
I executed the following query while trying to build some date-conversion
functions for data that was represented as milliseconds since the Unix epoch:
davidl=# SELECT to_char(timestamp '1970-01-01 00:00:00 GMT','J SSSS MS');
to_char
---------------
2440588 0 000
(1 row)
However, Postgres's notion of a "Julian Day" does not match the
generally-accepted definition. According to the generally-accepted
definition, the result of the query above should be
2440587 43200 000
; that is, 12 hours past noon on Julian day 2440687, which started at noon
on December 31st, 1969, GMT.
I'm not sure if this should be regarded as a database bug or a documentation
bug. Table 9-21 in the manual only says that a Julian day is "days since
January 1, 4712 BC", so Postgres is consistent with the manual; but every
other definition of a Julian day I've found says that it starts at noon.
The Wikipedia article has several good references:
http://en.wikipedia.org/wiki/Julian_day
--
Software Developer, Precision Motor Transport Group, LLC
Work phone 517-349-3011 x215
Cell phone 586-873-8813