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

"Julian day" date format is off by 12 hours

From: David Lee Lambert <dlambert(at)bmtcarhaul(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: "Julian day" date format is off by 12 hours
Date: 2006-12-08 22:46:42
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
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');
 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:


Software Developer,  Precision Motor Transport Group, LLC
Work phone 517-349-3011 x215
Cell phone 586-873-8813


pgsql-bugs by date

Next:From: Jim NasbyDate: 2006-12-09 02:25:02
Subject: Re: BUG #2802: Feature request: tinyint and unsigned types
Previous:From: Brien R. GivensDate: 2006-12-08 18:51:53
Subject: BUG #2818: ADO Field.Attributes reports NULL on NOT NULL fields

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