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

Some comments about Julian Dates and possible bug. Please provide feedback.

From: Leslie S Satenstein <lsatenstein(at)yahoo(dot)com>
To: pgsql-docs <pgsql-docs(at)postgresql(dot)org>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>
Subject: Some comments about Julian Dates and possible bug. Please provide feedback.
Date: 2010-12-28 02:23:03
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-docs
Hi Robert, 

If my email is not for the documentation group, please forward.

I am not sure if this has meaning to PG users. 

In the 9.0.2 PG documentation that was posted concerning Julian dates, I noted that comments stated that Julian dates were used mainly for astronomical calculations.

I would like to let you know the importance of Julian dates in the ERP manufacturing sector.  In fact, here is how it is used.

In the factory, work and material scheduling, as well as material requirements planning usually refers to a manufacturing calendar. These calendars often have 1000 working days to a manufacturing year. Using Julian dates in the software makes it easy to calculate target dates x working days after a given day. Calculations to take the Gregorian date, convert it to Julian, and then add a duration quantity before converting back, is where the use of the julian date shines. Calculations for differences between two dates is important and easily done.

I found the Julian date code that is programmed in Postgres, to be accurate and fast except for one situation. But, I do have one or two questions. 1) Which calendar is being used?   

In the Gregorian Calendar,  January 1, 0001 is lowest positive date.
The day before 1/1/0001, according to the Gregorian calendar is December 31,-0001 in which the year has a value of negative one --- there is no zero year in the Gregorian Calendar. (zero year is an error)

I tested and found the algorithm in Postgres to have the day before January 1,0001 calculating as December 31,0000 even though the world calculates the day before January 1,0001 as December 31,-0001.

2) Is the algorithm in Postgres correct?  I think not, as the calculations for the difference in days between January 1, 0001 and December 31,-0001 is not 367 days, but just the value 1.

To convert the code to work with the Gregorian calendar takes two fixes to two sub-routines. Each fix is two lines of C code.

I have tested the PG Date C language routines with/without my fix, starting with the year around -4713 to several centuries into the future. As long as both versions calculate Julian dates that are later than 1/1/0001, both the PG and my fixed versions respecting Gregorian algorithms produce identical results. 

3) Does PG want to fully follow the Gregorian Calendar rule?  If so, 
4) do they want my one patch with two fixes6


(celebrating 40 years in IT).

In response to


pgsql-docs by date

Next:From: Fujii MasaoDate: 2010-12-28 04:26:09
Subject: alter-table.sgml and DEFAULT clause
Previous:From: Leslie S SatensteinDate: 2010-12-27 01:28:56
Subject: Words missing in the following txt

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