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@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

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