Movatterモバイル変換


[0]ホーム

URL:


Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

Sign up
Appearance settings

Commit9c2a7c2

Browse files
committed
Apply (a somewhat revised version of) Greg Mullane's patch to eliminate
heuristic determination of day vs month in date/time input. Add theability to specify that input is interpreted as yy-mm-dd order (whichformerly worked, but only for yy greater than 31). DateStyle's inputcomponent now has the preferred spellings DMY, MDY, or YMD; the olderkeywords European and US are now aliases for the first two of these.Per recent discussions on pgsql-general.
1 parent2baf4ef commit9c2a7c2

26 files changed

+441
-350
lines changed

‎doc/src/sgml/datatype.sgml

Lines changed: 77 additions & 68 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.120 2003/07/1803:45:06 momjian Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.121 2003/07/29 00:03:17 tgl Exp $
33
-->
44

55
<chapter id="datatype">
@@ -1346,15 +1346,12 @@ SELECT b, char_length(b) FROM test2;
13461346
Date and time input is accepted in almost any reasonable format, including
13471347
ISO 8601, <acronym>SQL</acronym>-compatible,
13481348
traditional <productname>POSTGRES</productname>, and others.
1349-
For some formats, ordering of monthandday in date inputcan be
1349+
For some formats, ordering of month, day,andyear in date inputis
13501350
ambiguous and there is support for specifying the expected
1351-
ordering of these fields.
1352-
The command
1353-
<literal>SET datestyle TO 'US'</literal>
1354-
or <literal>SET datestyle TO 'NonEuropean'</literal>
1355-
specifies the variant <quote>month before day</quote>, the command
1356-
<literal>SET datestyle TO 'European'</literal> sets the variant
1357-
<quote>day before month</quote>.
1351+
ordering of these fields. Set the <varname>datestyle</> parameter
1352+
to <literal>MDY</> to select month-day-year interpretation,
1353+
<literal>DMY</> to select day-month-year interpretation, or
1354+
<literal>YMD</> to select year-month-day interpretation.
13581355
</para>
13591356

13601357
<para>
@@ -1410,36 +1407,42 @@ SELECT b, char_length(b) FROM test2;
14101407
<tbody>
14111408
<row>
14121409
<entry>January 8, 1999</entry>
1413-
<entry>unambiguous</entry>
1410+
<entry>unambiguous in any datestyle input mode</entry>
14141411
</row>
14151412
<row>
14161413
<entry>1999-01-08</entry>
1417-
<entry>ISO-8601 format, preferred</entry>
1414+
<entry>ISO-8601, January 8 in any mode
1415+
(recommended format)</entry>
14181416
</row>
14191417
<row>
14201418
<entry>1/8/1999</entry>
1421-
<entry>ambiguous (January 8 in U.S. mode; August 1 in European mode)</entry>
1419+
<entry>January 8 in <literal>MDY</> mode;
1420+
August 1 in <literal>DMY</> mode</entry>
14221421
</row>
14231422
<row>
14241423
<entry>1/18/1999</entry>
1425-
<entry>U.S. notation; January 18 in any mode</entry>
1424+
<entry>January 18 in <literal>MDY</> mode;
1425+
rejected in other modes</entry>
1426+
</row>
1427+
<row>
1428+
<entry>01/02/03</entry>
1429+
<entry>January 2, 2003 in <literal>MDY</> mode;
1430+
February 1, 2003 in <literal>DMY</> mode;
1431+
February 3, 2001 in <literal>YMD</> mode
1432+
</entry>
14261433
</row>
14271434
<row>
14281435
<entry>19990108</entry>
1429-
<entry>ISO-8601;year, month, day</entry>
1436+
<entry>ISO-8601;January 8, 1999 in any mode</entry>
14301437
</row>
14311438
<row>
14321439
<entry>990108</entry>
1433-
<entry>ISO-8601;year, month, day</entry>
1440+
<entry>ISO-8601;January 8, 1999 in any mode</entry>
14341441
</row>
14351442
<row>
14361443
<entry>1999.008</entry>
14371444
<entry>year and day of year</entry>
14381445
</row>
1439-
<row>
1440-
<entry>99008</entry>
1441-
<entry>year and day of year</entry>
1442-
</row>
14431446
<row>
14441447
<entry>J2451187</entry>
14451448
<entry>Julian day</entry>
@@ -1480,7 +1483,8 @@ SELECT b, char_length(b) FROM test2;
14801483
<para>
14811484
Valid input for these types consists of a time of day followed
14821485
by an optional time zone. (See <xref
1483-
linkend="datatype-datetime-time-table">.) If a time zone is
1486+
linkend="datatype-datetime-time-table">
1487+
and <xref linkend="datatype-timezone-table">.) If a time zone is
14841488
specified in the input for <type>time without time zone</type>,
14851489
it is silently ignored.
14861490
</para>
@@ -1539,14 +1543,43 @@ SELECT b, char_length(b) FROM test2;
15391543
<entry>040506-08</entry>
15401544
<entry>ISO 8601</entry>
15411545
</row>
1546+
<row>
1547+
<entry>04:05:06 PST</entry>
1548+
<entry>timezone specified by name</entry>
1549+
</row>
15421550
</tbody>
15431551
</tgroup>
15441552
</table>
15451553

