File: datatype897.html

package info (click to toggle)
mpsql 2.1-2
links: PTS
area: non-free
in suites: potato
size: 3,528 kB
ctags: 4,886
sloc: ansic: 35,184; makefile: 3,761; sh: 44
file content (1358 lines) | stat: -rw-r--r-- 21,731 bytes
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML
><HEAD
><TITLE
>Date/Time Types</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet version 1.19"><LINK
REL="HOME"
TITLE="PostgreSQL User's Guide"
HREF="user.html"><LINK
REL="UP"
TITLE="Data Types"
HREF="datatype.html"><LINK
REL="PREVIOUS"
TITLE="Character Types"
HREF="datatype833.html"><LINK
REL="NEXT"
TITLE="Boolean Type"
HREF="datatype1243.html"></HEAD
><BODY
BGCOLOR="#FFFFFF"
TEXT="#000000"
><DIV
CLASS="NAVHEADER"
><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="3"
ALIGN="center"
>PostgreSQL User's Guide</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="bottom"
><A
HREF="datatype833.html"
>Prev</A
></TD
><TD
WIDTH="80%"
ALIGN="center"
VALIGN="bottom"
>Chapter 5. Data Types</TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="bottom"
><A
HREF="datatype1243.html"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><H1
CLASS="SECT1"
><A
NAME="AEN897"
>Date/Time Types</A
></H1
><P
>There are two fundamental kinds of date and time measurements:
 absolute clock times and relative time intervals.
Both quantities should demonstrate continuity and smoothness, as does time itself.
<SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> supplies two primary user-oriented 
date and time types,
<SPAN
CLASS="TYPE"
>datetime</SPAN
> and <SPAN
CLASS="TYPE"
>timespan</SPAN
>, as well as 
the related <SPAN
CLASS="ACRONYM"
>SQL92</SPAN
> types <SPAN
CLASS="TYPE"
>timestamp</SPAN
>,
<SPAN
CLASS="TYPE"
>interval</SPAN
>,
<SPAN
CLASS="TYPE"
>date</SPAN
> and <SPAN
CLASS="TYPE"
>time</SPAN
>.</P
><P
>In a future release, <SPAN
CLASS="TYPE"
>datetime</SPAN
> and <SPAN
CLASS="TYPE"
>timespan</SPAN
> are likely
to merge with the <SPAN
CLASS="ACRONYM"
>SQL92</SPAN
> types <SPAN
CLASS="TYPE"
>timestamp</SPAN
>,
<SPAN
CLASS="TYPE"
>interval</SPAN
>.
Other date and time types are also available, mostly
for historical reasons.</P
><P
><DIV
CLASS="TABLE"
><P
><B
>Table 5-7. <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> Date/Time Types</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><TR
><TH
ALIGN="LEFT"
VALIGN="TOP"
>Date/Time Type</TH
><TH
ALIGN="LEFT"
VALIGN="TOP"
>Storage</TH
><TH
ALIGN="LEFT"
VALIGN="TOP"
>Recommendation</TH
><TH
ALIGN="LEFT"
VALIGN="TOP"
>Description</TH
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>abstime</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>4 bytes</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>original date and time</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>limited range</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>date</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>4 bytes</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
><SPAN
CLASS="ACRONYM"
>SQL92</SPAN
> type</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>wide range</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>datetime</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>8 bytes</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>best general date and time</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>wide range, high precision</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>interval</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>12 bytes</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
><SPAN
CLASS="ACRONYM"
>SQL92</SPAN
> type</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>equivalent to timespan</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>reltime</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>4 bytes</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>original time interval</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>limited range, low precision</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>time</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>4 bytes</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
><SPAN
CLASS="ACRONYM"
>SQL92</SPAN
> type</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>wide range</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>timespan</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>12 bytes</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>best general time interval</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>wide range, high precision</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>timestamp</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>4 bytes</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
><SPAN
CLASS="ACRONYM"
>SQL92</SPAN
> type</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>limited range</TD
></TR
></TABLE
></DIV
>

<SPAN
CLASS="TYPE"
>timestamp</SPAN
> is currently implemented separately from
<SPAN
CLASS="TYPE"
>datetime</SPAN
>, although they share input and output routines.</P
><P
><DIV
CLASS="TABLE"
><P
><B
>Table 5-8. <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> Date/Time Ranges</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><TR
><TH
ALIGN="LEFT"
VALIGN="TOP"
>Date/Time Type</TH
><TH
ALIGN="LEFT"
VALIGN="TOP"
>Earliest</TH
><TH
ALIGN="LEFT"
VALIGN="TOP"
>Latest</TH
><TH
ALIGN="LEFT"
VALIGN="TOP"
>Resolution</TH
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>abstime</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>1901-12-14</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>2038-01-19</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>1 sec</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>date</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>4713 BC</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>32767 AD</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>1 day</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>datetime</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>4713 BC</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>1465001 AD</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>1 microsec to 14 digits</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>interval</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>-178000000 years</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>178000000 years</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>1 microsec</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>reltime</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>-68 years</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>+68 years</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>1 sec</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>time</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>00:00:00.00</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>23:59:59.99</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>1 microsec</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>timespan</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>-178000000 years</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>178000000 years</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>1 microsec (14 digits)</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>timestamp</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>1901-12-14</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>2038-01-19</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>1 sec</TD
></TR
></TABLE
></DIV
></P
><P
><SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> endevours to be compatible with
<SPAN
CLASS="ACRONYM"
>SQL92</SPAN
> definitions for typical usage.
The <SPAN
CLASS="ACRONYM"
>SQL92</SPAN
> standard has an odd mix of date and
time types and capabilities. Two obvious problems are:

<P
></P
></P><UL
><LI
><P
>Although the <SPAN
CLASS="TYPE"
>date</SPAN
> type 
does not have an associated time zone, the
<SPAN
CLASS="TYPE"
>time</SPAN
> type can or does.&#13;</P
></LI
><LI
><P
>The default time zone is specified as a constant integer offset 
from GMT/UTC.&#13;</P
></LI
></UL
><P>

However, time zones in the real world can have no meaning unless 
associated with a date as well as a time
since the offset may vary through the year with daylight savings
time boundaries.&#13;</P
><P
>To address these difficulties, <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> 
associates time zones only with date and time
types which contain both date and time,
 and assumes local time for any type containing only
date or time. Further, time zone support is derived from 
the underlying operating system
time zone capabilities, and hence can handle daylight savings time 
and other expected behavior.&#13;</P
><P
>In future releases, the number of date/time types will decrease, 
with the current implementation of 
<SPAN
CLASS="TYPE"
>datetime</SPAN
> becoming <SPAN
CLASS="TYPE"
>timestamp</SPAN
>, 
<SPAN
CLASS="TYPE"
>timespan</SPAN
> becoming <SPAN
CLASS="TYPE"
>interval</SPAN
>,
and (possibly) <SPAN
CLASS="TYPE"
>abstime</SPAN
> and <SPAN
CLASS="TYPE"
>reltime</SPAN
> 
being deprecated in favor of <SPAN
CLASS="TYPE"
>timestamp</SPAN
> and <SPAN
CLASS="TYPE"
>interval</SPAN
>.
The more arcane features of the date/time definitions from 
the <SPAN
CLASS="ACRONYM"
>SQL92</SPAN
> standard are not likely to be pursued.</P
><H2
CLASS="SECT2"
><A
NAME="AEN1049"
>Date/Time Styles</A
></H2
><P
>Output formats can be set to one of four styles: 
ISO-8601, <SPAN
CLASS="ACRONYM"
>SQL</SPAN
> (Ingres), traditional
Postgres, and German.

<DIV
CLASS="TABLE"
><P
><B
>Table 5-9. <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> Date Styles</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><TR
><TH
ALIGN="LEFT"
VALIGN="TOP"
>Style Specification</TH
><TH
ALIGN="LEFT"
VALIGN="TOP"
>Description</TH
><TH
ALIGN="LEFT"
VALIGN="TOP"
>Example</TH
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>ISO</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>ISO-8601 standard</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>1997-12-17 07:37:16-08</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
><SPAN
CLASS="ACRONYM"
>SQL</SPAN
></TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>Traditional style</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>12/17/1997 07:37:16.00 PST</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>Postgres</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>Original style</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>Wed Dec 17 07:37:16 1997 PST</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>German</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>Regional style</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>17.12.1997 07:37:16.00 PST</TD
></TR
></TABLE
></DIV
></P
><P
>The <SPAN
CLASS="ACRONYM"
>SQL</SPAN
> style has European and non-European (US) variants, 
which determines whether month follows day or vica versa.

<DIV
CLASS="TABLE"
><P
><B
>Table 5-10. <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> Date Order Conventions</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><TR
><TH
ALIGN="LEFT"
VALIGN="TOP"
>Style Specification</TH
><TH
ALIGN="LEFT"
VALIGN="TOP"
>Description</TH
><TH
ALIGN="LEFT"
VALIGN="TOP"
>Example</TH
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>European</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>Regional convention</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>17/12/1997 15:37:16.00 MET</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>NonEuropean</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>Regional convention</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>12/17/1997 07:37:16.00 PST</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>US</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>Regional convention</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>12/17/1997 07:37:16.00 PST</TD
></TR
></TABLE
></DIV
></P
><P
>There are several ways to affect the appearance of date/time types:

<P
></P
></P><UL
COMPACT="COMPACT"
><LI
STYLE="list-style-type: disc"
><P
>The PGDATESTYLE environment variable used by the backend directly 
on postmaster startup.</P
></LI
><LI
STYLE="list-style-type: disc"
><P
>The PGDATESTYLE environment variable used by the frontend libpq 
on session startup.</P
></LI
><LI
STYLE="list-style-type: disc"
><P
>SET DateStyle <SPAN
CLASS="ACRONYM"
>SQL</SPAN
> command.</P
></LI
></UL
><P>&#13;</P
><P
>For <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> v6.4 (and earlier) 
the default date/time style is
"non-European traditional Postgres". 
In future releases, the default may become ISO-8601, which alleviates
date specification ambiguities and Y2K collation problems.</P
><H2
CLASS="SECT2"
><A
NAME="AEN1117"
>Time Zones</A
></H2
><P
><SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> obtains time zone support 
from the underlying operating system.
All dates and times are stored internally in Universal Coordinated Time (UTC),
 alternately known as Greenwich Mean Time (GMT). 
Times are converted to local time on the database server before being
sent to the client frontend, hence by default are in the server time zone.&#13;</P
><P
>There are several ways to affect the time zone behavior:

<P
></P
></P><UL
COMPACT="COMPACT"
><LI
STYLE="list-style-type: disc"
><P
>The TZ environment variable used by the backend directly
 on postmaster startup as the default time zone.</P
></LI
><LI
STYLE="list-style-type: disc"
><P
>The PGTZ environment variable set at the client used by libpq 
to send time zone information to the backend upon connection.</P
></LI
><LI
STYLE="list-style-type: disc"
><P
>The <SPAN
CLASS="ACRONYM"
>SQL</SPAN
> command <B
CLASS="COMMAND"
>SET TIME ZONE</B
>
sets the time zone for the session.</P
></LI
></UL
><P>&#13;</P
><P
> If an invalid time zone is specified,
the time zone becomes GMT (on most systems anyway).</P
><H2
CLASS="SECT2"
><A
NAME="AEN1132"
>Date/Time Input</A
></H2
><P
>General-use date and time is input using a wide range of
styles, including ISO-compatible, <SPAN
CLASS="ACRONYM"
>SQL</SPAN
>-compatible, 
traditional <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> 
and other permutations of date and time. In cases where interpretation
can be ambiguous (quite possible with many traditional styles of date 
specification) <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> uses a style setting 
to resolve the ambiguity.</P
><P
>Most date and time types share code for data input. For those types
the input can have any of a wide variety of styles. For numeric date 
representations,
European and US conventions can differ, and the proper interpretation 
is obtained
by using the
<B
CLASS="COMMAND"
>set datestyle</B
>
command before entering data.
Note that the style setting does not preclude use of various styles for input;
 it is
used primarily to determine the output style and to resolve ambiguities.</P
><P
>The special values `current',
`infinity' and `-infinity' are provided.
`infinity' specifies a time later than any other valid time, and
`-infinity' specifies a time earlier than any other valid time.
`current' indicates that the current time should be
substituted whenever this value appears in a computation.

The strings
`now',
`today',
`yesterday',
`tomorrow',
and `epoch' can be used to specify
time values.  `now' means the current transaction time, and differs from
`current' in that the current time is immediately substituted
for it.  `epoch' means Jan 1 00:00:00 1970 GMT.</P
><P
><DIV
CLASS="TABLE"
><P
><B
>Table 5-11. <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> Date/Time Special Constants</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><TR
><TH
ALIGN="LEFT"
VALIGN="TOP"
>Constant</TH
><TH
ALIGN="LEFT"
VALIGN="TOP"
>Description</TH
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>current</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>Current transaction time, deferred</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>epoch</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>1970-01-01 00:00:00+00 (Unix system time zero)</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>infinity</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>Later than other valid times</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>-infinity</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>Earlier than other valid times</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>invalid</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>Illegal entry</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>now</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>Current transaction time</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>today</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>Midnight today</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>tomorrow</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>Midnight tomorrow</TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
>yesterday</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
>Midnight yesterday</TD
></TR
></TABLE
></DIV
></P
><H2
CLASS="SECT2"
><A
NAME="AEN1179"
>datetime</A
></H2
><P
>General-use date and time is input using a wide range of
styles, including ISO-compatible, <SPAN
CLASS="ACRONYM"
>SQL</SPAN
>-compatible, traditional
<SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> (see section on "absolute time")
and other permutations of date and time. Output styles can be ISO-compatible,
<SPAN
CLASS="ACRONYM"
>SQL</SPAN
>-compatible, or traditional 
<SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
>, with the default set to be compatible
with <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> v6.0.</P
><P
><SPAN
CLASS="TYPE"
>datetime</SPAN
> is specified using the following syntax:

<PRE
CLASS="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</PRE
>&#13;</P
><P
>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.</P
><H2
CLASS="SECT2"
><A
NAME="AEN1191"
><SPAN
CLASS="TYPE"
>timespan</SPAN
></A
></H2
><P
>General-use time span is input using a wide range of
syntaxes, including ISO-compatible, <SPAN
CLASS="ACRONYM"
>SQL</SPAN
>-compatible, 
traditional
<SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> (see section on "relative time")
 and other permutations of time span. Output formats can be ISO-compatible,
<SPAN
CLASS="ACRONYM"
>SQL</SPAN
>-compatible, or traditional 
<SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
>, 
with the default set to be <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
>-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.&#13;</P
><P
>Time span is specified with the following syntax:

<PRE
CLASS="PROGRAMLISTING"
>  Quantity Unit [Quantity Unit...] [Direction]
@ Quantity Unit [Direction]
where 	
    Quantity is ..., `-1', `0', `1', `2', ...
    Unit is `second', `minute', `hour', `day', `week', `month', `year',
    'decade', 'century', millenium', or abbreviations or plurals of these units.
    Direction is `ago'.</PRE
></P
><H2
CLASS="SECT2"
><A
NAME="AEN1202"
>abstime</A
></H2
><P
>Absolute time (<SPAN
CLASS="TYPE"
>abstime</SPAN
>) is a limited-range (+/- 68 years) and 
limited-precision (1 sec)
date data type. <SPAN
CLASS="TYPE"
>datetime</SPAN
> may be preferred, since it
covers a larger range with greater precision.&#13;</P
><P
>Absolute time is specified using the following syntax:

<PRE
CLASS="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</PRE
></P
><P
>Valid dates are from Dec 13 20:45:53 1901 GMT to Jan 19 03:14:04
2038 GMT. 

<BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Historical Note: </B
>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.</P
></BLOCKQUOTE
>

All special values allowed for <SPAN
CLASS="TYPE"
>datetime</SPAN
> are also 
allowed for "absolute time".</P
><H2
CLASS="SECT2"
><A
NAME="AEN1214"
>reltime</A
></H2
><P
>Relative time <SPAN
CLASS="TYPE"
>reltime</SPAN
> is a limited-range (+/- 68 years)
 and limited-precision (1 sec) time span data type.
<SPAN
CLASS="TYPE"
>timespan</SPAN
> 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 year to abstime today does not 
produce today's date one year from
now, but rather a date 360 days from today.</P
><P
><SPAN
CLASS="TYPE"
>reltime</SPAN
> shares input and output routines with the other 
time span types.
The section on <SPAN
CLASS="TYPE"
>timespan</SPAN
> covers this in more detail.</P
><H2
CLASS="SECT2"
><A
NAME="AEN1222"
><SPAN
CLASS="TYPE"
>timestamp</SPAN
></A
></H2
><P
>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 
<SPAN
CLASS="TYPE"
>datetime</SPAN
> type
and will move toward <SPAN
CLASS="ACRONYM"
>SQL92</SPAN
> compliance.</P
><P
><SPAN
CLASS="TYPE"
>timestamp</SPAN
> is specified using the same syntax as for 
<SPAN
CLASS="TYPE"
>datetime</SPAN
>.</P
><H2
CLASS="SECT2"
><A
NAME="AEN1231"
><SPAN
CLASS="TYPE"
>interval</SPAN
></A
></H2
><P
><SPAN
CLASS="TYPE"
>interval</SPAN
> is an <SPAN
CLASS="ACRONYM"
>SQL92</SPAN
> data type which is
currently mapped to the <SPAN
CLASS="TYPE"
>timespan</SPAN
> 
<SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> data type.</P
><H2
CLASS="SECT2"
><A
NAME="AEN1239"
>tinterval</A
></H2
><P
>Time ranges are specified as:

<PRE
CLASS="PROGRAMLISTING"
>[ 'abstime' 'abstime']
where
    abstime is a time in the absolute time format.</PRE
>

Special abstime values such as 
`current', `infinity' and `-infinity' can be used.</P
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="datatype833.html"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="user.html"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="datatype1243.html"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Character Types</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="datatype.html"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Boolean Type</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>