<!--

$Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.120 2003/07/1803:45:06 momjian Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.121 2003/07/29 00:03:17 tgl Exp $

-->

<chapter id="datatype">

Date and time input is accepted in almost any reasonable format, including

ISO 8601, <acronym>SQL</acronym>-compatible,

traditional <productname>POSTGRES</productname>, and others.

For some formats, ordering of monthandday in date inputcan be

For some formats, ordering of month, day,andyear in date inputis

ambiguous and there is support for specifying the expected

ordering of these fields.

The command

<literal>SET datestyle TO 'US'</literal>

or <literal>SET datestyle TO 'NonEuropean'</literal>

specifies the variant <quote>month before day</quote>, the command

<literal>SET datestyle TO 'European'</literal> sets the variant

<quote>day before month</quote>.

ordering of these fields. Set the <varname>datestyle</> parameter

to <literal>MDY</> to select month-day-year interpretation,

<literal>DMY</> to select day-month-year interpretation, or

<literal>YMD</> to select year-month-day interpretation.

</para>

<para>

<tbody>

<row>

<entry>January 8, 1999</entry>

<entry>unambiguous</entry>

<entry>unambiguous in any datestyle input mode</entry>

</row>

<row>

<entry>1999-01-08</entry>

<entry>ISO-8601 format, preferred</entry>

<entry>ISO-8601, January 8 in any mode

(recommended format)</entry>

</row>

<row>

<entry>1/8/1999</entry>

<entry>ambiguous (January 8 in U.S. mode; August 1 in European mode)</entry>

<entry>January 8 in <literal>MDY</> mode;

August 1 in <literal>DMY</> mode</entry>

</row>

<row>

<entry>1/18/1999</entry>

<entry>U.S. notation; January 18 in any mode</entry>

<entry>January 18 in <literal>MDY</> mode;

rejected in other modes</entry>

</row>

<row>

<entry>01/02/03</entry>

<entry>January 2, 2003 in <literal>MDY</> mode;

February 1, 2003 in <literal>DMY</> mode;

February 3, 2001 in <literal>YMD</> mode

</entry>

</row>

<row>

<entry>19990108</entry>

<entry>ISO-8601;year, month, day</entry>

<entry>ISO-8601;January 8, 1999 in any mode</entry>

</row>

<row>

<entry>990108</entry>

<entry>ISO-8601;year, month, day</entry>

<entry>ISO-8601;January 8, 1999 in any mode</entry>

</row>

<row>

<entry>1999.008</entry>

<entry>year and day of year</entry>

</row>

<row>

<entry>99008</entry>

<entry>year and day of year</entry>

</row>

<row>

<entry>J2451187</entry>

<entry>Julian day</entry>

<para>

Valid input for these types consists of a time of day followed

by an optional time zone. (See <xref

linkend="datatype-datetime-time-table">.) If a time zone is

linkend="datatype-datetime-time-table">

and <xref linkend="datatype-timezone-table">.) If a time zone is

specified in the input for <type>time without time zone</type>,

it is silently ignored.

</para>

<entry>040506-08</entry>

<entry>ISO 8601</entry>

</row>

<row>

<entry>04:05:06 PST</entry>

<entry>timezone specified by name</entry>

</row>

</tbody>

</tgroup>

</table>

<para>

Refer to <xref linkend="datatype-timezone-table"> for

more examples of time zones.

</para>

<table tocentry="1" id="datatype-timezone-table">

<title>Time Zone Input</title>

<tgroup cols="2">

<thead>

<row>

<entry>Example</entry>

<entry>Description</entry>

</row>

</thead>

<tbody>

<row>

<entry>PST</entry>

<entry>Pacific Standard Time</entry>

</row>

<row>

<entry>-8:00</entry>

<entry>ISO-8601 offset for PST</entry>

</row>

<row>

<entry>-800</entry>

<entry>ISO-8601 offset for PST</entry>

</row>

<row>

<entry>-8</entry>

<entry>ISO-8601 offset for PST</entry>

</row>

</tbody>

</tgroup>

</table>

</sect3>

<sect3>

Valid input for the time stamp types consists of a concatenation

of a date and a time, followed by an optional

<literal>AD</literal> or <literal>BC</literal>, followed by an

optional time zone. (See <xref

linkend="datatype-timezone-table">.) Thus

optional time zone. Thus

<programlisting>

1999-01-08 04:05:06

as <varname>timezone</> local time. A different zone reference can

be specified for the conversion using <literal>AT TIME ZONE</>.

</para>

<table tocentry="1" id="datatype-timezone-table">

<title>Time Zone Input</title>

<tgroup cols="2">

<thead>

<row>

<entry>Example</entry>

<entry>Description</entry>

</row>

</thead>

<tbody>

<row>

<entry>PST</entry>

<entry>Pacific Standard Time</entry>

</row>

<row>

<entry>-8:00</entry>

<entry>ISO-8601 offset for PST</entry>

</row>

<row>

<entry>-800</entry>

<entry>ISO-8601 offset for PST</entry>

