Re: "Julian day" date format is off by 12 hours

Since to_char() is supposed to be Oracle-compatible, would someone test
this query in Oracle?

---------------------------------------------------------------------------

David Lee Lambert wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +