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

I did some research on this, and because Oracle fails with that query,
we can't use them as a guide.

What is happening in the code is that the "J" is independent from the
"SSSS" and "MS", so you are getting a "J" based on the date (assuming
midnight start/stop), and not on the actual time in the rest of the
timestamp.

As you stated, to do this correctly 11:59am would have a different
Julian date from 12:01pm. However, I think this would make "J" much
less useful because the most common use assumes midnight to 11:59pm is
the same day number. What I did was to update the documentation to say
explicitly "midnight":

Julian Day (days since midnight, January 1, 4712 BC)

I didn't document that we don't follow the specification, but the
"midnight" should be a hint for those who know about it.

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

bruce wrote:
>
> 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. +

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

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