</row>

<row>

<entry>-8</entry>

<entry>ISO-8601 offset for PST</entry>

</row>

</tbody>

</tgroup>

</table>

</sect3>

<sect3>

<row>

<entry><literal>now</literal></entry>

<entry><type>date</type>, <type>time</type>, <type>timestamp</type></entry>

<entry>current transaction time</entry>

<entry>current transaction's start time</entry>

</row>

<row>

<entry><literal>today</literal></entry>

</indexterm>

<para>

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,

<acronym>SQL</acronym> (Ingres), traditional POSTGRES, and

German, using the <literal>SET datestyle</literal>. The default

German, using thecommand<literal>SET datestyle</literal>. The default

is the <acronym>ISO</acronym> format. (The

<acronym>SQL</acronym> standard requires the use of the ISO 8601

format. The name of the <quote>SQL</quote> output format is a

</table>

<para>

The <acronym>SQL</acronym> style has European and non-European

(U.S.) variants, which determines whether month follows day or

vice versa. (See <xref linkend="datatype-datetime-input">

In the <acronym>SQL</acronym> and POSTGRES styles, day appears before

month if DMY field ordering has been specified, otherwise month appears

before day.

(See <xref linkend="datatype-datetime-input">

for how this setting also affects interpretation of input values.)

<xref linkend="datatype-datetime-output2-table"> shows an

example.

<tgroup cols="3">

<thead>

<row>

<entry>Style Specification</entry>

<entry>Description</entry>

<entry>Example</entry>

<entry>DateStyle setting</entry>

<entry>Input Ordering</entry>

<entry>Example Output</entry>

</row>

</thead>

<tbody>

<row>

<entry>European</entry>

<entry><literal>SQL, DMY</></entry>

<entry><replaceable>day</replaceable>/<replaceable>month</replaceable>/<replaceable>year</replaceable></entry>

<entry>17/12/1997 15:37:16.00 CET</entry>

</row>

<row>

<entry>US</entry>

<entry><literal>SQL, MDY</></entry>

<entry><replaceable>month</replaceable>/<replaceable>day</replaceable>/<replaceable>year</replaceable></entry>

<entry>12/17/1997 07:37:16.00 PST</entry>

</row>

<row>

<entry><literal>Postgres, DMY</></entry>

<entry><replaceable>day</replaceable>/<replaceable>month</replaceable>/<replaceable>year</replaceable></entry>

<entry>Wed 17 Dec 07:37:16 1997 PST</entry>

</row>

</tbody>

</tgroup>

</table>

The date/time styles can be selected by the user using the

<command>SET datestyle</command> command, the

<varname>datestyle</varname> parameter in the

<filename>postgresql.conf</filename> configuration file,and the

<filename>postgresql.conf</filename> configuration file,or the

<envar>PGDATESTYLE</envar> environment variable on the server or

client. The formatting function <function>to_char</function>

(see <xref linkend="functions-formatting">) is also available as

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/datetime.sgml,v 2.31 2003/04/07 01:29:25 petere Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/datetime.sgml,v 2.32 2003/07/29 00:03:17 tgl Exp $

-->

<appendix id="datetime-appendix">

<title>Date/Time Input Interpretation</title>

<para>

The date/time type inputs are all decoded using the followingroutine.

The date/time type inputs are all decoded using the followingprocedure.

</para>

<procedure>

<step>

<para>

If not found, throw an error.

Ifstillnot found, throw an error.

</para>

</step>

</substeps>

</step>

<step>

<para>

Thetoken is a number or number field.

When thetoken is a number or number field:

</para>

<substeps>

<step>

<para>

If there aremore than 4 digits,

If there areeight or six digits,

and if no other date fields have been previously read, then interpret

as a <quote>concatenated date</quote> (e.g., <literal>19990118</literal>). 8

and 5 digits are interpreted as year, day of year, respectively.

as a <quote>concatenated date</quote> (e.g.,

The interpretation is <literal>YYYYMMDD</> or <literal>YYMMDD</>.

</para>

</step>

<step>

<para>

If the token is three digits

and a year has already beendecoded, then interpret as day of year.

and a year has already beenread, then interpret as day of year.

</para>

</step>

<step>

<para>

If four or six digits and a year has already been read, then

interpret as a time.

interpret as a time (<literal>HHMM</> or <literal>HHMMSS</>).

</para>

</step>

<step>

<para>

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).

</para>

</step>

<step>

<para>

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.

</para>

</step>

<step>

<para>

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.

</para>

</step>

<step>

<para>

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.

</para>

</step>

<step>

<para>

If two digits or four or more digits, then interpret as a year.

</para>

</step>

<step>

<para>

Otherwise, throw an error.

Otherwise the date field ordering is assumed to follow the

<varname>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.

</para>

</step>

</substeps>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.160 2003/07/17 00:55:36 tgl Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.161 2003/07/29 00:03:17 tgl Exp $

PostgreSQL documentation

-->

current_setting

-----------------

ISO,US

ISO,MDY

(1 row)

</programlisting>

</para>