1546-
<para>
1547-
Refer to <xref linkend="datatype-timezone-table"> for
1548-
more examples of time zones.
1549-
</para>
1554+
<table tocentry="1" id="datatype-timezone-table">
1555+
<title>Time Zone Input</title>
1556+
<tgroup cols="2">
1557+
<thead>
1558+
<row>
1559+
<entry>Example</entry>
1560+
<entry>Description</entry>
1561+
</row>
1562+
</thead>
1563+
<tbody>
1564+
<row>
1565+
<entry>PST</entry>
1566+
<entry>Pacific Standard Time</entry>
1567+
</row>
1568+
<row>
1569+
<entry>-8:00</entry>
1570+
<entry>ISO-8601 offset for PST</entry>
1571+
</row>
1572+
<row>
1573+
<entry>-800</entry>
1574+
<entry>ISO-8601 offset for PST</entry>
1575+
</row>
1576+
<row>
1577+
<entry>-8</entry>
1578+
<entry>ISO-8601 offset for PST</entry>
1579+
</row>
1580+
</tbody>
1581+
</tgroup>
1582+
</table>
15501583
</sect3>
15511584

15521585
<sect3>
@@ -1571,8 +1604,7 @@ SELECT b, char_length(b) FROM test2;
15711604
Valid input for the time stamp types consists of a concatenation
15721605
of a date and a time, followed by an optional
15731606
<literal>AD</literal> or <literal>BC</literal>, followed by an
1574-
optional time zone. (See <xref
1575-
linkend="datatype-timezone-table">.) Thus
1607+
optional time zone. Thus
15761608

15771609
<programlisting>
15781610
1999-01-08 04:05:06
@@ -1626,36 +1658,6 @@ January 8 04:05:06 1999 PST
16261658
as <varname>timezone</> local time. A different zone reference can
16271659
be specified for the conversion using <literal>AT TIME ZONE</>.
16281660
</para>
1629-
1630-
<table tocentry="1" id="datatype-timezone-table">
1631-
<title>Time Zone Input</title>
1632-
<tgroup cols="2">
1633-
<thead>
1634-
<row>
1635-
<entry>Example</entry>
1636-
<entry>Description</entry>
1637-
</row>
1638-
</thead>
1639-
<tbody>
1640-
<row>
1641-
<entry>PST</entry>
1642-
<entry>Pacific Standard Time</entry>
1643-
</row>
1644-
<row>
1645-
<entry>-8:00</entry>
1646-
<entry>ISO-8601 offset for PST</entry>
1647-
</row>
1648-
<row>
1649-
<entry>-800</entry>
1650-
<entry>ISO-8601 offset for PST</entry>
1651-
</row>
1652-
<row>
1653-
<entry>-8</entry>
1654-
<entry>ISO-8601 offset for PST</entry>
1655-
</row>
1656-
</tbody>
1657-
</tgroup>
1658-
</table>
16591661
</sect3>
16601662

16611663
<sect3>
@@ -1760,7 +1762,7 @@ January 8 04:05:06 1999 PST
17601762
<row>
17611763
<entry><literal>now</literal></entry>
17621764
<entry><type>date</type>, <type>time</type>, <type>timestamp</type></entry>
1763-
<entry>current transaction time</entry>
1765+
<entry>current transaction's start time</entry>
17641766
</row>
17651767
<row>
17661768
<entry><literal>today</literal></entry>
@@ -1805,9 +1807,10 @@ January 8 04:05:06 1999 PST
18051807
</indexterm>
18061808

18071809
<para>
1808-
The output format of the date/time types can be set to one of the four styles ISO 8601,
1810+
The output format of the date/time types can be set to one of the four
1811+
styles ISO 8601,
18091812
<acronym>SQL</acronym> (Ingres), traditional POSTGRES, and
1810-
German, using the <literal>SET datestyle</literal>. The default
1813+
German, using thecommand<literal>SET datestyle</literal>. The default
18111814
is the <acronym>ISO</acronym> format. (The
18121815
<acronym>SQL</acronym> standard requires the use of the ISO 8601
18131816
format. The name of the <quote>SQL</quote> output format is a
@@ -1854,9 +1857,10 @@ January 8 04:05:06 1999 PST
18541857
</table>
18551858

18561859
<para>
1857-
The <acronym>SQL</acronym> style has European and non-European
1858-
(U.S.) variants, which determines whether month follows day or
1859-
vice versa. (See <xref linkend="datatype-datetime-input">
1860+
In the <acronym>SQL</acronym> and POSTGRES styles, day appears before
1861+
month if DMY field ordering has been specified, otherwise month appears
1862+
before day.
1863+
(See <xref linkend="datatype-datetime-input">
18601864
for how this setting also affects interpretation of input values.)
18611865
<xref linkend="datatype-datetime-output2-table"> shows an
18621866
example.
@@ -1867,22 +1871,27 @@ January 8 04:05:06 1999 PST
18671871
<tgroup cols="3">
18681872
<thead>
18691873
<row>
1870-
<entry>Style Specification</entry>
1871-
<entry>Description</entry>
1872-
<entry>Example</entry>
1874+
<entry>DateStyle setting</entry>
1875+
<entry>Input Ordering</entry>
1876+
<entry>Example Output</entry>
18731877
</row>
18741878
</thead>
18751879
<tbody>
18761880
<row>
1877-
<entry>European</entry>
1881+
<entry><literal>SQL, DMY</></entry>
18781882
<entry><replaceable>day</replaceable>/<replaceable>month</replaceable>/<replaceable>year</replaceable></entry>
18791883
<entry>17/12/1997 15:37:16.00 CET</entry>
18801884
</row>
18811885
<row>
1882-
<entry>US</entry>
1886+
<entry><literal>SQL, MDY</></entry>
18831887
<entry><replaceable>month</replaceable>/<replaceable>day</replaceable>/<replaceable>year</replaceable></entry>
18841888
<entry>12/17/1997 07:37:16.00 PST</entry>
18851889
</row>
1890+
<row>
1891+
<entry><literal>Postgres, DMY</></entry>
1892+
<entry><replaceable>day</replaceable>/<replaceable>month</replaceable>/<replaceable>year</replaceable></entry>
1893+
<entry>Wed 17 Dec 07:37:16 1997 PST</entry>
1894+
</row>
18861895
</tbody>
18871896
</tgroup>
18881897
</table>
@@ -1903,7 +1912,7 @@ January 8 04:05:06 1999 PST
19031912
The date/time styles can be selected by the user using the
19041913
<command>SET datestyle</command> command, the
19051914
<varname>datestyle</varname> parameter in the
1906-
<filename>postgresql.conf</filename> configuration file,and the
1915+
<filename>postgresql.conf</filename> configuration file,or the
19071916
<envar>PGDATESTYLE</envar> environment variable on the server or
19081917
client. The formatting function <function>to_char</function>
19091918
(see <xref linkend="functions-formatting">) is also available as

‎doc/src/sgml/datetime.sgml

Lines changed: 16 additions & 39 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/datetime.sgml,v 2.31 2003/04/07 01:29:25 petere Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/datetime.sgml,v 2.32 2003/07/29 00:03:17 tgl Exp $
33
-->
44

55
<appendix id="datetime-appendix">
@@ -27,7 +27,7 @@ $Header: /cvsroot/pgsql/doc/src/sgml/datetime.sgml,v 2.31 2003/04/07 01:29:25 pe
2727
<title>Date/Time Input Interpretation</title>
2828

2929
<para>
30-
The date/time type inputs are all decoded using the followingroutine.
30+
The date/time type inputs are all decoded using the followingprocedure.
3131
</para>
3232

3333
<procedure>
@@ -103,78 +103,55 @@ $Header: /cvsroot/pgsql/doc/src/sgml/datetime.sgml,v 2.31 2003/04/07 01:29:25 pe
103103

104104
<step>
105105
<para>
106-
If not found, throw an error.
106+
Ifstillnot found, throw an error.
107107
</para>
108108
</step>
109109
</substeps>
110110
</step>
111111

112112
<step>
113113
<para>
114-
Thetoken is a number or number field.
114+
When thetoken is a number or number field:
115115
</para>
116116

117117
<substeps>
118118
<step>
119119
<para>
120-
If there aremore than 4 digits,
120+
If there areeight or six digits,
121121
and if no other date fields have been previously read, then interpret
122-
as a <quote>concatenated date</quote> (e.g., <literal>19990118</literal>). 8
123-
and 6 digits are interpreted as year, month, and day, while 7
124-
and 5 digits are interpreted as year, day of year, respectively.
122+
as a <quote>concatenated date</quote> (e.g.,
123+
<literal>19990118</literal> or <literal>990118</literal>).
124+
The interpretation is <literal>YYYYMMDD</> or <literal>YYMMDD</>.
125125
</para>
126126
</step>
127127

128128
<step>
129129
<para>
130130
If the token is three digits
131-
and a year has already beendecoded, then interpret as day of year.
131+
and a year has already beenread, then interpret as day of year.
132132
</para>
133133
</step>
134134

135135
<step>
136136
<para>
137137
If four or six digits and a year has already been read, then
138-
interpret as a time.
138+
interpret as a time (<literal>HHMM</> or <literal>HHMMSS</>).
139139
</para>
140140
</step>
141141

142142
<step>
143143
<para>
144-
If four or more digits, then interpret as a year.
144+
If three or more digits and no date fields have yet been found,
145+
interpret as a year (this forces yy-mm-dd ordering of the remaining
146+
date fields).
145147
</para>
146148
</step>
147149

148150
<step>
149151
<para>
150-
If in European date mode, and if the day field has not yet been read,
151-
and if the value is less than or equal to 31, then interpret as a day.
152-
</para>
153-
</step>
154-
155-
<step>
156-
<para>
157-
If the month field has not yet been read,
158-
and if the value is less than or equal to 12, then interpret as a month.
159-
</para>
160-
</step>
161-
162-
<step>
163-
<para>
164-
If the day field has not yet been read,
165-
and if the value is less than or equal to 31, then interpret as a day.
166-
</para>
167-
</step>
168-
169-
<step>
170-
<para>
171-
If two digits or four or more digits, then interpret as a year.
172-
</para>
173-
</step>
174-
175-
<step>
176-
<para>
177-
Otherwise, throw an error.
152+
Otherwise the date field ordering is assumed to follow the
153+
<varname>DateStyle</> setting: mm-dd-yy, dd-mm-yy, or yy-mm-dd.
154+
Throw an error if a month or day field is found to be out of range.
178155
</para>
179156
</step>
180157
</substeps>

‎doc/src/sgml/func.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.160 2003/07/17 00:55:36 tgl Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.161 2003/07/29 00:03:17 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -6503,7 +6503,7 @@ SELECT current_setting('datestyle');
65036503

65046504
current_setting
65056505
-----------------
6506-
ISO,US
6506+
ISO,MDY
65076507
(1 row)
65086508
</programlisting>
65096509
</para>

0 commit comments

Comments
 (0)

[8]ページ先頭

©2009-2025 Movatter.jp