pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedSome comments about Julian Dates and possible bug. Please provide feedback.
4+ messages / 3 participants
[nested] [flat]
* Some comments about Julian Dates and possible bug. Please provide feedback.
@ 2010-12-28 02:23 Leslie S Satenstein <lsatenstein@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: Leslie S Satenstein @ 2010-12-28 02:23 UTC (permalink / raw)
To: pgsql-docs; +Cc: Robert Haas <robertmhaas@gmail.com>
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
Regards
Leslie
(celebrating 40 years in IT).
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Some comments about Julian Dates and possible bug. Please provide feedback.
@ 2010-12-29 11:58 Robert Haas <robertmhaas@gmail.com>
parent: Leslie S Satenstein <lsatenstein@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: Robert Haas @ 2010-12-29 11:58 UTC (permalink / raw)
To: Leslie S Satenstein <lsatenstein@yahoo.com>; +Cc: pgsql-docs
On Mon, Dec 27, 2010 at 9:23 PM, Leslie S Satenstein
<lsatenstein@yahoo.com> wrote:
> 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
This seems like it'd be more appropriate for pgsql-bugs or
pgsql-hackers rather than here. I can't really figure out exactly
what change you're proposing, so I'm not entirely sure whether it's
right or wrong. Perhaps you could post your patch, and some examples.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Some comments about Julian Dates and possible bug. Please provide feedback.
@ 2010-12-29 17:38 Leslie S Satenstein <lsatenstein@yahoo.com>
parent: Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Leslie S Satenstein @ 2010-12-29 17:38 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; pgsql-docs
Good morning Robert
I presume that you are the acting project manager, in the sense of vetting changes or commentary. The pdf guide has only one statement concerning julian dates. I feel that more is required.
Julian dates are used as follows.
Working days between two dates is (the difference of the day numbers * 5/7) , with minor adjustment for rounding and weekend start-end considerations.
Julian date conversion is used for this type of calculation. It would be nice to see more in the manual about Julian dates, such as tests if a day is a weekend day, or to determine the day of the week for a specific Gregorian calendar date and vice-versa.
Regarding the julian bug(let), I will raise it as a comment on the code buglist.
------------------
Regards
Leslie
Mr. Leslie Satenstein
mailto:lsatenstein@yahoo.com
mailto leslie.satenstein@itbms.biz / leslies@itbms.biz
www.itbms.biz
--- On Wed, 12/29/10, Robert Haas <robertmhaas@gmail.com> wrote:
> From: Robert Haas <robertmhaas@gmail.com>
> Subject: Re: [DOCS] Some comments about Julian Dates and possible bug. Please provide feedback.
> To: "Leslie S Satenstein" <lsatenstein@yahoo.com>
> Cc: "pgsql-docs" <pgsql-docs@postgresql.org>
> Date: Wednesday, December 29, 2010, 6:58 AM
> On Mon, Dec 27, 2010 at 9:23 PM,
> Leslie S Satenstein
> <lsatenstein@yahoo.com>
> wrote:
> > 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
>
> This seems like it'd be more appropriate for pgsql-bugs or
> pgsql-hackers rather than here. I can't really figure
> out exactly
> what change you're proposing, so I'm not entirely sure
> whether it's
> right or wrong. Perhaps you could post your patch,
> and some examples.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
> --
> Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-docs
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Some comments about Julian Dates and possible bug. Please provide feedback.
@ 2010-12-29 17:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Leslie S Satenstein <lsatenstein@yahoo.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2010-12-29 17:56 UTC (permalink / raw)
To: Leslie S Satenstein <lsatenstein@yahoo.com>; +Cc: Robert Haas <robertmhaas@gmail.com>; pgsql-docs
Leslie S Satenstein <lsatenstein@yahoo.com> writes:
>> 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.
This is not a bug, it's just failure to understand the conventions used
internally. If you did the calculations at the SQL level you would get
the right answers:
regression=# select '0001-01-01'::date - 1;
?column?
---------------
0001-12-31 BC
(1 row)
regression=# select '0001-01-01'::date - '0001-12-31 BC'::date;
?column?
----------
1
(1 row)
Internally we represent 1 BC as "year zero", 2 BC as "year -1", etc,
but this isn't a problem from users' perspective.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-12-29 17:56 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-12-28 02:23 Some comments about Julian Dates and possible bug. Please provide feedback. Leslie S Satenstein <lsatenstein@yahoo.com>
2010-12-29 11:58 ` Robert Haas <robertmhaas@gmail.com>
2010-12-29 17:38 ` Leslie S Satenstein <lsatenstein@yahoo.com>
2010-12-29 17:56 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox