Skip site navigation (1) Skip section navigation (2)

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

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: bruce(at)momjian(dot)us
Cc: David Lee Lambert <dlambert(at)bmtcarhaul(dot)com>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: "Julian day" date format is off by 12 hours
Date: 2007-02-19 22:50:36
Message-ID: 200702192250.l1JMoar08767@momjian.us (view raw or flat)
Thread:
Lists: pgsql-bugs
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. +

pgsql-bugs by date

Next:From: Tom LaneDate: 2007-02-20 03:42:38
Subject: Re: BUG #3031: PHP (and others?) cannot close a connection when "in" a COPY-statement
Previous:From: JessicaDate: 2007-02-19 21:53:26
Subject: BUG #3034: FATAL: could not open lock file "/tmp/.s.PGSQL.5432.lock": Permission denied

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group