Thread: BUG #11883: Year 1500 not treated as leap year when it was a leap year
BUG #11883: Year 1500 not treated as leap year when it was a leap year
From
hunsakerbn@familysearch.org
Date:
The following bug has been logged on the website:
Bug reference: 11883
Logged by: Bruce Hunsaker
Email address: hunsakerbn@familysearch.org
PostgreSQL version: 9.3.5
Operating system: Linux
Description:
Entering historical dates we found we could not enter a date of '1500-02-29'
Even though 1500 is documented to be a leap year. Tested with date and
timestamp column types.
To reproduce:
psql> create table date_test (mydate date);
CREATE TABLE
psql> insert into date_test values ('1500-02-29');
ERROR: date/time field value out of range: "1500-02-29"
LINE 1: insert into date_test values ('1500-02-29');
psql> insert into date_test values ('1500-02-28');
INSERT 0 1;
So, Feb 29, is not allowed but Feb 28 is.
On Wed, Nov 5, 2014 at 05:56:07PM +0000, hunsakerbn@familysearch.org wrote:
> The following bug has been logged on the website:
>
> Bug reference: 11883
> Logged by: Bruce Hunsaker
> Email address: hunsakerbn@familysearch.org
> PostgreSQL version: 9.3.5
> Operating system: Linux
> Description:
>
> Entering historical dates we found we could not enter a date of '1500-02-29'
> Even though 1500 is documented to be a leap year. Tested with date and
> timestamp column types.
>
> To reproduce:
> psql> create table date_test (mydate date);
> CREATE TABLE
> psql> insert into date_test values ('1500-02-29');
> ERROR: date/time field value out of range: "1500-02-29"
> LINE 1: insert into date_test values ('1500-02-29');
>
> psql> insert into date_test values ('1500-02-28');
> INSERT 0 1;
>
> So, Feb 29, is not allowed but Feb 28 is.
Uh, what makes you think 1500 was a leap year? This is the canonical
way to calculate which years are leap years:
#define isleap(y) (((y) % 4) == 0 && (((y) % 100) != 0 || ((y) % 400) == 0))
Because 1500 % 100 == 0, I think 1500 was not a leap year.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
Re: BUG #11883: Year 1500 not treated as leap year when it was a leap year
From
Magnus Hagander
Date:
On Wed, Nov 5, 2014 at 7:02 PM, Bruce Momjian <bruce@momjian.us> wrote:
> On Wed, Nov 5, 2014 at 05:56:07PM +0000, hunsakerbn@familysearch.org wrote:
>> The following bug has been logged on the website:
>>
>> Bug reference: 11883
>> Logged by: Bruce Hunsaker
>> Email address: hunsakerbn@familysearch.org
>> PostgreSQL version: 9.3.5
>> Operating system: Linux
>> Description:
>>
>> Entering historical dates we found we could not enter a date of '1500-02-29'
>> Even though 1500 is documented to be a leap year. Tested with date and
>> timestamp column types.
>>
>> To reproduce:
>> psql> create table date_test (mydate date);
>> CREATE TABLE
>> psql> insert into date_test values ('1500-02-29');
>> ERROR: date/time field value out of range: "1500-02-29"
>> LINE 1: insert into date_test values ('1500-02-29');
>>
>> psql> insert into date_test values ('1500-02-28');
>> INSERT 0 1;
>>
>> So, Feb 29, is not allowed but Feb 28 is.
>
> Uh, what makes you think 1500 was a leap year? This is the canonical
> way to calculate which years are leap years:
>
> #define isleap(y) (((y) % 4) == 0 && (((y) % 100) != 0 || ((y) % 400) == 0))
>
> Because 1500 % 100 == 0, I think 1500 was not a leap year.
I believe it was a leap year in the Julian calendar, maybe that's
where the difference comes from?
http://en.wikipedia.org/wiki/1500
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
Re: BUG #11883: Year 1500 not treated as leap year when it was a leap year
From
Bruce Hunsaker
Date:
On 05 Nov 14 13 02, Bruce Momjian wrote:
> On Wed, Nov 5, 2014 at 05:56:07PM +0000, hunsakerbn@familysearch.org wrote:
> > The following bug has been logged on the website:
> >
> > Bug reference: 11883
> > Logged by: Bruce Hunsaker
> > Email address: hunsakerbn@familysearch.org
> > PostgreSQL version: 9.3.5
> > Operating system: Linux
> > Description:
> >
> > Entering historical dates we found we could not enter a date of '1500-02-29'
> > Even though 1500 is documented to be a leap year. Tested with date and
> > timestamp column types.
> >
> > To reproduce:
> > psql> create table date_test (mydate date);
> > CREATE TABLE
> > psql> insert into date_test values ('1500-02-29');
> > ERROR: date/time field value out of range: "1500-02-29"
> > LINE 1: insert into date_test values ('1500-02-29');
> >
> > psql> insert into date_test values ('1500-02-28');
> > INSERT 0 1;
> >
> > So, Feb 29, is not allowed but Feb 28 is.
>
> Uh, what makes you think 1500 was a leap year? This is the canonical
> way to calculate which years are leap years:
>
> #define isleap(y) (((y) % 4) == 0 && (((y) % 100) != 0 || ((y) % 400) == 0))
>
> Because 1500 % 100 == 0, I think 1500 was not a leap year.
Well, I believe that rule started at 1600.
Wikipedia says it's a leap year. As do some other sources I checked.
--
Bruce Hunsaker
NOTICE: This email message is for the sole use of the intended recipient(s) and may contain confidential and
privilegedinformation. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the
intendedrecipient, please contact the sender by reply email and destroy all copies of the original message.
On 5.11.2014 19:02, Bruce Momjian wrote:
> On Wed, Nov 5, 2014 at 05:56:07PM +0000, hunsakerbn@familysearch.org wrote:
>> The following bug has been logged on the website:
>>
>> Bug reference: 11883
>> Logged by: Bruce Hunsaker
>> Email address: hunsakerbn@familysearch.org
>> PostgreSQL version: 9.3.5
>> Operating system: Linux
>> Description:
>>
>> Entering historical dates we found we could not enter a date of '1500-02-29'
>> Even though 1500 is documented to be a leap year. Tested with date and
>> timestamp column types.
>>
>> To reproduce:
>> psql> create table date_test (mydate date);
>> CREATE TABLE
>> psql> insert into date_test values ('1500-02-29');
>> ERROR: date/time field value out of range: "1500-02-29"
>> LINE 1: insert into date_test values ('1500-02-29');
>>
>> psql> insert into date_test values ('1500-02-28');
>> INSERT 0 1;
>>
>> So, Feb 29, is not allowed but Feb 28 is.
>
> Uh, what makes you think 1500 was a leap year? This is the canonical
> way to calculate which years are leap years:
>
> #define isleap(y) (((y) % 4) == 0 && (((y) % 100) != 0 || ((y) % 400) == 0))
>
> Because 1500 % 100 == 0, I think 1500 was not a leap year.
Well, the thing is this only works since 1582. Prior to that, only the
first condition was used.
Tomas
Magnus Hagander <magnus@hagander.net> writes:
> On Wed, Nov 5, 2014 at 7:02 PM, Bruce Momjian <bruce@momjian.us> wrote:
>> Because 1500 % 100 == 0, I think 1500 was not a leap year.
> I believe it was a leap year in the Julian calendar, maybe that's
> where the difference comes from?
Indeed. We won't be changing our code though, because we document that
we follow Gregorian calendar rules even before that calendar was instituted
(ie, proleptic Gregorian calendar). You could argue for doing that
differently, but then what are you going to do for dates before the Julian
calendar was instituted? In any case, this behavior appears to be
required by the SQL standard, which repeatedly says that datetime values
are "constrained according to the Gregorian calendar".
regards, tom lane
On Wed, Nov 5, 2014 at 01:13:21PM -0500, Tom Lane wrote: > Magnus Hagander <magnus@hagander.net> writes: > > On Wed, Nov 5, 2014 at 7:02 PM, Bruce Momjian <bruce@momjian.us> wrote: > >> Because 1500 % 100 == 0, I think 1500 was not a leap year. > > > I believe it was a leap year in the Julian calendar, maybe that's > > where the difference comes from? > > Indeed. We won't be changing our code though, because we document that > we follow Gregorian calendar rules even before that calendar was instituted > (ie, proleptic Gregorian calendar). You could argue for doing that > differently, but then what are you going to do for dates before the Julian > calendar was instituted? In any case, this behavior appears to be > required by the SQL standard, which repeatedly says that datetime values > are "constrained according to the Gregorian calendar". I have applied the attached C comment to document why we use the Gregorian calendar for pre-1582 years. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
Attachment
Re: BUG #11883: Year 1500 not treated as leap year when it was a leap year
From
Bruce Hunsaker
Date:
On 06 Nov 14 02 34, Bruce Momjian wrote: > On Wed, Nov 5, 2014 at 01:13:21PM -0500, Tom Lane wrote: > > Magnus Hagander <magnus@hagander.net> writes: > > > On Wed, Nov 5, 2014 at 7:02 PM, Bruce Momjian <bruce@momjian.us> wrote: > > >> Because 1500 % 100 == 0, I think 1500 was not a leap year. > > > > > I believe it was a leap year in the Julian calendar, maybe that's > > > where the difference comes from? > > > > Indeed. We won't be changing our code though, because we document that > > we follow Gregorian calendar rules even before that calendar was instituted > > (ie, proleptic Gregorian calendar). You could argue for doing that > > differently, but then what are you going to do for dates before the Julian > > calendar was instituted? In any case, this behavior appears to be > > required by the SQL standard, which repeatedly says that datetime values > > are "constrained according to the Gregorian calendar". > > I have applied the attached C comment to document why we use the > Gregorian calendar for pre-1582 years. Thanks everyone for the response. I guess the bottom line for us is that if we want to store dates before 1582, we may not want to use date or timestamp columns for that data, particularly if the dates are from a Julian calendar. -- Bruce Hunsaker NOTICE: This email message is for the sole use of the intended recipient(s) and may contain confidential and privilegedinformation. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intendedrecipient, please contact the sender by reply email and destroy all copies of the original message.
On Thu, Nov 6, 2014 at 07:29:49AM -0700, Bruce Hunsaker wrote: > > I have applied the attached C comment to document why we use the > > Gregorian calendar for pre-1582 years. > > Thanks everyone for the response. I guess the bottom line for us > is that if we want to store dates before 1582, we may not want > to use date or timestamp columns for that data, particularly if > the dates are from a Julian calendar. Yeah, the big problem is that there is no way to store leap days for years like 1500. The only good part is that the Gregorian calendar is very good at keeping the calendar aligned with the seasons. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
Re: BUG #11883: Year 1500 not treated as leap year when it was a leap year
From
Alvaro Herrera
Date:
Bruce Momjian wrote: > On Thu, Nov 6, 2014 at 07:29:49AM -0700, Bruce Hunsaker wrote: > > > I have applied the attached C comment to document why we use the > > > Gregorian calendar for pre-1582 years. > > > > Thanks everyone for the response. I guess the bottom line for us > > is that if we want to store dates before 1582, we may not want > > to use date or timestamp columns for that data, particularly if > > the dates are from a Julian calendar. > > Yeah, the big problem is that there is no way to store leap days for > years like 1500. The only good part is that the Gregorian calendar is > very good at keeping the calendar aligned with the seasons. I think this warrants a big "Meh". Here we have a use case for which a very good calendar system would be truly useful, and it seems as we satisfy almost all of what the OP needs; yet we fail only because of a mostly trivial leap year issue. I agree we don't want a simplistic change that would only change the rule from Gregorian to Julian without much other thinking; but suppose Bruce H was able to come up with a well reasoned design to cover all interesting cases, would we consider accepting a patch that changed the behavior? I would also go as far as suggesting that a different data type might be a useful direction to consider. (Now I am assuming that Bruce H is willing to put in the effort to make this work in the first place.) -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> I agree we don't want a simplistic change that would only change the
> rule from Gregorian to Julian without much other thinking; but suppose
> Bruce H was able to come up with a well reasoned design to cover all
> interesting cases, would we consider accepting a patch that changed the
> behavior?
Meh. Using Julian calendar from circa 50 BC to 1582 is the "right thing"
in some abstract sense, but:
1. What ya gonna do before 50 BC? If your argument against proleptic
Gregorian is that it doesn't represent what people actually did, then
replacing it with proleptic Julian is no better.
2. There's that two weeks or so that completely disappeared in the switch.
Those are dates we'd no longer accept (or at least if we did, it's none
too clear what we'd do with them). This creates a dump/reload hazard.
3. Between #2 and the altered frequency of leap years, stored dates
before 1582 would change meaning rather substantially, so that the
results of pg_upgrade would differ from a dump/reload.
4. We'd be adding a substantial amount of complexity, cycles, and possibly
bugs to all datetime operations, in service of what is evidently a tiny
minority requirement (and one that not only lacks support in the SQL
standard, but is arguably contrary to the express wording of same).
5. A side issue that's probably pretty relevant for the OP's application
is that not every country adopted the Gregorian calendar at the same time.
Really if he wants any data integrity he's going to need to store an
explicit indication of whether a date is in Julian or Gregorian calendar.
(In a world of infinite manpower you could imagine extending the zic
database to provide information about which calendar was used when in
every region, but I don't see us going there.)
So the risk/reward/backwards-compatibility tradeoff just doesn't look
very good from here.
I think the OP's best bet is to store old dates as text, or perhaps
integers, with some CHECK constraints to make whatever validity checks
he deems appropriate.
> I would also go as far as suggesting that a different data
> type might be a useful direction to consider.
Yeah, an extension data type is also a possibility, but it'd be a
significant amount of work.
regards, tom lane
Re: BUG #11883: Year 1500 not treated as leap year when it was a leap year
From
Bruce Hunsaker
Date:
On 07 Nov 14 02 02, Bruce Momjian wrote: > On Thu, Nov 6, 2014 at 07:29:49AM -0700, Bruce Hunsaker wrote: > > > I have applied the attached C comment to document why we use the > > > Gregorian calendar for pre-1582 years. > > > > Thanks everyone for the response. I guess the bottom line for us > > is that if we want to store dates before 1582, we may not want > > to use date or timestamp columns for that data, particularly if > > the dates are from a Julian calendar. > > Yeah, the big problem is that there is no way to store leap days for > years like 1500. The only good part is that the Gregorian calendar is > very good at keeping the calendar aligned with the seasons. Just to add some additional background, We were migrating data from an Oracle DB when we hit this. Oracle's dates are Julian based and range from Jan 1, 4712 BCE to Dec 31 9999 CE. So Feb 29, 1500 is a valid date in an Oracle Date type (Julian day 2268992). So I wonder what the rules might be to convert a Julian date to a 'proleptic Gregorian' date (pre 1582) and vice versa? -- Bruce Hunsaker NOTICE: This email message is for the sole use of the intended recipient(s) and may contain confidential and privilegedinformation. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intendedrecipient, please contact the sender by reply email and destroy all copies of the original message.
Re: BUG #11883: Year 1500 not treated as leap year when it was a leap year
From
Bruce Hunsaker
Date:
On 07 Nov 14 10 06, Tom Lane wrote: > 5. A side issue that's probably pretty relevant for the OP's application > is that not every country adopted the Gregorian calendar at the same time. > Really if he wants any data integrity he's going to need to store an > explicit indication of whether a date is in Julian or Gregorian calendar. > (In a world of infinite manpower you could imagine extending the zic > database to provide information about which calendar was used when in > every region, but I don't see us going there.) It's quite relevant for us, and for other systems, and so we do our own date standardization to take into account those factors. Unfortunately this specific data pre-dates our standardization process, and we are not sure it's worth the effort to fix, particularly since we only got errors for the one date. > > So the risk/reward/backwards-compatibility tradeoff just doesn't look > very good from here. > > I think the OP's best bet is to store old dates as text, or perhaps > integers, with some CHECK constraints to make whatever validity checks > he deems appropriate. This has been, and is our approach for new systems going forward. Thanks -- Bruce Hunsaker NOTICE: This email message is for the sole use of the intended recipient(s) and may contain confidential and privilegedinformation. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intendedrecipient, please contact the sender by reply email and destroy all copies of the original message.
Bruce Hunsaker <hunsakerbn@ldschurch.org> writes:
> Just to add some additional background, We were migrating data from
> an Oracle DB when we hit this. Oracle's dates are Julian based
> and range from Jan 1, 4712 BCE to Dec 31 9999 CE. So Feb 29, 1500
> is a valid date in an Oracle Date type (Julian day 2268992). So I
> wonder what the rules might be to convert a Julian date to a
> 'proleptic Gregorian' date (pre 1582) and vice versa?
I think you have a bigger data-integrity problem than you realize.
Perhaps I'm jumping to conclusions based on your return address,
but I suppose what you've got here is a database of historical
birth and death records from lots of different countries? Your
problem, as I mentioned upthread, is that you're going to need some
context to figure out whether any given recorded date was meant as
Julian or Gregorian. If you don't know that for sure, applying a
transformation that's only going to be right for 80% or 90% of your
data will make things worse not better.
In the short term you'd probably be best off just storing the Oracle
output as text. You could consider converting it to some type with
stronger semantics later, but you'll have enough on your plate
making the switch at all.
regards, tom lane
Re: BUG #11883: Year 1500 not treated as leap year when it was a leap year
From
Bruce Hunsaker
Date:
On 07 Nov 14 10 40, Tom Lane wrote: > Bruce Hunsaker <hunsakerbn@ldschurch.org> writes: > > Just to add some additional background, We were migrating data from > > an Oracle DB when we hit this. Oracle's dates are Julian based > > and range from Jan 1, 4712 BCE to Dec 31 9999 CE. So Feb 29, 1500 > > is a valid date in an Oracle Date type (Julian day 2268992). So I > > wonder what the rules might be to convert a Julian date to a > > 'proleptic Gregorian' date (pre 1582) and vice versa? > > I think you have a bigger data-integrity problem than you realize. > Perhaps I'm jumping to conclusions based on your return address, > but I suppose what you've got here is a database of historical > birth and death records from lots of different countries? Your > problem, as I mentioned upthread, is that you're going to need some > context to figure out whether any given recorded date was meant as > Julian or Gregorian. If you don't know that for sure, applying a > transformation that's only going to be right for 80% or 90% of your > data will make things worse not better. I was thinking the same thing after my last post. > In the short term you'd probably be best off just storing the Oracle > output as text. You could consider converting it to some type with > stronger semantics later, but you'll have enough on your plate > making the switch at all. That's an excellent suggestion. I'm going to advise that we store the Oracle output as text, so the original value gets preserved. -- Bruce Hunsaker NOTICE: This email message is for the sole use of the intended recipient(s) and may contain confidential and privilegedinformation. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intendedrecipient, please contact the sender by reply email and destroy all copies of the original message.
On Fri, Nov 7, 2014 at 10:06:26AM -0500, Tom Lane wrote:
> 5. A side issue that's probably pretty relevant for the OP's application
> is that not every country adopted the Gregorian calendar at the same time.
> Really if he wants any data integrity he's going to need to store an
> explicit indication of whether a date is in Julian or Gregorian calendar.
> (In a world of infinite manpower you could imagine extending the zic
> database to provide information about which calendar was used when in
> every region, but I don't see us going there.)
Oh, it is worse than the date of the switch to Gregorian --- Russia had
some really odd calendars:
http://novaonline.nvcc.edu/eli/evans/his241/Notes/Calendar.html
So, roughly speaking, in the nineteenth century, the Russian calendar
was 12 days behind the Western European calendar.
http://en.wikipedia.org/wiki/Soviet_calendar
The Soviet calendar added five- and six-day work weeks between 1929 and
1940 to the Gregorian calendar adopted by Russia in 1918.
Many Russian events, even those in the 20th century, have multiple dates
for a single day/event, based on the calendar system used.
We could figure out a way to relax our code to allow for leap year
handling for pre-1582 dates, but the larger issue is that date
calculations would also be affected by the existance of such Julian-only leap
days, whether you stored those dates or not, meaning this change would
affect everyone, not just those who want to store such dates. For
example, should:
SELECT '1500-03-01'::date - '1500-02-28'::date;
return 1 or 2?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +