From 9c2a7c2269d1ecebd7f83e769bb2640cb82fa0e0 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 29 Jul 2003 00:03:19 +0000 Subject: Apply (a somewhat revised version of) Greg Mullane's patch to eliminate heuristic determination of day vs month in date/time input. Add the ability to specify that input is interpreted as yy-mm-dd order (which formerly worked, but only for yy greater than 31). DateStyle's input component now has the preferred spellings DMY, MDY, or YMD; the older keywords European and US are now aliases for the first two of these. Per recent discussions on pgsql-general. --- doc/src/sgml/datatype.sgml | 145 ++++++++++++++++++++----------------- doc/src/sgml/datetime.sgml | 55 ++++---------- doc/src/sgml/func.sgml | 4 +- doc/src/sgml/ref/postgres-ref.sgml | 11 ++- doc/src/sgml/ref/set.sgml | 7 +- doc/src/sgml/ref/show.sgml | 4 +- doc/src/sgml/release.sgml | 4 +- doc/src/sgml/runtime.sgml | 16 +++- 8 files changed, 121 insertions(+), 125 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index 348e142773..fd81d447fd 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -1,5 +1,5 @@ @@ -1346,15 +1346,12 @@ SELECT b, char_length(b) FROM test2; Date and time input is accepted in almost any reasonable format, including ISO 8601, SQL-compatible, traditional POSTGRES, and others. - For some formats, ordering of month and day in date input can be + For some formats, ordering of month, day, and year in date input is ambiguous and there is support for specifying the expected - ordering of these fields. - The command - SET datestyle TO 'US' - or SET datestyle TO 'NonEuropean' - specifies the variant month before day, the command - SET datestyle TO 'European' sets the variant - day before month. + ordering of these fields. Set the datestyle parameter + to MDY to select month-day-year interpretation, + DMY to select day-month-year interpretation, or + YMD to select year-month-day interpretation. @@ -1410,36 +1407,42 @@ SELECT b, char_length(b) FROM test2; January 8, 1999 - unambiguous + unambiguous in any datestyle input mode 1999-01-08 - ISO-8601 format, preferred + ISO-8601, January 8 in any mode + (recommended format) 1/8/1999 - ambiguous (January 8 in U.S. mode; August 1 in European mode) + January 8 in MDY mode; + August 1 in DMY mode 1/18/1999 - U.S. notation; January 18 in any mode + January 18 in MDY mode; + rejected in other modes + + + 01/02/03 + January 2, 2003 in MDY mode; + February 1, 2003 in DMY mode; + February 3, 2001 in YMD mode + 19990108 - ISO-8601; year, month, day + ISO-8601; January 8, 1999 in any mode 990108 - ISO-8601; year, month, day + ISO-8601; January 8, 1999 in any mode 1999.008 year and day of year - - 99008 - year and day of year - J2451187 Julian day @@ -1480,7 +1483,8 @@ SELECT b, char_length(b) FROM test2; Valid input for these types consists of a time of day followed by an optional time zone. (See .) If a time zone is + linkend="datatype-datetime-time-table"> + and .) If a time zone is specified in the input for time without time zone, it is silently ignored. @@ -1539,14 +1543,43 @@ SELECT b, char_length(b) FROM test2; 040506-08 ISO 8601 + + 04:05:06 PST + timezone specified by name + - - Refer to for - more examples of time zones. - + + Time Zone Input + + + + Example + Description + + + + + PST + Pacific Standard Time + + + -8:00 + ISO-8601 offset for PST + + + -800 + ISO-8601 offset for PST + + + -8 + ISO-8601 offset for PST + + + +
@@ -1571,8 +1604,7 @@ SELECT b, char_length(b) FROM test2; Valid input for the time stamp types consists of a concatenation of a date and a time, followed by an optional AD or BC, followed by an - optional time zone. (See .) Thus + optional time zone. Thus 1999-01-08 04:05:06 @@ -1626,36 +1658,6 @@ January 8 04:05:06 1999 PST as timezone local time. A different zone reference can be specified for the conversion using AT TIME ZONE. - - - Time Zone Input - - - - Example - Description - - - - - PST - Pacific Standard Time - - - -8:00 - ISO-8601 offset for PST - - - -800 - ISO-8601 offset for PST - - - -8 - ISO-8601 offset for PST - - - -
@@ -1760,7 +1762,7 @@ January 8 04:05:06 1999 PST now date, time, timestamp - current transaction time + current transaction's start time today @@ -1805,9 +1807,10 @@ January 8 04:05:06 1999 PST - The output format of the date/time types can be set to one of the four styles ISO 8601, + The output format of the date/time types can be set to one of the four + styles ISO 8601, SQL (Ingres), traditional POSTGRES, and - German, using the SET datestyle. The default + German, using the command SET datestyle. The default is the ISO format. (The SQL standard requires the use of the ISO 8601 format. The name of the SQL output format is a @@ -1854,9 +1857,10 @@ January 8 04:05:06 1999 PST - The SQL style has European and non-European - (U.S.) variants, which determines whether month follows day or - vice versa. (See + In the SQL and POSTGRES styles, day appears before + month if DMY field ordering has been specified, otherwise month appears + before day. + (See for how this setting also affects interpretation of input values.) shows an example. @@ -1867,22 +1871,27 @@ January 8 04:05:06 1999 PST - Style Specification - Description - Example + DateStyle setting + Input Ordering + Example Output - European + SQL, DMY day/month/year 17/12/1997 15:37:16.00 CET - US + SQL, MDY month/day/year 12/17/1997 07:37:16.00 PST + + Postgres, DMY + day/month/year + Wed 17 Dec 07:37:16 1997 PST + @@ -1903,7 +1912,7 @@ January 8 04:05:06 1999 PST The date/time styles can be selected by the user using the SET datestyle command, the datestyle parameter in the - postgresql.conf configuration file, and the + postgresql.conf configuration file, or the PGDATESTYLE environment variable on the server or client. The formatting function to_char (see ) is also available as diff --git a/doc/src/sgml/datetime.sgml b/doc/src/sgml/datetime.sgml index d7a76dab23..c205ee9c31 100644 --- a/doc/src/sgml/datetime.sgml +++ b/doc/src/sgml/datetime.sgml @@ -1,5 +1,5 @@ @@ -27,7 +27,7 @@ $Header: /cvsroot/pgsql/doc/src/sgml/datetime.sgml,v 2.31 2003/04/07 01:29:25 pe Date/Time Input Interpretation - The date/time type inputs are all decoded using the following routine. + The date/time type inputs are all decoded using the following procedure. @@ -103,7 +103,7 @@ $Header: /cvsroot/pgsql/doc/src/sgml/datetime.sgml,v 2.31 2003/04/07 01:29:25 pe - If not found, throw an error. + If still not found, throw an error. @@ -111,70 +111,47 @@ $Header: /cvsroot/pgsql/doc/src/sgml/datetime.sgml,v 2.31 2003/04/07 01:29:25 pe - The token is a number or number field. + When the token is a number or number field: - If there are more than 4 digits, + If there are eight or six digits, and if no other date fields have been previously read, then interpret - as a concatenated date (e.g., 19990118). 8 - and 6 digits are interpreted as year, month, and day, while 7 - and 5 digits are interpreted as year, day of year, respectively. + as a concatenated date (e.g., + 19990118 or 990118). + The interpretation is YYYYMMDD or YYMMDD. If the token is three digits - and a year has already been decoded, then interpret as day of year. + and a year has already been read, then interpret as day of year. If four or six digits and a year has already been read, then - interpret as a time. + interpret as a time (HHMM or HHMMSS). - If four or more digits, then interpret as a year. + If three or more digits and no date fields have yet been found, + interpret as a year (this forces yy-mm-dd ordering of the remaining + date fields). - If in European date mode, and if the day field has not yet been read, - and if the value is less than or equal to 31, then interpret as a day. - - - - - - If the month field has not yet been read, - and if the value is less than or equal to 12, then interpret as a month. - - - - - - If the day field has not yet been read, - and if the value is less than or equal to 31, then interpret as a day. - - - - - - If two digits or four or more digits, then interpret as a year. - - - - - - Otherwise, throw an error. + Otherwise the date field ordering is assumed to follow the + DateStyle setting: mm-dd-yy, dd-mm-yy, or yy-mm-dd. + Throw an error if a month or day field is found to be out of range. diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 942bd83c78..d5a528e1c3 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,5 +1,5 @@ @@ -6503,7 +6503,7 @@ SELECT current_setting('datestyle'); current_setting ----------------- - ISO, US + ISO, MDY (1 row) diff --git a/doc/src/sgml/ref/postgres-ref.sgml b/doc/src/sgml/ref/postgres-ref.sgml index e09c523b1a..073b83fd7d 100644 --- a/doc/src/sgml/ref/postgres-ref.sgml +++ b/doc/src/sgml/ref/postgres-ref.sgml @@ -1,5 +1,5 @@ @@ -147,11 +147,10 @@ PostgreSQL documentation - Sets the default date style to European, which - means that the day before month (rather than - month before day) rule is used to interpret ambiguous date - input, and that the day is printed before the month in certain - date output formats. See for more information. + Sets the default date style to European, that is + DMY ordering of input date fields. This also causes + the day to be printed before the month in certain date output formats. + See for more information. diff --git a/doc/src/sgml/ref/set.sgml b/doc/src/sgml/ref/set.sgml index 08ea5f3c2f..bb1ddd2275 100644 --- a/doc/src/sgml/ref/set.sgml +++ b/doc/src/sgml/ref/set.sgml @@ -1,5 +1,5 @@ @@ -283,9 +283,10 @@ SET search_path TO my_schema, public; Set the style of date to traditional - POSTGRES with European conventions: + POSTGRES with day before month + input convention: -SET datestyle TO postgres,european; +SET datestyle TO postgres, dmy; diff --git a/doc/src/sgml/ref/show.sgml b/doc/src/sgml/ref/show.sgml index 1302ee2661..1f0f52735a 100644 --- a/doc/src/sgml/ref/show.sgml +++ b/doc/src/sgml/ref/show.sgml @@ -1,5 +1,5 @@ @@ -158,7 +158,7 @@ SHOW ALL SHOW DateStyle; DateStyle ----------- - ISO, US + ISO, MDY (1 row) diff --git a/doc/src/sgml/release.sgml b/doc/src/sgml/release.sgml index 4bca26e8fd..50e42336d4 100644 --- a/doc/src/sgml/release.sgml +++ b/doc/src/sgml/release.sgml @@ -1,5 +1,5 @@ @@ -26,6 +26,8 @@ worries about funny characters. @@ -2101,9 +2101,17 @@ SET ENABLE_SEQSCAN TO OFF; Sets the display format for date and time values, as well as - the rules for interpreting ambiguous date input values. See - for more information. The - default is ISO, US. + the rules for interpreting ambiguous date input values. + For historical reasons, this variable contains two independent + components: the output format specification (ISO, + Postgres, SQL, or German) and + the date field order specification (DMY, MDY, + or YMD). These can be set separately or together. + The keywords Euro and European are synonyms + for DMY; the keywords US, NonEuro, + and NonEuropean are synonyms for MDY. + See for more information. The + default is ISO, MDY. -- cgit v1.2.1