datatype.sgml

来自「关系型数据库 Postgresql 6.5.2」· SGML 代码 · 共 1,926 行 · 第 1/4 页

       <thead>
	<row>
	 <entry>Constant</entry>
	 <entry>Description</entry>
	</row>
       </thead>
       <tbody>
	<row>
	 <entry>current</entry>
	 <entry>Current transaction time, deferred</entry>
	</row>
	<row>
	 <entry>epoch</entry>
	 <entry>1970-01-01 00:00:00+00 (Unix system time zero)</entry>
	</row>
	<row>
	 <entry>infinity</entry>
	 <entry>Later than other valid times</entry>
	</row>
	<row>
	 <entry>-infinity</entry>
	 <entry>Earlier than other valid times</entry>
	</row>
	<row>
	 <entry>invalid</entry>
	 <entry>Illegal entry</entry>
	</row>
	<row>
	 <entry>now</entry>
	 <entry>Current transaction time</entry>
	</row>
	<row>
	 <entry>today</entry>
	 <entry>Midnight today</entry>
	</row>
	<row>
	 <entry>tomorrow</entry>
	 <entry>Midnight tomorrow</entry>
	</row>
	<row>
	 <entry>yesterday</entry>
	 <entry>Midnight yesterday</entry>
	</row>
       </tbody>
      </tgroup>
     </table>
    </para>

    <para>
     <table tocentry="1">
      <title><productname>Postgres</productname> Date Input</title>
      <titleabbrev>Date Inputs</titleabbrev>
      <tgroup cols="2">
       <thead>
	<row>
	 <entry>Example</entry>
	 <entry>Description</entry>
	</row>
       </thead>
       <tbody>
	<row>
	 <entry>January 8, 1999</entry>
	 <entry>Unambiguous text month</entry>
	</row>
	<row>
	 <entry>1999-01-08</entry>
	 <entry>ISO-8601</entry>
	</row>
	<row>
	 <entry>1/8/1999</entry>
	 <entry>US; read as August 1 in European mode</entry>
	</row>
	<row>
	 <entry>8/1/1999</entry>
	 <entry>European; read as August 1 in US mode</entry>
	</row>
	<row>
	 <entry>1/18/1999</entry>
	 <entry>US; read as January 18 in any mode</entry>
	</row>
	<row>
	 <entry>1999.008</entry>
	 <entry>Year and day of year</entry>
	</row>
	<row>
	 <entry>19990108</entry>
	 <entry>ISO-8601 year, month, day</entry>
	</row>
	<row>
	 <entry>990108</entry>
	 <entry>ISO-8601 year, month, day</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>January 8, 99 BC</entry>
	 <entry>Year 99 before the Christian Era</entry>
	</row>
       </tbody>
      </tgroup>
     </table>
    </para>

    <para>
     <table tocentry="1">
      <title><productname>Postgres</productname> Month Abbreviations</title>
      <titleabbrev>Month Abbreviations</titleabbrev>
      <tgroup cols="2">
       <thead>
	<row>
	 <entry>Month</entry>
	 <entry>Abbreviations</entry>
	</row>
       </thead>
       <tbody>
	<row>
	 <entry>April</entry>
	 <entry>Apr</entry>
	</row>
	<row>
	 <entry>August</entry>
	 <entry>Aug</entry>
	</row>
	<row>
	 <entry>December</entry>
	 <entry>Dec</entry>
	</row>
	<row>
	 <entry>February</entry>
	 <entry>Feb</entry>
	</row>
	<row>
	 <entry>January</entry>
	 <entry>Jan</entry>
	</row>
	<row>
	 <entry>July</entry>
	 <entry>Jul</entry>
	</row>
	<row>
	 <entry>June</entry>
	 <entry>Jun</entry>
	</row>
	<row>
	 <entry>March</entry>
	 <entry>Mar</entry>
	</row>
	<row>
	 <entry>November</entry>
	 <entry>Nov</entry>
	</row>
	<row>
	 <entry>October</entry>
	 <entry>Oct</entry>
	</row>
	<row>
	 <entry>September</entry>
	 <entry>Sep, Sept</entry>
	</row>
       </tbody>
      </tgroup>
     </table>

     <note>
      <para>
       The month <literal>May</literal> has no explicit abbreviation, for obvious reasons.
      </para>
     </note>
    </para>

    <para>
     <table tocentry="1">
      <title><productname>Postgres</productname> Day of Week Abbreviations</title>
      <titleabbrev>Day of Week Abbreviations</titleabbrev>
      <tgroup cols="2">
       <thead>
	<row>
	 <entry>Day</entry>
	 <entry>Abbreviation</entry>
	</row>
       </thead>
       <tbody>
	<row>
	 <entry>Sunday</entry>
	 <entry>Sun</entry>
	</row>
	<row>
	 <entry>Monday</entry>
	 <entry>Mon</entry>
	</row>
	<row>
	 <entry>Tuesday</entry>
	 <entry>Tue, Tues</entry>
	</row>
	<row>
	 <entry>Wednesday</entry>
	 <entry>Wed, Weds</entry>
	</row>
	<row>
	 <entry>Thursday</entry>
	 <entry>Thu, Thur, Thurs</entry>
	</row>
	<row>
	 <entry>Friday</entry>
	 <entry>Fri</entry>
	</row>
	<row>
	 <entry>Saturday</entry>
	 <entry>Sat</entry>
	</row>
       </tbody>
      </tgroup>
     </table>
    </para>

    <para>
     <table tocentry="1">
      <title><productname>Postgres</productname> Time Input</title>
      <titleabbrev>Time Inputs</titleabbrev>
      <tgroup cols="2">
       <thead>
	<row>
	 <entry>Example</entry>
	 <entry>Description</entry>
	</row>
       </thead>
       <tbody>
	<row>
	 <entry>04:05:06.789</entry>
	 <entry>ISO-8601, with all time fields</entry>
	</row>
	<row>
	 <entry>04:05:06</entry>
	 <entry>ISO-8601</entry>
	</row>
	<row>
	 <entry>04:05</entry>
	 <entry>ISO-8601</entry>
	</row>
	<row>
	 <entry>040506</entry>
	 <entry>ISO-8601</entry>
	</row>
	<row>
	 <entry>04:05 AM</entry>
	 <entry>Same as 04:05; AM does not affect value</entry>
	</row>
	<row>
	 <entry>04:05 PM</entry>
	 <entry>Same as 16:05; input hour must be <= 12</entry>
	</row>
	<row>
	 <entry>z</entry>
	 <entry>Same as 00:00:00</entry>
	</row>
	<row>
	 <entry>zulu</entry>
	 <entry>Same as 00:00:00</entry>
	</row>
	<row>
	 <entry>allballs</entry>
	 <entry>Same as 00:00:00</entry>
	</row>
       </tbody>
      </tgroup>
     </table>
    </para>

    <para>
     <table tocentry="1">
      <title><productname>Postgres</productname> Time Zone Input</title>
      <titleabbrev>Time Zone Inputs</titleabbrev>
      <tgroup cols="2">
       <thead>
	<row>
	 <entry>Time Zone</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>
    </para>

    <para>
     See <xref linkend="datetime-appendix-title" endterm="datetime-appendix-title">
     for details on time zones recognized by <productname>Postgres</productname>.

     <note>
      <para>
       If the compiler option USE_AUSTRALIAN_RULES is set 
       then <literal>EST</literal> refers to Australia Eastern Std Time,
       which has an offset of +10:00 hours from UTC.
      </para>
     </note>
    </para>

    <para>
     Australian time zones and their naming variants
     account for fully one quarter of all time zones in the 
     <productname>Postgres</productname> time zone lookup table.
    </para>

   </sect2>

   <sect2>
    <title>datetime</title>

