Re: oh dear ... - Mailing list pgsql-hackers
| From | Tom Lane |
|---|---|
| Subject | Re: oh dear ... |
| Date | |
| Msg-id | 14988.1068917446@sss.pgh.pa.us Whole thread Raw |
| In response to | Re: oh dear ... (Joe Conway <mail@joeconway.com>) |
| Responses |
Re: oh dear ...
(Christopher Kings-Lynne <chriskl@familyhealth.com.au>)
|
| List | pgsql-hackers |
Joe Conway <mail@joeconway.com> writes:
> Tom Lane wrote:
>> The pgstat patch has already been checked to my satisfaction, but the
>> datetime patch needs more eyeballs on it; anyone out there have time to
>> look at it?
> FWIW, it looks good to me, seems to work as intended, and passes all
> existing regression tests.
I made up a more thorough regression test for date input formats, and
found that there were still some cases that were rejected :-(. Attached
is a more complete patch that handles all month-name cases, and
explicitly can not change the behavior when there's not a textual month
name. Documentation addition and regression test included.
I'd like some further review of this before I risk applying it to 7.4
though ... anyone have time today?
regards, tom lane
*** doc/src/sgml/datatype.sgml.orig Thu Nov 6 17:21:47 2003
--- doc/src/sgml/datatype.sgml Sat Nov 15 11:53:47 2003
***************
*** 1464,1470 ****
</row>
<row>
<entry>1999-01-08</entry>
! <entry>ISO 8601, January 8 in any mode
(recommended format)</entry>
</row>
<row>
--- 1464,1470 ----
</row>
<row>
<entry>1999-01-08</entry>
! <entry>ISO 8601; January 8 in any mode
(recommended format)</entry>
</row>
<row>
***************
*** 1483,1488 ****
--- 1483,1512 ----
February 1, 2003 in <literal>DMY</> mode;
February 3, 2001 in <literal>YMD</> mode
</entry>
+ </row>
+ <row>
+ <entry>1999-Jan-08</entry>
+ <entry>January 8 in any mode</entry>
+ </row>
+ <row>
+ <entry>Jan-08-1999</entry>
+ <entry>January 8 in any mode</entry>
+ </row>
+ <row>
+ <entry>08-Jan-1999</entry>
+ <entry>January 8 in any mode</entry>
+ </row>
+ <row>
+ <entry>99-Jan-08</entry>
+ <entry>January 8 in <literal>YMD</> mode, else error</entry>
+ </row>
+ <row>
+ <entry>08-Jan-99</entry>
+ <entry>January 8, except error in <literal>YMD</> mode</entry>
+ </row>
+ <row>
+ <entry>Jan-08-99</entry>
+ <entry>January 8, except error in <literal>YMD</> mode</entry>
</row>
<row>
<entry>19990108</entry>
*** src/backend/utils/adt/datetime.c.orig Thu Sep 25 10:23:13 2003
--- src/backend/utils/adt/datetime.c Sat Nov 15 11:33:50 2003
***************
*** 25,31 ****
#include "utils/guc.h"
! static int DecodeNumber(int flen, char *field,
int fmask, int *tmask,
struct tm * tm, fsec_t *fsec, int *is2digits);
static int DecodeNumberField(int len, char *str,
--- 25,31 ----
#include "utils/guc.h"
! static int DecodeNumber(int flen, char *field, bool haveTextMonth,
int fmask, int *tmask,
struct tm * tm, fsec_t *fsec, int *is2digits);
static int DecodeNumberField(int len, char *str,
***************
*** 924,930 ****
int val;
int dterr;
int mer = HR24;
! int haveTextMonth = FALSE;
int is2digits = FALSE;
int bc = FALSE;
--- 924,930 ----
int val;
int dterr;
int mer = HR24;
! bool haveTextMonth = FALSE;
int is2digits = FALSE;
int bc = FALSE;
***************
*** 1281,1287 ****
/* otherwise it is a single date/time field... */
else
{
! dterr = DecodeNumber(flen, field[i], fmask,
&tmask, tm,
fsec, &is2digits);
if (dterr)
--- 1281,1288 ----
/* otherwise it is a single date/time field... */
else
{
! dterr = DecodeNumber(flen, field[i],
! haveTextMonth, fmask,
&tmask, tm,
fsec, &is2digits);
if (dterr)
***************
*** 2032,2037 ****
--- 2033,2039 ----
else
{
dterr = DecodeNumber(flen, field[i],
+ FALSE,
(fmask | DTK_DATE_M),
&tmask, tm,
fsec, &is2digits);
***************
*** 2229,2234 ****
--- 2231,2237 ----
int i,
len;
int dterr;
+ bool haveTextMonth = FALSE;
int bc = FALSE;
int is2digits = FALSE;
int type,
***************
*** 2283,2288 ****
--- 2286,2292 ----
{
case MONTH:
tm->tm_mon = val;
+ haveTextMonth = TRUE;
break;
case ADBC:
***************
*** 2312,2318 ****
if ((len = strlen(field[i])) <= 0)
return DTERR_BAD_FORMAT;
! dterr = DecodeNumber(len, field[i], fmask,
&dmask, tm,
&fsec, &is2digits);
if (dterr)
--- 2316,2322 ----
if ((len = strlen(field[i])) <= 0)
return DTERR_BAD_FORMAT;
! dterr = DecodeNumber(len, field[i], haveTextMonth, fmask,
&dmask, tm,
&fsec, &is2digits);
if (dterr)
***************
*** 2444,2450 ****
* Return 0 if okay, a DTERR code if not.
*/
static int
! DecodeNumber(int flen, char *str, int fmask,
int *tmask, struct tm * tm, fsec_t *fsec, int *is2digits)
{
int val;
--- 2448,2454 ----
* Return 0 if okay, a DTERR code if not.
*/
static int
! DecodeNumber(int flen, char *str, bool haveTextMonth, int fmask,
int *tmask, struct tm * tm, fsec_t *fsec, int *is2digits)
{
int val;
***************
*** 2534,2543 ****
tm->tm_mon = val;
break;
case (DTK_M(YEAR) | DTK_M(MONTH)):
! /* Must be at third field of YY-MM-DD */
! *tmask = DTK_M(DAY);
! tm->tm_mday = val;
break;
case (DTK_M(DAY)):
--- 2538,2596 ----
tm->tm_mon = val;
break;
+ case (DTK_M(MONTH)):
+ if (haveTextMonth)
+ {
+ /*
+ * We are at the first numeric field of a date that included
+ * a textual month name. We want to support the variants
+ * MON-DD-YYYY, DD-MON-YYYY, and YYYY-MON-DD as unambiguous
+ * inputs. We will also accept MON-DD-YY or DD-MON-YY in
+ * either DMY or MDY modes, as well as YY-MON-DD in YMD mode.
+ */
+ if (flen >= 3 || DateOrder == DATEORDER_YMD)
+ {
+ *tmask = DTK_M(YEAR);
+ tm->tm_year = val;
+ }
+ else
+ {
+ *tmask = DTK_M(DAY);
+ tm->tm_mday = val;
+ }
+ }
+ else
+ {
+ /* Must be at second field of MM-DD-YY */
+ *tmask = DTK_M(DAY);
+ tm->tm_mday = val;
+ }
+ break;
+
case (DTK_M(YEAR) | DTK_M(MONTH)):
! if (haveTextMonth)
! {
! /* Need to accept DD-MON-YYYY even in YMD mode */
! if (flen >= 3 && *is2digits)
! {
! /* Guess that first numeric field is day was wrong */
! *tmask = DTK_M(DAY); /* YEAR is already set */
! tm->tm_mday = tm->tm_year;
! tm->tm_year = val;
! *is2digits = FALSE;
! }
! else
! {
! *tmask = DTK_M(DAY);
! tm->tm_mday = val;
! }
! }
! else
! {
! /* Must be at third field of YY-MM-DD */
! *tmask = DTK_M(DAY);
! tm->tm_mday = val;
! }
break;
case (DTK_M(DAY)):
***************
*** 2552,2563 ****
tm->tm_year = val;
break;
- case (DTK_M(MONTH)):
- /* Must be at second field of MM-DD-YY */
- *tmask = DTK_M(DAY);
- tm->tm_mday = val;
- break;
-
case (DTK_M(YEAR) | DTK_M(MONTH) | DTK_M(DAY)):
/* we have all the date, so it must be a time field */
dterr = DecodeNumberField(flen, str, fmask,
--- 2605,2610 ----
***************
*** 2574,2583 ****
/*
* When processing a year field, mark it for adjustment if it's
! * exactly two digits.
*/
if (*tmask == DTK_M(YEAR))
! *is2digits = (flen == 2);
return 0;
}
--- 2621,2630 ----
/*
* When processing a year field, mark it for adjustment if it's
! * only one or two digits.
*/
if (*tmask == DTK_M(YEAR))
! *is2digits = (flen <= 2);
return 0;
}
*** src/test/regress/expected/date.out.orig Wed Aug 27 19:13:42 2003
--- src/test/regress/expected/date.out Sat Nov 15 11:59:09 2003
***************
*** 63,68 ****
--- 63,745 ----
(3 rows)
--
+ -- Check all the documented input formats
+ --
+ SET datestyle TO iso; -- display results in ISO
+ SET datestyle TO ymd;
+ SELECT date 'January 8, 1999';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '1999-01-08';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '1999-01-18';
+ date
+ ------------
+ 1999-01-18
+ (1 row)
+
+ SELECT date '1/8/1999';
+ ERROR: date/time field value out of range: "1/8/1999"
+ HINT: Perhaps you need a different "datestyle" setting.
+ SELECT date '1/18/1999';
+ ERROR: date/time field value out of range: "1/18/1999"
+ HINT: Perhaps you need a different "datestyle" setting.
+ SELECT date '18/1/1999';
+ ERROR: date/time field value out of range: "18/1/1999"
+ HINT: Perhaps you need a different "datestyle" setting.
+ SELECT date '01/02/03';
+ date
+ ------------
+ 2001-02-03
+ (1 row)
+
+ SELECT date '19990108';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '990108';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '1999.008';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date 'J2451187';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date 'January 8, 99 BC';
+ ERROR: date/time field value out of range: "January 8, 99 BC"
+ HINT: Perhaps you need a different "datestyle" setting.
+ SELECT date '99-Jan-08';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '1999-Jan-08';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '08-Jan-99';
+ ERROR: date/time field value out of range: "08-Jan-99"
+ HINT: Perhaps you need a different "datestyle" setting.
+ SELECT date '08-Jan-1999';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date 'Jan-08-99';
+ ERROR: date/time field value out of range: "Jan-08-99"
+ HINT: Perhaps you need a different "datestyle" setting.
+ SELECT date 'Jan-08-1999';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '99-08-Jan';
+ ERROR: invalid input syntax for type date: "99-08-Jan"
+ SELECT date '1999-08-Jan';
+ ERROR: invalid input syntax for type date: "1999-08-Jan"
+ SELECT date '99 Jan 08';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '1999 Jan 08';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '08 Jan 99';
+ ERROR: date/time field value out of range: "08 Jan 99"
+ HINT: Perhaps you need a different "datestyle" setting.
+ SELECT date '08 Jan 1999';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date 'Jan 08 99';
+ ERROR: date/time field value out of range: "Jan 08 99"
+ HINT: Perhaps you need a different "datestyle" setting.
+ SELECT date 'Jan 08 1999';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '99 08 Jan';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '1999 08 Jan';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '99-01-08';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '1999-01-08';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '08-01-99';
+ ERROR: date/time field value out of range: "08-01-99"
+ HINT: Perhaps you need a different "datestyle" setting.
+ SELECT date '08-01-1999';
+ ERROR: date/time field value out of range: "08-01-1999"
+ HINT: Perhaps you need a different "datestyle" setting.
+ SELECT date '01-08-99';
+ ERROR: date/time field value out of range: "01-08-99"
+ HINT: Perhaps you need a different "datestyle" setting.
+ SELECT date '01-08-1999';
+ ERROR: date/time field value out of range: "01-08-1999"
+ HINT: Perhaps you need a different "datestyle" setting.
+ SELECT date '99-08-01';
+ date
+ ------------
+ 1999-08-01
+ (1 row)
+
+ SELECT date '1999-08-01';
+ date
+ ------------
+ 1999-08-01
+ (1 row)
+
+ SELECT date '99 01 08';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '1999 01 08';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '08 01 99';
+ ERROR: date/time field value out of range: "08 01 99"
+ HINT: Perhaps you need a different "datestyle" setting.
+ SELECT date '08 01 1999';
+ ERROR: date/time field value out of range: "08 01 1999"
+ HINT: Perhaps you need a different "datestyle" setting.
+ SELECT date '01 08 99';
+ ERROR: date/time field value out of range: "01 08 99"
+ HINT: Perhaps you need a different "datestyle" setting.
+ SELECT date '01 08 1999';
+ ERROR: date/time field value out of range: "01 08 1999"
+ HINT: Perhaps you need a different "datestyle" setting.
+ SELECT date '99 08 01';
+ date
+ ------------
+ 1999-08-01
+ (1 row)
+
+ SELECT date '1999 08 01';
+ date
+ ------------
+ 1999-08-01
+ (1 row)
+
+ SET datestyle TO dmy;
+ SELECT date 'January 8, 1999';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '1999-01-08';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '1999-01-18';
+ date
+ ------------
+ 1999-01-18
+ (1 row)
+
+ SELECT date '1/8/1999';
+ date
+ ------------
+ 1999-08-01
+ (1 row)
+
+ SELECT date '1/18/1999';
+ ERROR: date/time field value out of range: "1/18/1999"
+ HINT: Perhaps you need a different "datestyle" setting.
+ SELECT date '18/1/1999';
+ date
+ ------------
+ 1999-01-18
+ (1 row)
+
+ SELECT date '01/02/03';
+ date
+ ------------
+ 2003-02-01
+ (1 row)
+
+ SELECT date '19990108';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '990108';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '1999.008';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date 'J2451187';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date 'January 8, 99 BC';
+ date
+ ---------------
+ 0099-01-08 BC
+ (1 row)
+
+ SELECT date '99-Jan-08';
+ ERROR: date/time field value out of range: "99-Jan-08"
+ HINT: Perhaps you need a different "datestyle" setting.
+ SELECT date '1999-Jan-08';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '08-Jan-99';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '08-Jan-1999';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date 'Jan-08-99';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date 'Jan-08-1999';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '99-08-Jan';
+ ERROR: invalid input syntax for type date: "99-08-Jan"
+ SELECT date '1999-08-Jan';
+ ERROR: invalid input syntax for type date: "1999-08-Jan"
+ SELECT date '99 Jan 08';
+ ERROR: date/time field value out of range: "99 Jan 08"
+ HINT: Perhaps you need a different "datestyle" setting.
+ SELECT date '1999 Jan 08';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '08 Jan 99';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '08 Jan 1999';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date 'Jan 08 99';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date 'Jan 08 1999';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '99 08 Jan';
+ ERROR: invalid input syntax for type date: "99 08 Jan"
+ SELECT date '1999 08 Jan';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '99-01-08';
+ ERROR: date/time field value out of range: "99-01-08"
+ HINT: Perhaps you need a different "datestyle" setting.
+ SELECT date '1999-01-08';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '08-01-99';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '08-01-1999';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '01-08-99';
+ date
+ ------------
+ 1999-08-01
+ (1 row)
+
+ SELECT date '01-08-1999';
+ date
+ ------------
+ 1999-08-01
+ (1 row)
+
+ SELECT date '99-08-01';
+ ERROR: date/time field value out of range: "99-08-01"
+ HINT: Perhaps you need a different "datestyle" setting.
+ SELECT date '1999-08-01';
+ date
+ ------------
+ 1999-08-01
+ (1 row)
+
+ SELECT date '99 01 08';
+ ERROR: date/time field value out of range: "99 01 08"
+ HINT: Perhaps you need a different "datestyle" setting.
+ SELECT date '1999 01 08';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '08 01 99';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '08 01 1999';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '01 08 99';
+ date
+ ------------
+ 1999-08-01
+ (1 row)
+
+ SELECT date '01 08 1999';
+ date
+ ------------
+ 1999-08-01
+ (1 row)
+
+ SELECT date '99 08 01';
+ ERROR: date/time field value out of range: "99 08 01"
+ HINT: Perhaps you need a different "datestyle" setting.
+ SELECT date '1999 08 01';
+ date
+ ------------
+ 1999-08-01
+ (1 row)
+
+ SET datestyle TO mdy;
+ SELECT date 'January 8, 1999';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '1999-01-08';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '1999-01-18';
+ date
+ ------------
+ 1999-01-18
+ (1 row)
+
+ SELECT date '1/8/1999';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '1/18/1999';
+ date
+ ------------
+ 1999-01-18
+ (1 row)
+
+ SELECT date '18/1/1999';
+ ERROR: date/time field value out of range: "18/1/1999"
+ HINT: Perhaps you need a different "datestyle" setting.
+ SELECT date '01/02/03';
+ date
+ ------------
+ 2003-01-02
+ (1 row)
+
+ SELECT date '19990108';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '990108';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '1999.008';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date 'J2451187';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date 'January 8, 99 BC';
+ date
+ ---------------
+ 0099-01-08 BC
+ (1 row)
+
+ SELECT date '99-Jan-08';
+ ERROR: date/time field value out of range: "99-Jan-08"
+ HINT: Perhaps you need a different "datestyle" setting.
+ SELECT date '1999-Jan-08';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '08-Jan-99';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '08-Jan-1999';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date 'Jan-08-99';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date 'Jan-08-1999';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '99-08-Jan';
+ ERROR: invalid input syntax for type date: "99-08-Jan"
+ SELECT date '1999-08-Jan';
+ ERROR: invalid input syntax for type date: "1999-08-Jan"
+ SELECT date '99 Jan 08';
+ ERROR: invalid input syntax for type date: "99 Jan 08"
+ SELECT date '1999 Jan 08';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '08 Jan 99';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '08 Jan 1999';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date 'Jan 08 99';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date 'Jan 08 1999';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '99 08 Jan';
+ ERROR: invalid input syntax for type date: "99 08 Jan"
+ SELECT date '1999 08 Jan';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '99-01-08';
+ ERROR: date/time field value out of range: "99-01-08"
+ HINT: Perhaps you need a different "datestyle" setting.
+ SELECT date '1999-01-08';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '08-01-99';
+ date
+ ------------
+ 1999-08-01
+ (1 row)
+
+ SELECT date '08-01-1999';
+ date
+ ------------
+ 1999-08-01
+ (1 row)
+
+ SELECT date '01-08-99';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '01-08-1999';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '99-08-01';
+ ERROR: date/time field value out of range: "99-08-01"
+ HINT: Perhaps you need a different "datestyle" setting.
+ SELECT date '1999-08-01';
+ date
+ ------------
+ 1999-08-01
+ (1 row)
+
+ SELECT date '99 01 08';
+ ERROR: date/time field value out of range: "99 01 08"
+ HINT: Perhaps you need a different "datestyle" setting.
+ SELECT date '1999 01 08';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '08 01 99';
+ date
+ ------------
+ 1999-08-01
+ (1 row)
+
+ SELECT date '08 01 1999';
+ date
+ ------------
+ 1999-08-01
+ (1 row)
+
+ SELECT date '01 08 99';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '01 08 1999';
+ date
+ ------------
+ 1999-01-08
+ (1 row)
+
+ SELECT date '99 08 01';
+ ERROR: date/time field value out of range: "99 08 01"
+ HINT: Perhaps you need a different "datestyle" setting.
+ SELECT date '1999 08 01';
+ date
+ ------------
+ 1999-08-01
+ (1 row)
+
+ RESET datestyle;
+ --
-- Simple math
-- Leave most of it for the horology tests
--
*** src/test/regress/sql/date.sql.orig Tue Sep 12 01:42:19 2000
--- src/test/regress/sql/date.sql Sat Nov 15 11:58:39 2003
***************
*** 29,34 ****
--- 29,194 ----
WHERE f1 BETWEEN '2000-01-01' AND '2001-01-01';
--
+ -- Check all the documented input formats
+ --
+ SET datestyle TO iso; -- display results in ISO
+
+ SET datestyle TO ymd;
+
+ SELECT date 'January 8, 1999';
+ SELECT date '1999-01-08';
+ SELECT date '1999-01-18';
+ SELECT date '1/8/1999';
+ SELECT date '1/18/1999';
+ SELECT date '18/1/1999';
+ SELECT date '01/02/03';
+ SELECT date '19990108';
+ SELECT date '990108';
+ SELECT date '1999.008';
+ SELECT date 'J2451187';
+ SELECT date 'January 8, 99 BC';
+
+ SELECT date '99-Jan-08';
+ SELECT date '1999-Jan-08';
+ SELECT date '08-Jan-99';
+ SELECT date '08-Jan-1999';
+ SELECT date 'Jan-08-99';
+ SELECT date 'Jan-08-1999';
+ SELECT date '99-08-Jan';
+ SELECT date '1999-08-Jan';
+
+ SELECT date '99 Jan 08';
+ SELECT date '1999 Jan 08';
+ SELECT date '08 Jan 99';
+ SELECT date '08 Jan 1999';
+ SELECT date 'Jan 08 99';
+ SELECT date 'Jan 08 1999';
+ SELECT date '99 08 Jan';
+ SELECT date '1999 08 Jan';
+
+ SELECT date '99-01-08';
+ SELECT date '1999-01-08';
+ SELECT date '08-01-99';
+ SELECT date '08-01-1999';
+ SELECT date '01-08-99';
+ SELECT date '01-08-1999';
+ SELECT date '99-08-01';
+ SELECT date '1999-08-01';
+
+ SELECT date '99 01 08';
+ SELECT date '1999 01 08';
+ SELECT date '08 01 99';
+ SELECT date '08 01 1999';
+ SELECT date '01 08 99';
+ SELECT date '01 08 1999';
+ SELECT date '99 08 01';
+ SELECT date '1999 08 01';
+
+ SET datestyle TO dmy;
+
+ SELECT date 'January 8, 1999';
+ SELECT date '1999-01-08';
+ SELECT date '1999-01-18';
+ SELECT date '1/8/1999';
+ SELECT date '1/18/1999';
+ SELECT date '18/1/1999';
+ SELECT date '01/02/03';
+ SELECT date '19990108';
+ SELECT date '990108';
+ SELECT date '1999.008';
+ SELECT date 'J2451187';
+ SELECT date 'January 8, 99 BC';
+
+ SELECT date '99-Jan-08';
+ SELECT date '1999-Jan-08';
+ SELECT date '08-Jan-99';
+ SELECT date '08-Jan-1999';
+ SELECT date 'Jan-08-99';
+ SELECT date 'Jan-08-1999';
+ SELECT date '99-08-Jan';
+ SELECT date '1999-08-Jan';
+
+ SELECT date '99 Jan 08';
+ SELECT date '1999 Jan 08';
+ SELECT date '08 Jan 99';
+ SELECT date '08 Jan 1999';
+ SELECT date 'Jan 08 99';
+ SELECT date 'Jan 08 1999';
+ SELECT date '99 08 Jan';
+ SELECT date '1999 08 Jan';
+
+ SELECT date '99-01-08';
+ SELECT date '1999-01-08';
+ SELECT date '08-01-99';
+ SELECT date '08-01-1999';
+ SELECT date '01-08-99';
+ SELECT date '01-08-1999';
+ SELECT date '99-08-01';
+ SELECT date '1999-08-01';
+
+ SELECT date '99 01 08';
+ SELECT date '1999 01 08';
+ SELECT date '08 01 99';
+ SELECT date '08 01 1999';
+ SELECT date '01 08 99';
+ SELECT date '01 08 1999';
+ SELECT date '99 08 01';
+ SELECT date '1999 08 01';
+
+ SET datestyle TO mdy;
+
+ SELECT date 'January 8, 1999';
+ SELECT date '1999-01-08';
+ SELECT date '1999-01-18';
+ SELECT date '1/8/1999';
+ SELECT date '1/18/1999';
+ SELECT date '18/1/1999';
+ SELECT date '01/02/03';
+ SELECT date '19990108';
+ SELECT date '990108';
+ SELECT date '1999.008';
+ SELECT date 'J2451187';
+ SELECT date 'January 8, 99 BC';
+
+ SELECT date '99-Jan-08';
+ SELECT date '1999-Jan-08';
+ SELECT date '08-Jan-99';
+ SELECT date '08-Jan-1999';
+ SELECT date 'Jan-08-99';
+ SELECT date 'Jan-08-1999';
+ SELECT date '99-08-Jan';
+ SELECT date '1999-08-Jan';
+
+ SELECT date '99 Jan 08';
+ SELECT date '1999 Jan 08';
+ SELECT date '08 Jan 99';
+ SELECT date '08 Jan 1999';
+ SELECT date 'Jan 08 99';
+ SELECT date 'Jan 08 1999';
+ SELECT date '99 08 Jan';
+ SELECT date '1999 08 Jan';
+
+ SELECT date '99-01-08';
+ SELECT date '1999-01-08';
+ SELECT date '08-01-99';
+ SELECT date '08-01-1999';
+ SELECT date '01-08-99';
+ SELECT date '01-08-1999';
+ SELECT date '99-08-01';
+ SELECT date '1999-08-01';
+
+ SELECT date '99 01 08';
+ SELECT date '1999 01 08';
+ SELECT date '08 01 99';
+ SELECT date '08 01 1999';
+ SELECT date '01 08 99';
+ SELECT date '01 08 1999';
+ SELECT date '99 08 01';
+ SELECT date '1999 08 01';
+
+ RESET datestyle;
+
+ --
-- Simple math
-- Leave most of it for the horology tests
--
pgsql-hackers by date: