Lists: | pgsql-hackers |
---|
From: | "Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au> |
---|---|
To: | "Hackers" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | 3 digit year problem |
Date: | 2002-05-03 07:17:12 |
Message-ID: | GNELIHDDFBOCMGBFGEFOEEHBCCAA.chriskl@familyhealth.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Hi,
Is there any rhyme or reason to these ISO format date parsing rules?
test=# select '1-1-1'::date;
ERROR: Bad date external representation '1-1-1'
test=# select '69-1-1'::date;
date
------------
2069-01-01
(1 row)
test=# select '50-1-1'::date;
date
------------
2050-01-01
(1 row)
test=# select '40-1-1'::date;
date
------------
2040-01-01
(1 row)
test=# select '30-1-1'::date;
ERROR: Bad date external representation '30-1-1'
test=# select '100-1-1'::date;
ERROR: Bad date external representation '100-1-1'
test=# select '999-1-1'::date;
ERROR: Bad date external representation '999-1-1'
test=# select '1000-1-1'::date;
date
------------
1000-01-01
(1 row)
Why can't someone store the year without having to pad with zeros for years
between 100 and 999?
What's wrong with 30-1-1 and below? Why does 40 work and not 30?
Chris
From: | Thomas Lockhart <lockhart(at)fourpalms(dot)org> |
---|---|
To: | Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> |
Cc: | Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: 3 digit year problem |
Date: | 2002-05-03 14:07:29 |
Message-ID: | 3CD299A1.96C42A11@fourpalms.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
> Is there any rhyme or reason to these ISO format date parsing rules?
Yes. Though adjustments to the rules are possible, so things are not set
in concrete. There *should* be a complete description of the date/time
parsing rules in the User's Guide appendix.
> Why can't someone store the year without having to pad with zeros for years
> between 100 and 999?
To help distinguish between day numbers and years. We used to allow more
variations in the length of a year field, but have tightened it up a bit
over the years.
> What's wrong with 30-1-1 and below? Why does 40 work and not 30?
Because "30" *could* be a day. "40" can only be something intended to be
a year. And input is not enforced to be strictly ISO-compliant, so
"30-1-1" *could* be interpreted multiple ways.
- Thomas