<para>
General-use date and time is input using a wide range of
styles, including ISO-compatible, <acronym>SQL</acronym>-compatible, traditional
<productname>Postgres</productname> (see section on "absolute time")
and other permutations of date and time. Output styles can be ISO-compatible,
<acronym>SQL</acronym>-compatible, or traditional 
<productname>Postgres</productname>, with the default set to be compatible
with <productname>Postgres</productname> v6.0.
</para>

<para>
<type>datetime</type> is specified using the following syntax:

<programlisting>
Year-Month-Day [ Hour : Minute : Second ]      [AD,BC] [ Timezone ]
  YearMonthDay [ Hour : Minute : Second ]      [AD,BC] [ Timezone ]
     Month Day [ Hour : Minute : Second ] Year [AD,BC] [ Timezone ]
where	
    Year is 4013 BC, ..., very large
    Month is Jan, Feb, ..., Dec or 1, 2, ..., 12
    Day is 1, 2, ..., 31
    Hour is 00, 02, ..., 23
    Minute is 00, 01, ..., 59
    Second is 00, 01, ..., 59 (60 for leap second)
    Timezone is 3 characters or ISO offset to GMT
</programlisting>
    </para>

<para>
Valid dates are from Nov 13 00:00:00 4013 BC GMT to far into the future.
Timezones are either three characters (e.g. "GMT" or "PST") or ISO-compatible
offsets to GMT (e.g. "-08" or "-08:00" when in Pacific Standard Time).
Dates are stored internally in Greenwich Mean Time. Input and output routines 
translate time to the local time zone of the server.
</para></sect2>

<sect2>
<title><type>timespan</type></title>

<para>
General-use time span is input using a wide range of
syntaxes, including ISO-compatible, <acronym>SQL</acronym>-compatible, 
traditional
<productname>Postgres</productname> (see section on "relative time")
 and other permutations of time span. Output formats can be ISO-compatible,
<acronym>SQL</acronym>-compatible, or traditional 
<productname>Postgres</productname>, 
with the default set to be <productname>Postgres</productname>-compatible.
Months and years are a "qualitative" time interval, and are stored separately
from the other "quantitative" time intervals such as day or hour. 
For date arithmetic,
the qualitative time units are instantiated in the context of the 
relevant date or time.
    </para>

<para>
Time span is specified with the following syntax:

<programlisting>
  Quantity Unit [Quantity Unit...] [Direction]
@ Quantity Unit [Direction]
where 	
    Quantity is ..., <literal>-1</literal>, <literal>0</literal>, <literal>1</literal>, <literal>2</literal>, ...
    Unit is <literal>second</literal>, <literal>minute</literal>, <literal>hour</literal>, <literal>day</literal>, <literal>week</literal>, <literal>month</literal>, <literal>year</literal>,
      <literal>decade</literal>, <literal>century</literal>, <literal>millenium</literal>, or abbreviations or plurals of these units.
    Direction is <literal>ago</literal>.
</programlisting>
</para>
</sect2>

<sect2>
<title>abstime</title>

    <para>
     Absolute time (<type>abstime</type>) is a limited-range (+/- 68 years) and 
     limited-precision (1 sec)
     date data type. <type>datetime</type> may be preferred, since it
     covers a larger range with greater precision.
    </para>

<para>
Absolute time is specified using the following syntax:

<programlisting>
Month  Day [ Hour : Minute : Second ]  Year [ Timezone ]
where	
    Month is Jan, Feb, ..., Dec
    Day is 1, 2, ..., 31
    Hour is 01, 02, ..., 24
    Minute is 00, 01, ..., 59
    Second is 00, 01, ..., 59
    Year is 1901, 1902, ..., 2038
</programlisting>
</para>

<para>
     Valid dates are from <literal>Dec 13 20:45:53 1901 GMT</literal>
     to <literal>Jan 19 03:14:04 2038 GMT</literal>. 

<note>
<title>Historical Note</title>
<para>
As of Version 3.0, times are no longer read and written
using Greenwich Mean Time; the input and output routines default to
the local time zone.</para>
</note>

All special values allowed for <type>datetime</type> are also 
allowed for "absolute time".
</para>

</sect2>

<sect2>
<title>reltime</title>

<para>
Relative time <type>reltime</type> is a limited-range (+/- 68 years)
 and limited-precision (1 sec) time span data type.
<type>timespan</type> should be preferred, since it
covers a larger range with greater precision and, more importantly, 
can distinguish between
relative units (months and years) and quantitative units (days, hours, etc). 
Instead, reltime
must force months to be exactly 30 days, so time arithmetic does not 
always work as expected.
     For example, adding one reltime <literal>year</literal> to abstime <literal>today</literal> does not 
produce today's date one year from
now, but rather a date 360 days from today.
</para>

<para>
<type>reltime</type> shares input and output routines with the other 
time span types.
The section on <type>timespan</type> covers this in more detail.
</para>

</sect2>

<sect2>
<title><type>timestamp</type></title>

<para>
This is currently a limited-range absolute time which closely resembles the
abstime
data type. It shares the general input parser with the other date/time types.
In future releases this type will absorb the capabilities of the 
<type>datetime</type> type
and will move toward <acronym>SQL92</acronym> compliance.
</para>