Thread: postgres and year 2000
Hi, it seems that the year handling in pgsql dates is not very consistent: dz=> create table a (n int, x date); CREATE dz=> insert into a values (1,'02-02-0'); INSERT 259246 1 dz=> insert into a values (2,'02-02-00'); INSERT 259247 1 dz=> insert into a values (3,'02-02-000'); INSERT 259248 1 dz=> insert into a values (4,'02-02-0000'); INSERT 259249 1 dz=> insert into a values (5,'02-02-00000'); INSERT 259250 1 dz=> insert into a values (6,'02-02-1'); INSERT 259251 1 dz=> insert into a values (7,'02-02-01'); INSERT 259252 1 dz=> insert into a values (8,'02-02-001'); INSERT 259253 1 dz=> insert into a values (9,'02-02-0001'); INSERT 259254 1 dz=> insert into a values (10,'02-02-00001'); INSERT 259255 1 dz=> insert into a values (11,'02-02-69'); INSERT 259256 1 dz=> insert into a values (12,'02-02-069'); INSERT 259257 1 dz=> insert into a values (13,'02-02-0069'); INSERT 259258 1 dz=> insert into a values (14,'02-02-00069'); INSERT 259259 1 dz=> insert into a values (15,'02-02-71'); INSERT 259260 1 dz=> insert into a values (16,'02-02-071'); INSERT 259261 1 dz=> insert into a values (17,'02-02-0071'); INSERT 259262 1 dz=> insert into a values (18,'02-02-00071'); INSERT 259263 1 dz=> select * from a;n|x --+-------------1|02-02-2000 2|02-02-2000 3|02-02-2000 4|02-02-0001 BC5|02-02-2000 6|02-02-2001 7|02-02-2001 8|02-02-2001 9|02-02-0001 10|02-02-2001 11|02-02-2069 12|02-02-2069 13|02-02-0069 14|02-02-2069 15|02-02-1971 16|02-02-1971 17|02-02-0071 18|02-02-1971 The problem I see is that the same number is converted to a different year depending on the number of digits and the number itself. I think that this kind of things are the most likely sources of Y2K troubles. A more consistent approach would be to treat the year literally and let any smart hack with dates entirely to the user under his responsability. Only then we could declare pgsql as full Y2K compliant. -- Massimo Dal Zotto +----------------------------------------------------------------------+ | Massimo Dal Zotto email: dz@cs.unitn.it | | Via Marconi, 141 phone: ++39-0461534251 | | 38057 Pergine Valsugana (TN) www: http://www.cs.unitn.it/~dz/ | | Italy pgp: finger dz@tango.cs.unitn.it | +----------------------------------------------------------------------+
> it seems that the year handling in pgsql dates is not very consistent:
> The problem I see is that the same number is converted to a different
> year depending on the number of digits and the number itself. I think
> that this kind of things are the most likely sources of Y2K troubles.
> A more consistent approach would be to treat the year literally and
> let any smart hack with dates entirely to the user under his
> responsability.
I agree that there are some cases in your examples which should be
giving a different result. Not *every* example you gave led to an
incorrect or unpredictable result, so could you please identify those
you feel are a problem? In glancing at the examples, the ones with zero
value (but lots of zeros) seem to be a problem, and perhaps some or all
of the ones with an odd number of year digits. Any others?
We do need to handle two-digit years, and we currently do so using 1970
as the break point. I've read recently that some industries are using a
"50/50 rule" for 2 digit conversions, where 1950 is the break point.
Don't know if we should try to use that (rather than the "Unix rule" :),
since it really doesn't offer a magic cure for all date possibilities.
> Only then we could declare pgsql as full Y2K compliant.
fwiw, the date candidates which are failing are outside the range of
normal usage or could be considered mal-formed. But I should be able to
get a fix in for it, and can post patches. Let me know what cases you
would like tested and fixed (but let's not bog down in discussion on
two-digit issues).
- Tom
> it seems that the year handling in pgsql dates is not very consistent:
> the same number is converted to a different year
> depending on the number of digits and the number itself.
> I think that this kind of things are the most likely sources of Y2K
> troubles. A more consistent approach would be to treat the year
> literally and let any smart hack with dates entirely to the user under
> his responsability.
OK, here is a patch which tightens up the date/time interpretation of
random input :)
For numeric fields, anything greater than two digits in length is
required to be either a "concatenated date/time" or an explicit date.
Also, now *only* two digit years are converted to a year in a nearby
century.
> Only then we could declare pgsql as full Y2K compliant.
I agree that the interpretation of 3 and 5 digit years was not right for
years with leading zeros. This patch should fix that.
"Y2K compliant" means to me that the behavior of well-formed dates must
be predictable and understandable. Dates *intended* to be in the 19th or
20th century should end up being interpreted that way.
The Postgres date/time types allow year ranges from (roughly) Julian Day
zero, which is Nov 23, 4714 BC (I wrote it as -4713 in my notes, so I
may be off by a year), to far into the future. So if you enter a three
digit year, that's what you are going to get (or will, with this patch).
I don't see that as causing Y2K trouble; you could just as easily call
it Y1K trouble :)
Anyway, there is a tradeoff between flexibility in date specification
and catching unintended typos. Dan suggested that all dates be required
to have 4 digit years, which may be overly harsh for some users and
developers. Should we have a way to specify the range of valid dates and
times so databases installed for specific applications can have more
restrictive constraints on input? I already parameterized the year range
check with a #define. Perhaps we should have it be able to be overridden
by ./configure or by a Makefile.custom??
A patch is enclosed which fixes all of the problems I can see in your
examples. It basically does no century adjustments unless the year is
exactly two digits, and interprets anything greater than two digits as
an explicit year (or a concatenated date if 8 or 6 or 5 digits). It adds
"two digit year" adjustments to concatentated dates, which I had
apparently omitted. It also does more rigorous checking on the usage of
"BC", so that if you specify a negative or zero year along with BC it
will complain (before, it just flipped the sign on the year).
Please install and let me know what you think:
cd src/backend/utils/adt
patch < dt.c.patch
cd ../../..
make install
I will apply this to the development cvs tree sometime soon, unless
folks find problems or need something different.
Massimo, would you be interested in adding some of your test cases to
the datetime or horology regression test? Send me patches and I'll add
them in...
- Tom*** dt.c.orig Mon Jan 4 17:43:25 1999
--- dt.c Sat Jan 9 18:24:29 1999
***************
*** 2839,2850 ****
case DTK_NUMBER:
flen = strlen(field[i]);
! if (flen > 4)
{
if (DecodeNumberField(flen, field[i], fmask, &tmask, tm, fsec) != 0)
return -1;
}
else
{
if (DecodeNumber(flen, field[i], fmask, &tmask, tm, fsec) != 0)
--- 2839,2853 ----
case DTK_NUMBER:
flen = strlen(field[i]);
! /* long numeric string and either no date or no time read yet?
! * then interpret as a concatenated date or time... */
! if ((flen > 4) && !((fmask & DTK_DATE_M) && (fmask & DTK_TIME_M)))
{
if (DecodeNumberField(flen, field[i], fmask, &tmask, tm, fsec) != 0)
return -1;
}
+ /* otherwise it is a single date/time field... */
else
{
if (DecodeNumber(flen, field[i], fmask, &tmask, tm, fsec) != 0)
***************
*** 3000,3006 ****
/* there is no year zero in AD/BC notation; i.e. "1 BC" == year 0 */
if (bc)
! tm->tm_year = -(tm->tm_year - 1);
if ((mer != HR24) && (tm->tm_hour > 12))
return -1;
--- 3003,3014 ----
/* there is no year zero in AD/BC notation; i.e. "1 BC" == year 0 */
if (bc)
! {
! if (tm->tm_year > 0)
! tm->tm_year = -(tm->tm_year - 1);
! else
! elog(ERROR,"Inconsistant use of year %04d and 'BC'", tm->tm_year);
! }
if ((mer != HR24) && (tm->tm_hour > 12))
return -1;
***************
*** 3375,3382 ****
printf("DecodeNumber- %s is %d fmask=%08x tmask=%08x\n", str, val, fmask, *tmask);
#endif
! /* enough digits to be unequivocal year? */
! if (flen == 4)
{
#ifdef DATEDEBUG
printf("DecodeNumber- match %d (%s) as year\n", val, str);
--- 3383,3405 ----
printf("DecodeNumber- %s is %d fmask=%08x tmask=%08x\n", str, val, fmask, *tmask);
#endif
! /* Special case day of year? */
! if ((flen == 3) && (fmask & DTK_M(YEAR))
! && ((val >= 1) && (val <= 366)))
! {
! *tmask = (DTK_M(DOY) | DTK_M(MONTH) | DTK_M(DAY));
! tm->tm_yday = val;
! j2date((date2j(tm->tm_year, 1, 1) + tm->tm_yday - 1),
! &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
!
! }
! /* Enough digits to be unequivocal year?
! * Used to test for 4 digits or more,
! * but we now test first for a three-digit doy
! * so anything bigger than two digits had better be
! * an explicit year. - thomas 1999-01-09
! */
! else if (flen > 2)
{
#ifdef DATEDEBUG
printf("DecodeNumber- match %d (%s) as year\n", val, str);
***************
*** 3399,3416 ****
tm->tm_year = val;
- /* special case day of year? */
- }
- else if ((flen == 3) && (fmask & DTK_M(YEAR))
- && ((val >= 1) && (val <= 366)))
- {
- *tmask = (DTK_M(DOY) | DTK_M(MONTH) | DTK_M(DAY));
- tm->tm_yday = val;
- j2date((date2j(tm->tm_year, 1, 1) + tm->tm_yday - 1),
- &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
-
- /* already have year? then could be month */
}
else if ((fmask & DTK_M(YEAR)) && (!(fmask & DTK_M(MONTH)))
&& ((val >= 1) && (val <= 12)))
{
--- 3422,3429 ----
tm->tm_year = val;
}
+ /* already have year? then could be month */
else if ((fmask & DTK_M(YEAR)) && (!(fmask & DTK_M(MONTH)))
&& ((val >= 1) && (val <= 12)))
{
***************
*** 3460,3469 ****
#endif
*tmask = DTK_M(YEAR);
tm->tm_year = val;
! if (tm->tm_year < 70)
! tm->tm_year += 2000;
! else if (tm->tm_year < 100)
! tm->tm_year += 1900;
}
else
--- 3473,3487 ----
#endif
*tmask = DTK_M(YEAR);
tm->tm_year = val;
!
! /* adjust ONLY if exactly two digits... */
! if (flen == 2)
! {
! if (tm->tm_year < 70)
! tm->tm_year += 2000;
! else if (tm->tm_year < 100)
! tm->tm_year += 1900;
! }
}
else
***************
*** 3527,3534 ****
--- 3545,3573 ----
tm->tm_mon = atoi(str + 2);
*(str + 2) = '\0';
tm->tm_year = atoi(str + 0);
+
+ if (tm->tm_year < 70)
+ tm->tm_year += 2000;
+ else if (tm->tm_year < 100)
+ tm->tm_year += 1900;
}
+ }
+ else if ((len == 5) && !(fmask & DTK_DATE_M))
+ {
+ #ifdef DATEDEBUG
+ printf("DecodeNumberField- %s is 5 characters fmask=%08x tmask=%08x\n", str, fmask, *tmask);
+ #endif
+ *tmask = DTK_DATE_M;
+ tm->tm_mday = atoi(str + 2);
+ *(str + 2) = '\0';
+ tm->tm_mon = 1;
+ tm->tm_year = atoi(str + 0);
+
+ if (tm->tm_year < 70)
+ tm->tm_year += 2000;
+ else if (tm->tm_year < 100)
+ tm->tm_year += 1900;
}
else if (strchr(str, '.') != NULL)
{
> it seems that the year handling in pgsql dates is not very consistent:
> The problem I see is that the same number is converted to a different
> year depending on the number of digits and the number itself.
I've posted a patch intended for v6.4 and v6.4.2 at ftp://postgresql.org/pub/patches/dt.c.patch
which addresses some date interpretation problems, mostly for dates
containing leading zeros and/or an odd number of digits (I'm referring
to this as the "Y1K problem" :). It also fixes problems with two-digit
years for "concatenated dates" like "990110" and adds a "yydoy" 5 digit
concatenated date, like "99010".
I'll claim that none of these problems or fixes were related directly to
a "Y2K" problem, since it didn't work in "Y1900" either :/
cvs commit notes are included below. Let me know if there are any
problems.
- Tom
Be more careful to check input string lengths as well as valueswhen deciding whether a field is a year field. Assume
*anything*longerthan 2 digits (if it isn't a special-case doy) is a valid year.This should fix the "Y1K" and "Y10K"
problemspointed out by Massimo recently.
Check usage of BC to require a positive-valued year; before just usedto flip the sign of the year without checking.
Thisled to problemsnear year zero.
Allow a 5 digit "concatenated date" of 2 digit year plus day of year.
Do 2->4 digit year correction for 6 and 5 digit "concatenated dates".Somehow forgot this originally. Guess not many
folksuse it...
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes: > We do need to handle two-digit years, [...] Is it at all possible to get away with _not_ doing so? It is, after all, incredibly stupid to use two-digit years in anything but spoken conversation, so in a way, I'd prefer computer systems to blankly refuse them. If they're allowed at all, I'd say parse them so that a year specification of '99' means the actual year 99. _Not_ 1999. Then again, I also think computer systems should refuse to accept non-ISO8601 date specifications, so I may be a bit too pedantic. :-) -tih -- Popularity is the hallmark of mediocrity. --Niles Crane, "Frasier"
On 11 Jan 1999, Tom Ivar Helbekkmo wrote:
> "Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes:
>
> > We do need to handle two-digit years, [...]
>
> Is it at all possible to get away with _not_ doing so? It is, after
> all, incredibly stupid to use two-digit years in anything but spoken
> conversation, so in a way, I'd prefer computer systems to blankly
> refuse them. If they're allowed at all, I'd say parse them so that a
> year specification of '99' means the actual year 99. _Not_ 1999.
Falling back to a Unix standard...type 'cal 99' and see which year you
get :)
I agree with Tom on this...if someone types a year of 99, we should
presume that whomever entered it knew what they were entering, and/or that
the programmer of the interface had enough sense to program checks into
it...
Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
> > "Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes:
> > > We do need to handle two-digit years, [...]
> > Is it at all possible to get away with _not_ doing so? It is, after
> > all, incredibly stupid to use two-digit years in anything but spoken
> > conversation, so in a way, I'd prefer computer systems to blankly
> > refuse them. If they're allowed at all, I'd say parse them so that
> > a year specification of '99' means the actual year 99. _Not_ 1999.
> Falling back to a Unix standard...type 'cal 99' and see which year you
> get :)
> I agree with Tom on this...if someone types a year of 99, we should
> presume that whomever entered it knew what they were entering, and/or
> that the programmer of the interface had enough sense to program
> checks into it...
Well, we're all Toms here, but I'll assume you are trying to agree with
Tom H.
You may think that ISO-8601 is the way to go, and I may think that
ISO-8601 is the way to go, but it isn't yet a universal usage, so why
should we presume that it is? That timezone stuff is pretty annoying and
confusing too, so perhaps we should revert Postgres back to its pre-v3
GMT-only date support? :)
If we don't accept a reasonably wide range of common date and time
specifications, then each app will have to, or may have to, do that.
istm that it is *much* easier to write an app which restricts data entry
to a particular style, which is then forwarded to our backend, than to
accept *only* a particular style in the backend and expect every client
app, in every supported language, on every platform, to learn how to
accept general date values and then convert them to a single specific
one.
I should also point out that in a recent review of our database the
date/time I/O was mentioned (with 2 or 3 other features) as being
particularly noteworthy. So *someone* thought it was a nice feature :)
I suppose we could consider a compile-time or run-time option to
constrain dates to a single style. Seems a bit of overkill, and
personally I'd like to work on outer joins, but...
- Tom
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes:
> If we don't accept a reasonably wide range of common date and time
> specifications, then each app will have to, or may have to, do that.
Just to throw another Tom's opinion into the mix ;-) ...
I agree with Tom Lockhart on this one. If we don't provide date
interpretation in the backend, that doesn't make the problem go away.
It just means that every frontend application has to re-invent that
same wheel. And, no doubt, cope with bugs in their re-invention.
Getting it right *once* is the whole idea of re-using software --- else
why not expect everyone to write their own whole DBMS?
A frontend programmer who has his own strong ideas about how to
interpret dates is certainly free to do so, and then to pass his results
to the backend in some unambiguous format like ISO. But not many people
will really want to do that --- they'd much rather have a flexible and
robust solution provided for them.
Date handling is inherently messy because there are so many different
conventions. But we can't make that go away by decree. Guess what:
people will keep writing two-digit years, even after the turn of the
century, and will expect their computers to understand what's meant.
> I suppose we could consider a compile-time or run-time option to
> constrain dates to a single style.
I see no need to do that. A particular frontend programmer who wants
that behavior can make it happen himself --- should you break other
apps that may be talking to the same database server in order to do
it for him?
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > I agree with Tom Lockhart on this one. So do I, actually. However: > > I suppose we could consider a compile-time or run-time option to > > constrain dates to a single style. > > I see no need to do that. Not compile-time, no. But I think it would be a good thing to have several run-time options (of which PostgreSQL already has a few), to specify exactly which behavior is wanted. For two digit years, it might be useful to be able to specify to the backend that they should be handled as, say, 1920-2019, or as the chronologically nearest year that ends in the two given digits, or maybe even as being in the current century. When using a four digit year mode, though, I think it's a good idea to handle '99' as the year 99, and not e.g. 1999. It may be that even this should be an option, and the dangerous mixture, where there are two years between between the starts of year '99' and year '2001', should be available on front-end application request. I would suggest that the defaults be safe, though, probably ISO 8601. -tih -- Popularity is the hallmark of mediocrity. --Niles Crane, "Frasier"
> > "Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes: > > If we don't accept a reasonably wide range of common date and time > > specifications, then each app will have to, or may have to, do that. > > Just to throw another Tom's opinion into the mix ;-) ... > > I agree with Tom Lockhart on this one. If we don't provide date > interpretation in the backend, that doesn't make the problem go away. > It just means that every frontend application has to re-invent that > same wheel. And, no doubt, cope with bugs in their re-invention. > Getting it right *once* is the whole idea of re-using software --- else > why not expect everyone to write their own whole DBMS? > > A frontend programmer who has his own strong ideas about how to > interpret dates is certainly free to do so, and then to pass his results > to the backend in some unambiguous format like ISO. But not many people > will really want to do that --- they'd much rather have a flexible and > robust solution provided for them. > > Date handling is inherently messy because there are so many different > conventions. But we can't make that go away by decree. Guess what: > people will keep writing two-digit years, even after the turn of the > century, and will expect their computers to understand what's meant. > > > I suppose we could consider a compile-time or run-time option to > > constrain dates to a single style. > > I see no need to do that. A particular frontend programmer who wants > that behavior can make it happen himself --- should you break other > apps that may be talking to the same database server in order to do > it for him? > > regards, tom lane It is nice to provide smart date interpretation in the backend but in order to be really Y2K compliant we *MUST* forbid any ambiguous date format in the backend. If the user insists in wanting two-digits years in his interface he will write his own not-Y2K-compliant conversion code. Someone mentioned the ISO-8601 standard. Could you post a summary of this standard ? -- Massimo Dal Zotto +----------------------------------------------------------------------+ | Massimo Dal Zotto email: dz@cs.unitn.it | | Via Marconi, 141 phone: ++39-0461534251 | | 38057 Pergine Valsugana (TN) www: http://www.cs.unitn.it/~dz/ | | Italy pgp: finger dz@tango.cs.unitn.it | +----------------------------------------------------------------------+
Massimo Dal Zotto <dz@cs.unitn.it> writes: > Someone mentioned the ISO-8601 standard. Could you post a summary of > this standard ? Very quick summary: Today is '1998-01-13', and I'm posting this at approximately '1998-01-13 13:02:00Z' (the Z is for Zulu, which is the military designation for UT), or '1998-01-13 14:02:00' my local time. See http://www.iso.ch/markete/moreend.htm for more information, and a downloadable PDF version of the complete standard. -tih -- Popularity is the hallmark of mediocrity. --Niles Crane, "Frasier"
Massimo Dal Zotto <dz@cs.unitn.it> writes:
> It is nice to provide smart date interpretation in the backend but in
> order to be really Y2K compliant we *MUST* forbid any ambiguous date
> format in the backend.
Why? Has some bureaucrat somewhere defined "Y2K-compliant" as meaning
"thou shalt stop accepting 2-digit years"? I have not heard of any
such definition and I am entirely prepared to ignore it if it exists...
it has nothing to do with reality.
regards, tom lane
Tom Ivar Helbekkmo <tih@nhh.no> writes:
> Not compile-time, no. But I think it would be a good thing to have
> several run-time options (of which PostgreSQL already has a few), to
> specify exactly which behavior is wanted. For two digit years, it
> might be useful to be able to specify to the backend that they should
> be handled as, say, 1920-2019, or as the chronologically nearest year
> that ends in the two given digits, or maybe even as being in the
> current century. When using a four digit year mode, though, I think
> it's a good idea to handle '99' as the year 99, and not e.g. 1999.
IIRC, we already have both behaviors (99->1999AD and 99->99AD)
available, but it's controlled by a combination of the DATESTYLE setting
and the actual formatting of the particular input string.
There doesn't seem to be anything in the documentation about exactly
how ambiguous inputs are parsed. Thomas, maybe some text needs to
be added?
regards, tom lane
> It is nice to provide smart date interpretation in the backend but in
> order to be really Y2K compliant we *MUST* forbid any ambiguous date
> format in the backend. If the user insists in wanting two-digits years
> in his interface he will write his own not-Y2K-compliant conversion
> code.
I've never heard that interpretation of Y2K compliance. Remember that
once a date has been interpreted, it is *never* ambiguous since it is
stored as a full value. The rules for interpretation are not ambiguous,
and follow common usage.
The cases you found which did not "do the right thing" allowed me to fix
bugs, but imho did not illustrate fundamental problems in dealing with
date/time. The *only* bothersome case really is that for a Roman from
the year 88 sitting down in front of Postgres, entering dates, and upon
seeing "1988" in a result saying: "What the ...?".
Saaayyyyy, you're not one of those research types bringing old bodies
back to life are you? I've seen movies about that... ;)
But back to the Roman, the modern calendar wasn't adopted until the 17th
or 18th century, so he'd be confused anyway.
Regards.
- Tom ;)
> There doesn't seem to be anything in the documentation about exactly
> how ambiguous inputs are parsed. Thomas, maybe some text needs to
> be added?
I'll bet I won't get away with "see the file dt.c for a complete
description of date/time parsing". Darn. Will try writing something
up...
- Tom
The FAQ_CVS does not discuss how to retrieve and follow particular branches. Could someone give me a quick lesson, (or better yet, update the FAQ?) I need to be able to fetch and build the current production release. Right now I'm fetching and building 6.5 :) Clark
> The FAQ_CVS does not discuss how to retrieve
> and follow particular branches. Could someone
> give me a quick lesson, (or better yet, update the FAQ?)
Don't put any new work into the FAQ! I've merged it into cvs.sgml, but
have not finished marking up cvs.sgml (there was some other stuff to
merge in, and...).
> I need to be able to fetch and build the current
> production release. Right now I'm fetching
> and building 6.5
Look at doc/src/sgml/cvs.sgml, which aside from the tags has some words
(taken from one of scrappy's e-mails on the subject, maybe not on one of
main lists) on how to retrieve tagged versions.
- Tom
> > Not compile-time, no. But I think it would be a good thing to have > > several run-time options (of which PostgreSQL already has a few), to > > specify exactly which behavior is wanted. For two digit years, it > > might be useful to be able to specify to the backend that they should > > be handled as, say, 1920-2019, or as the chronologically nearest year > > that ends in the two given digits, or maybe even as being in the > > current century. When using a four digit year mode, though, I think > > it's a good idea to handle '99' as the year 99, and not e.g. 1999. > > IIRC, we already have both behaviors (99->1999AD and 99->99AD) > available, but it's controlled by a combination of the DATESTYLE setting > and the actual formatting of the particular input string. Why not just try to parse the date according to the DATESTYLE setting and cough up an error if the date-parsing code doesn't find what it is looking for? I believe Oracle does this, but also has the to_date(string, format) function to tell the backend just what format it is getting. The function also exists without the format arg in which case it will use the Oracle default. It seems to me that either ... Postgres needs a to_date function to be told what format to use instead of being expected to blindly guess what the user is giving it. Then overload the function s.t. calling it without the format will use the current DATESTYLE. or Postgres needs a way to set the DATESTYLE to the actual format string to be used to parse the input for a date field instead of being used to indicate a "style". This then eliminates the need for the to_date function. The latter would seem to be the easiest solution and allow the user to enter many different date styles per connection simply by issing a "SET DATESTYLE = 'abc-xyz'". Thomas, how hard would it be to parse an arg to "SET DATESTYLE" and use it to parse dates? Just my thoughts... darrenk
> Why not just try to parse the date according to the DATESTYLE setting
> and cough up an error if the date-parsing code doesn't find what it
> is looking for?
Postgres does use the DateStyle setting to resolve input ambiguities.
> I believe Oracle does this, but also has the to_date(string, format)
> function to tell the backend just what format it is getting. The
> function also exists without the format arg in which case it will
> use the Oracle default.
> It seems to me that either ...
> Postgres needs a to_date function to be told what format to use
> instead of being expected to blindly guess what the user is giving
> it. Then overload the function s.t. calling it without the format
> will use the current DATESTYLE.
> or
> Postgres needs a way to set the DATESTYLE to the actual format
> string to be used to parse the input for a date field instead of
> being used to indicate a "style". This then eliminates the need
> for the to_date function.
So what problem are we trying to solve again? Stripping out
functionality should buy us something useful...
> Thomas, how hard would it be to parse an arg to "SET DATESTYLE"
> and use it to parse dates?
Not sure. It would just be a different set of parsing code.
- Tom