Using YY-MM-DD date input
Does anyone use YY-MM-DD for date input?
Right now, it only works mostly for pre-2000 dates because we can detect
that 97-02-03 is a year, while we can not detect that in 03-02-01.
We are considering eliminating it for 7.4. You can still use
yyyy-mm-dd, or course.
Comments?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Does anyone use YY-MM-DD for date input?
We are considering eliminating it for 7.4. You can still use
yyyy-mm-dd, or course.
The way I think the date input parser should work when seeing three
numeric values is:
1. If first field has four digits, then format is yyyy-mm-dd.
2. If first field is larger than 31, assume format is yy-mm-dd.
(I'm not totally wedded to this, since it could be argued to be
allowing the input values to determine the interpretation, which
is exactly the kind of heuristic that people objected to in the
dd/mm vs mm/dd discussion last month. It seems reasonable to me,
but it could be removed without affecting the rest of this proposal.)
3. Otherwise, the format must be one of yy-mm-dd, dd-mm-yy, or mm-dd-yy.
We should use DateStyle to decide which one of these applies.
There are presently only two input DateStyles ('US' and 'European') but
it would be trivial to add a third to accept yy-mm-dd. We'd only need
to figure out what to call it. I'm tempted to just call it 'YMD' and
provide 'DMY' and 'MDY' as alternative names for 'US' and 'European'.
We could also use datestyle to decide what to do with ambiguous inputs
like 03-FEB-01 --- given a 3-way input DateStyle, I'd say YMD should
mean that the year is first, while the other two mean the day is first.
regards, tom lane
On Friday, July 25, 2003, at 04:02 PM, Bruce Momjian wrote:
Does anyone use YY-MM-DD for date input?
Right now, it only works mostly for pre-2000 dates because we can
detect
that 97-02-03 is a year, while we can not detect that in 03-02-01.We are considering eliminating it for 7.4. You can still use
yyyy-mm-dd, or course.
Does this apply to MM/DD/YY as well, or just the YY-MM-DD variation?
I still have to deal with data (from mainframes) which has some
dates in MM/DD/YY format, and will have to for the foreseeable
future.
But I always load that type of data into a temp table of text
columns and "convert" it with a view. As long as I can still CAST a
YY-MM-DD (actually MM/DD/YY) to a valid date then this would not be
a problem for me.
I wouldn't be happy to see it dropped (because I know I'll continue
to have to deal with data with 2-digit years), but I can get around
it.
-heath
Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Does anyone use YY-MM-DD for date input?
We are considering eliminating it for 7.4. You can still use
yyyy-mm-dd, or course.The way I think the date input parser should work when seeing three
numeric values is:1. If first field has four digits, then format is yyyy-mm-dd.
OK
2. If first field is larger than 31, assume format is yy-mm-dd.
(I'm not totally wedded to this, since it could be argued to be
allowing the input values to determine the interpretation, which
is exactly the kind of heuristic that people objected to in the
dd/mm vs mm/dd discussion last month. It seems reasonable to me,
but it could be removed without affecting the rest of this proposal.)
I am not excited about this, though you could say it is an extension of
the 4-digit rule. However, I don't like that 30-03-03 is in 2003 and
32-03-03 is in the year 32AD. That seems error-prone to me. :-)
3. Otherwise, the format must be one of yy-mm-dd, dd-mm-yy, or mm-dd-yy.
We should use DateStyle to decide which one of these applies.
I see this as a feature addition, because right now, in 2003, there is
no way to enter a current date with a leading year using only two digits.
Also, if there aren't many people who want it, I am concerned it will
cause confusion, because our datestyle stuff is already complicated.
There are presently only two input DateStyles ('US' and 'European') but
it would be trivial to add a third to accept yy-mm-dd. We'd only need
to figure out what to call it. I'm tempted to just call it 'YMD' and
provide 'DMY' and 'MDY' as alternative names for 'US' and 'European'.
Now, that is an interesting idea, and I wonder if they aren't better
than US and European (and German), because they are more general.
Is this something we want to do in feature freeze?
We could also use datestyle to decide what to do with ambiguous inputs
like 03-FEB-01 --- given a 3-way input DateStyle, I'd say YMD should
mean that the year is first, while the other two mean the day is first.
Interesting.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Heath Tanner wrote:
On Friday, July 25, 2003, at 04:02 PM, Bruce Momjian wrote:
Does anyone use YY-MM-DD for date input?
Right now, it only works mostly for pre-2000 dates because we can
detect
that 97-02-03 is a year, while we can not detect that in 03-02-01.We are considering eliminating it for 7.4. You can still use
yyyy-mm-dd, or course.Does this apply to MM/DD/YY as well, or just the YY-MM-DD variation?
I still have to deal with data (from mainframes) which has some
dates in MM/DD/YY format, and will have to for the foreseeable
future.
This only deals with leading years as YY. It doesn't effect trailing YY
at all.
But I always load that type of data into a temp table of text
columns and "convert" it with a view. As long as I can still CAST a
YY-MM-DD (actually MM/DD/YY) to a valid date then this would not be
a problem for me.I wouldn't be happy to see it dropped (because I know I'll continue
to have to deal with data with 2-digit years), but I can get around
it.
Don't worry, we will never get rid of training YY.
\
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Bruce Momjian <pgman@candle.pha.pa.us> writes:
I see this as a feature addition, because right now, in 2003, there is
no way to enter a current date with a leading year using only two digits.
How can you say that, when there is a regression test that proves it
works (and always has worked)?
There are presently only two input DateStyles ('US' and 'European') but
it would be trivial to add a third to accept yy-mm-dd. We'd only need
to figure out what to call it. I'm tempted to just call it 'YMD' and
provide 'DMY' and 'MDY' as alternative names for 'US' and 'European'.
Now, that is an interesting idea, and I wonder if they aren't better
than US and European (and German), because they are more general.
I'm envisioning these as determining the input interpretation only.
The output formatting choices are a distinct set. (It was a bad idea to
overload DateStyle to contain two separate settings, but we're probably
stuck with that for now.) But yes, I could easily be talked into making
these names be the standard ones. Right now would be the time to do it,
since we're already changing the output format of "SHOW DATESTYLE"; if
we wait a cycle then we'll be churning that API twice in a row.
Is this something we want to do in feature freeze?
I think it's a necessary part of the already-proposed patch to tighten
input date interpretation. Now personally I'd be quite happy to put off
that whole affair for 7.5, because I don't think it's been thought
through adequately. But if you want to complete that open item in this
cycle, then I think we have to follow it out to the logical conclusions.
You can't arbitrarily decide to remove functionality and not put a
substitute in place just because we're past feature freeze. (A freeze
extends to not removing stuff, as well as not adding stuff, IMHO.)
regards, tom lane
On Fri, 2003-07-25 at 15:02, Bruce Momjian wrote:
Does anyone use YY-MM-DD for date input?
Right now, it only works mostly for pre-2000 dates because we can detect
that 97-02-03 is a year, while we can not detect that in 03-02-01.We are considering eliminating it for 7.4. You can still use
yyyy-mm-dd, or course.Comments?
What about the sliding window approach that some used to "solve"
the y2k problem: any year between, say '00' and '32' is presumed
to be in the 21st century, but years between '33' and '99' are
20th century.
However, dropping it and letting the app deal with it is my non-
counting vote...
--
+-----------------------------------------------------------------+
| Ron Johnson, Jr. Home: ron.l.johnson@cox.net |
| Jefferson, LA USA |
| |
| "I'm not a vegetarian because I love animals, I'm a vegetarian |
| because I hate vegetables!" |
| unknown |
+-----------------------------------------------------------------+Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
I see this as a feature addition, because right now, in 2003, there is
no way to enter a current date with a leading year using only two digits.How can you say that, when there is a regression test that proves it
works (and always has worked)?
I said "no way to enter a current date":
no way to enter a current date with a leading year using only two digits.
^^^^^^^^^^^^^^
It might work, but do we want it to work in 2003. I say no, just like
we don't want to arbitrarily guess which is the day and which the month.
You haven't told me how anyone is using yy-mm-dd for date input in 2003,
so removing it doesn't seem like a big deal to me.
There are presently only two input DateStyles ('US' and 'European') but
it would be trivial to add a third to accept yy-mm-dd. We'd only need
to figure out what to call it. I'm tempted to just call it 'YMD' and
provide 'DMY' and 'MDY' as alternative names for 'US' and 'European'.Now, that is an interesting idea, and I wonder if they aren't better
than US and European (and German), because they are more general.I'm envisioning these as determining the input interpretation only.
The output formatting choices are a distinct set. (It was a bad idea to
overload DateStyle to contain two separate settings, but we're probably
stuck with that for now.) But yes, I could easily be talked into making
It would be nice to specify the input and output formats independently.
I think we can sort of do that now, but it isn't clear. When format is
Postgres, US/European control whether month is first in input and
output. When it is ISO, the US/European only controls input for non-ISO
dates. It isn't very clear, but does hit the common uses.
One problem with a YMD format specification is that it isn't clear that
that applies only to two-digit years because ISO is already YMD. I
wonder if we have to call them YYMMDD, MMDDYY, and DDMMYY.
these names be the standard ones. Right now would be the time to do it,
since we're already changing the output format of "SHOW DATESTYLE"; if
we wait a cycle then we'll be churning that API twice in a row.
Oh, good point. I had forgotten about that.
Is this something we want to do in feature freeze?
I think it's a necessary part of the already-proposed patch to tighten
input date interpretation. Now personally I'd be quite happy to put off
that whole affair for 7.5, because I don't think it's been thought
through adequately. But if you want to complete that open item in this
cycle, then I think we have to follow it out to the logical conclusions.
You can't arbitrarily decide to remove functionality and not put a
substitute in place just because we're past feature freeze. (A freeze
extends to not removing stuff, as well as not adding stuff, IMHO.)
His patch came in before feature freeze. We are only addressing it in
feature freeze.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Ron Johnson wrote:
On Fri, 2003-07-25 at 15:02, Bruce Momjian wrote:
Does anyone use YY-MM-DD for date input?
Right now, it only works mostly for pre-2000 dates because we can detect
that 97-02-03 is a year, while we can not detect that in 03-02-01.We are considering eliminating it for 7.4. You can still use
yyyy-mm-dd, or course.Comments?
What about the sliding window approach that some used to "solve"
the y2k problem: any year between, say '00' and '32' is presumed
to be in the 21st century, but years between '33' and '99' are
20th century.
We are doing that now, but with only two digits, we have to assume a
year 00-31 is a day, not a year.
However, dropping it and letting the app deal with it is my non-
counting vote...
OK.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
2. If first field is larger than 31, assume format is yy-mm-dd.
(I'm not totally wedded to this, since it could be argued to be
allowing the input values to determine the interpretation, which
Well, just give people the choice by:
set date_input_heuristics to true;
the default being "false", perhaps.
is exactly the kind of heuristic that people objected to in the
dd/mm vs mm/dd discussion last month. It seems reasonable to me,
but it could be removed without affecting the rest of this proposal.)
I think it makes a lot of sense if combined with a switch
"Yes, I want heuristics".
I am not excited about this, though you could say it is an extension of
the 4-digit rule. However, I don't like that 30-03-03 is in 2003 and
32-03-03 is in the year 32AD. That seems error-prone to me. :-)
Well, but that's what the input means. Or, to be more precise,
that is what the "32-03-03" means. Either it is a data input
error (date_input_heuristics is false and input style is not
YMD) or it is 32AD (input style YMD and heuristics off OR
heuristics on and un-ambigous year field detected). Of course,
for ambigous data even heuristics==true should have to revert
back to strict checking and signal ambiguity if
non-resolvable.
Just my thoughts,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Bruce Momjian <pgman@candle.pha.pa.us> writes:
It would be nice to specify the input and output formats independently.
They are independent now.
I think we can sort of do that now, but it isn't clear. When format is
Postgres, US/European control whether month is first in input and
output. When it is ISO, the US/European only controls input for non-ISO
dates. It isn't very clear, but does hit the common uses.
Obviously not clear, if you don't understand it ;-). Here is the
current situation:
* The output part of DateStyle is the ISO/Postgres/SQL/German setting.
This determines the output format, and nothing else.
* The input part of DateStyle is the US/European setting. This
determines the resolution of dd vs mm during input, but only when
the input is ambiguous --- if the code can infer which is dd and which
is mm by range, it will do that regardless of DateStyle.
The discussion last month concluded that the heuristic resolution of
dd/mm is dangerous, and that we ought to drop the heuristics in favor
of saying that dd/mm is resolved only on the basis of DateStyle. That's
okay as far as it goes (I'm not very happy with it, but I understand
that I'm outvoted on that point). The point I'm trying to make is that
we need to extend input DateStyle so that this approach can apply to all
commonly used two-digit-year formats, or we are going to lose
functionality.
regards, tom lane
On Fri, 2003-07-25 at 18:55, Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
It would be nice to specify the input and output formats independently.
[snip]
that I'm outvoted on that point). The point I'm trying to make is that
we need to extend input DateStyle so that this approach can apply to all
commonly used two-digit-year formats, or we are going to lose
functionality.
Or drop YY because it's not y2k compliant.
--
+-----------------------------------------------------------------+
| Ron Johnson, Jr. Home: ron.l.johnson@cox.net |
| Jefferson, LA USA |
| |
| "I'm not a vegetarian because I love animals, I'm a vegetarian |
| because I hate vegetables!" |
| unknown |
+-----------------------------------------------------------------+Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
It would be nice to specify the input and output formats independently.
They are independent now.
I think we can sort of do that now, but it isn't clear. When format is
Postgres, US/European control whether month is first in input and
output. When it is ISO, the US/European only controls input for non-ISO
dates. It isn't very clear, but does hit the common uses.Obviously not clear, if you don't understand it ;-). Here is the
current situation:* The output part of DateStyle is the ISO/Postgres/SQL/German setting.
This determines the output format, and nothing else.* The input part of DateStyle is the US/European setting. This
determines the resolution of dd vs mm during input, but only when
the input is ambiguous --- if the code can infer which is dd and which
is mm by range, it will do that regardless of DateStyle.The discussion last month concluded that the heuristic resolution of
dd/mm is dangerous, and that we ought to drop the heuristics in favor
of saying that dd/mm is resolved only on the basis of DateStyle. That's
okay as far as it goes (I'm not very happy with it, but I understand
that I'm outvoted on that point). The point I'm trying to make is that
we need to extend input DateStyle so that this approach can apply to all
commonly used two-digit-year formats, or we are going to lose
functionality.
But we lose functionality that can't possibily be used in 2003 because
03-01-01 doesn't identify 03 as a year. This idea hasn't been used
since 2000, and will not be possible again since 2032, and I think Y2K
was enough to show us that we don't want to encourage such usage.
I think removing this functionality is a good idea, and matches our
new tightening up of the date specification.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Bruce Momjian <pgman@candle.pha.pa.us> writes:
But we lose functionality that can't possibily be used in 2003 because
03-01-01 doesn't identify 03 as a year.
This argument is specious. You could equally well use it to justify
removing our support for dd-mm-yy and mm-dd-yy, because those aren't
unique either.
regards, tom lane
Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
But we lose functionality that can't possibily be used in 2003 because
03-01-01 doesn't identify 03 as a year.This argument is specious. You could equally well use it to justify
removing our support for dd-mm-yy and mm-dd-yy, because those aren't
unique either.
I must not be understanding you. Given our current feature set,
removing the ability to specify YY-MM-DD when the year is greater than
31 just seems useless to me, and a cause of possible errors.
To be specific, your complaint, I think, is that we don't want to lose
the functionality that says 97-01-02 is from 1997.
Now, adding a YYMMDDD mode (not to be confused with YYYYMMDD) to
datestyle is a feature addition to me. I am not sure if anyone wants
it, though.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Fri, Jul 25, 2003 at 04:13:12PM -0500, Ron Johnson wrote:
On Fri, 2003-07-25 at 15:02, Bruce Momjian wrote:
Does anyone use YY-MM-DD for date input?
Right now, it only works mostly for pre-2000 dates because we can detect
that 97-02-03 is a year, while we can not detect that in 03-02-01.We are considering eliminating it for 7.4. You can still use
yyyy-mm-dd, or course.Comments?
What about the sliding window approach that some used to "solve"
the y2k problem: any year between, say '00' and '32' is presumed
to be in the 21st century, but years between '33' and '99' are
20th century.However, dropping it and letting the app deal with it is my non-
counting vote...
Mine too - as time goes by, we would also want to move the window
32][33 above etc, so simplest is if 03-02-01 would just be 3rd Feb
1AD, 2nd Mar 1AD or 1st Feb 3AD according to DMY, MDY or YMD. Still
not clear to me how you make a difference between input and output
encoding with a single DateStyle GUC variable.. DMY MDY and YMD
just for input, US and European just for output?
Cheers,
Patrick
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
As much as I would love to stay involved in this topic, I am leaving
for vacation tomorrow and my email will be erratic at best. Looking
forward to testing out 7.4 when I get back! :)
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200307252031
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html
iD8DBQE/IphpvJuQZxSWSsgRAmRMAKCQT3Rrrm4PBR4UzogS7lwSdYpN4gCg5OcO
SECdrdbcAUOHoYq8/RkHlbg=
=3Fw6
-----END PGP SIGNATURE-----
Patrick Welche wrote:
On Fri, Jul 25, 2003 at 04:13:12PM -0500, Ron Johnson wrote:
On Fri, 2003-07-25 at 15:02, Bruce Momjian wrote:
Does anyone use YY-MM-DD for date input?
Right now, it only works mostly for pre-2000 dates because we can detect
that 97-02-03 is a year, while we can not detect that in 03-02-01.We are considering eliminating it for 7.4. You can still use
yyyy-mm-dd, or course.Comments?
What about the sliding window approach that some used to "solve"
the y2k problem: any year between, say '00' and '32' is presumed
to be in the 21st century, but years between '33' and '99' are
20th century.However, dropping it and letting the app deal with it is my non-
counting vote...Mine too - as time goes by, we would also want to move the window
32][33 above etc, so simplest is if 03-02-01 would just be 3rd Feb
1AD, 2nd Mar 1AD or 1st Feb 3AD according to DMY, MDY or YMD. Still
not clear to me how you make a difference between input and output
encoding with a single DateStyle GUC variable.. DMY MDY and YMD
just for input, US and European just for output?
We really can't do that because too many people are using a trailing YY
to mean current century, or most recent year with that suffix. For
leading years, you have to specify it as 0001, rather than just 01.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Does anyone use YY-MM-DD for date input?
Not from the postgresql context, but I've seen yy.mm.dd in a lot of
contexts lately. I had figured, with the Japanese eras, that the average
Japanese user of western dates would avoid things like 03.07.28, because
it would leave some question as to whether the date was today or twelve
years ago today. Perhaps some people got into that habit during the '80s
and '90s and haven't realized the implications.
Since I've already given my opinion that heuristics are better left to
the UI layer, and, if provided, should be off by default, I won't
mention it again. 8-)
How far off-the-wall would customizable heuristics be? In Japan, the
order is going to be yy.mm.dd except when a stupid foreigner (like me)
is entering data. However, it might be convenient to be able to set
rules something like the following:
FOUR_DIGIT_YEAR_IS_COMMON_ERA
TWO_DIGIT_YEAR_IS_CURRENT_LOCAL_ERA
TWO_DIGIT_YEAR_WITHIN_RANGE_IS_COMMON_ERA
TWO_DIGIT_COMMON_YEAR_RANGE_LIMIT +nn -nn
(It's not something I'd necessarily condone, BTW.)
--
Joel Rees, programmer, Kansai Systems Group
Altech Corporation (Alpsgiken), Osaka, Japan
http://www.alpsgiken.co.jp
At this moment, I'm getting a cute "this is the apache testpage"-page
when I'm trying to access www.postgresql.org , the same holds for the
developer.postgresql.org.
If there is planned work in progress, perhaps you'd set up some sort of
"work in progress, come back later"-page, that's more explaining than
the apache default page ;)
www.postgresql.org resolves to svr2.postgresql.org (64.117.225.209) for
me and I'm a dutch resident (in case there is country-based
loadbalancing in place).
Regards,
Arjen van der Meijden
