<!--

$Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.108 2002/11/15 03:11:15 momjian Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.109 2002/11/21 23:31:20 tgl Exp $

-->

<chapter id="datatype">

<secondary>data type</secondary>

</indexterm>

<indexterm>

<primary>timestamp with time zone</primary>

<secondary>data type</secondary>

</indexterm>

<indexterm>

<primary>timestamp without time zone</primary>

<secondary>data type</secondary>

</indexterm>

<para>

Time stamp types exist as <type>timestamp [

(<replaceable>p</replaceable>) ]</type>, <type>timestamp [

The time stamp types are <type>timestamp [

(<replaceable>p</replaceable>) ] without time zone</type> and

<type>timestamp [ (<replaceable>p</replaceable>) ] without time

zone</type>. A plain <type>timestamp</type> is equivalent to

<type>timestamp without timezone</type>.

<type>timestamp [ (<replaceable>p</replaceable>) ] with time

zone</type>. Writing just <type>timestamp</type> is equivalent to

<type>timestamp without time zone</type>.

</para>

<note>

<para>

Prior to <productname>PostgreSQL</productname> 7.3, writing just

<type>timestamp</type> was equivalent to <type>timestamp with time

zone</type>. This was changed for SQL spec compliance.

</para>

</note>

<para>

Valid input for the time stamp types consists of a concatenation

<para>

For <type>timestamp without time zone</type>, any explicit time

zone specified in the input is silentlyswallowed. That is, the

zone specified in the input is silentlyignored. That is, the

resulting date/time value is derived from the explicit date/time

fields in the input value, and is not adjusted for time zone.

</para>

<para>

For <type>timestamp with time zone</type>, the internally stored

value is always in UTC (GMT). An input value that has an explicit

time zone specified is converted to UTC using the appropriate offset

for that time zone. If no time zone is stated in the input string,

then it is assumed to be in the time zone indicated by the system's

<varname>TimeZone</> parameter, and is converted to UTC using the

offset for the <varname>TimeZone</> zone.

</para>

<para>

When a <type>timestamp with time

zone</type> value is output, it is always converted from UTC to the

current <varname>TimeZone</> zone, and displayed as local time in that

zone. To see the time in another time zone, either change

<varname>TimeZone</> or use the <literal>AT TIME ZONE</> construct

(see <xref linkend="functions-datetime-zoneconvert">).

</para>

<para>

Conversions between <type>timestamp without time zone</type> and

<type>timestamp with time zone</type> normally assume that the

<type>timestamp without time zone</type> value should be taken or given

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">

<para>

The following <acronym>SQL</acronym>-compatible functions can be

used as date or time

input for the corresponding data type: <literal>CURRENT_DATE</literal>,

values for the corresponding data type: <literal>CURRENT_DATE</literal>,

<literal>CURRENT_TIME</literal>,

<literal>CURRENT_TIMESTAMP</literal>. The latter two accept an

optional precision specification. (See also <xref linkend="functions-datetime">.)

optional precision specification. (See also <xref linkend="functions-datetime-current">.)

</para>

<para>

<productname>PostgreSQL</productname> also supports several

special constants for convenience, shown in <xref

linkend="datatype-datetime-special-table">.

special date/time input values for convenience, as shown in <xref

linkend="datatype-datetime-special-table">. The values

<literal>infinity</literal> and <literal>-infinity</literal>

are specially represented inside the system and will be displayed

the same way; but the others are simply notational shorthands

that will be converted to ordinary date/time values when read.

</para>

<table id="datatype-datetime-special-table">

<title>Special Date/TimeConstants</title>

<title>Special Date/TimeInputs</title>

<tgroup cols="2">

<thead>

<row>

<entry>Constant</entry>

<entry>Input string</entry>

<entry>Description</entry>

</row>

</thead>

</row>

<row>

<entry><literal>infinity</literal></entry>

<entry>later than other valid times</entry>

<entry>later than all other timestamps (not available for

type <type>date</>)</entry>

</row>

<row>

<entry><literal>-infinity</literal></entry>

<entry>earlier than other valid times</entry>

</row>

<row>

<entry><literal>invalid</literal></entry>

<entry>illegal entry</entry>

<entry>earlier than all other timestamps (not available for

type <type>date</>)</entry>

</row>

<row>

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

</para>

<para>

There are several ways toaffect the time-zonebehavior:

There are several ways toselect the timezoneused by the server:

<itemizedlist>

<listitem>

<para>

The <envar>TZ</envar> environment variable on the server host

is used by the server as the default time zone.

is used by the server as the default time zone, if no other is

specified.

</para>

</listitem>

<listitem>

<para>

The <varname>timezone</varname> configuration parameter can be

set in <filename>postgresql.conf</>.

</para>

</listitem>

sets the time zone for the session.

</para>

</listitem>

<listitem>

<para>

The construct

<programlisting>

<replaceable>timestamp</replaceable> AT TIME ZONE '<replaceable>zone</replaceable>'

</programlisting>

where <replaceable>zone</replaceable> can be specified as a

text time zone (e.g., <literal>'PST'</literal>) or as an

interval (e.g., <literal>INTERVAL '-08:00'</literal>).

</para>

</listitem>

</itemizedlist>

</para>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.130 2002/11/11 20:14:02 petere Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.131 2002/11/21 23:31:20 tgl Exp $

PostgreSQL documentation

-->

<literal>*</literal>, etc.). For formatting functions, refer to

<xref linkend="functions-formatting">. You should be familiar with

the background information on date/time data types (see <xref

linkend="datatype-datetime">). The date/time operators described

below behave similarly for types involving time zones as well as

those without.

linkend="datatype-datetime">).

</para>

<para>

All the functions and operators described below that take time or timestamp

inputs actually come in two variants: one that takes time or timestamp

with time zone, and one that takes time or timestamp without time zone.

For brevity, these variants are not shown separately.

</para>

<table id="operators-datetime-table">

<row>

<entry><function>now</function>()</entry>

<entry><type>timestamp</type></entry>

<entry><type>timestamp with time zone</type></entry>

<entry>Current date and time (equivalent to

<function>current_timestamp</function>); see <xref

linkend="functions-datetime-current">

<listitem>

<para>

For <type>date</type> and <type>timestamp</type> values, the

number of seconds since 1970-01-01 00:00:00-00 (Result may be

negative.);for <type>interval</type> values, the total number

number of seconds since 1970-01-01 00:00:00-00 (can be negative);

for <type>interval</type> values, the total number

of seconds in the interval

</para>

<para>

The <function>date_part</function> function is modeled on the traditional

<productname>Ingres</productname> equivalent to the

<acronym>SQL</acronym>-function <function>extract</function>:

<acronym>SQL</acronym>-standardfunction <function>extract</function>:

<synopsis>

date_part('<replaceable>field</replaceable>', <replaceable>source</replaceable>)

</synopsis>

Note that here the <replaceable>field</replaceable>value needs to

be a string. The valid field values for

Note that here the <replaceable>field</replaceable>parameter needs to

be a string value, not a name. The valid field values for

<function>date_part</function> are the same as for

<function>extract</function>.

</para>

</para>

</sect2>

<sect2 id="functions-datetime-zoneconvert">

<title><function>AT TIME ZONE</function></title>

<indexterm>

<primary>timezone</primary>

<secondary>conversion</secondary>

</indexterm>

<para>

The <function>AT TIME ZONE</function> construct allows conversions

of timestamps to different timezones.

</para>

<table id="functions-datetime-zoneconvert-table">

<title>AT TIME ZONE Variants</title>

<tgroup cols="3">

<thead>

<row>

<entry>Expression</entry>

<entry>Returns</entry>

<entry>Description</entry>

</row>

</thead>

<tbody>

<row>

<entry>

<type>timestamp without time zone</type>

<literal>AT TIME ZONE</literal>

<replaceable>zone</>

</entry>

<entry><type>timestamp with time zone</type></entry>

<entry>Convert local time in given timezone to UTC</entry>

</row>

<row>

<entry>

<type>timestamp with time zone</type>

<literal>AT TIME ZONE</literal>

<replaceable>zone</>

</entry>

<entry><type>timestamp without time zone</type></entry>

<entry>Convert UTC to local time in given timezone</entry>

</row>

<row>

<entry>

<type>time with time zone</type>

<literal>AT TIME ZONE</literal>

<replaceable>zone</>

</entry>

<entry><type>time with time zone</type></entry>

<entry>Convert local time across timezones</entry>

</row>

</tbody>

</tgroup>

</table>

<para>

In these expressions, the desired time <replaceable>zone</> can be

specified either as a text string (e.g., <literal>'PST'</literal>)

or as an interval (e.g., <literal>INTERVAL '-08:00'</literal>).

</para>

<para>

Examples (supposing that <varname>TimeZone</> is <literal>PST8PDT</>):

<screen>

SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';

<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 19:38:40-08</computeroutput>

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';

<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 18:38:40</computeroutput>

</screen>

The first example takes a zone-less timestamp and interprets it as MST time

(GMT-7) to produce a UTC timestamp, which is then rotated to PST (GMT-8)

for display. The second example takes a timestamp specified in EST

(GMT-5) and converts it to local time in MST (GMT-7).

</para>

<para>

The function <function>timezone</function>(<replaceable>zone</>,

<replaceable>timestamp</>) is equivalent to the SQL-compliant construct

<replaceable>timestamp</> <literal>AT TIME ZONE</literal>

<replaceable>zone</>.

</para>

</sect2>

<sect2 id="functions-datetime-current">

<title>Current Date/Time</title>

LOCALTIME ( <replaceable>precision</replaceable> )

LOCALTIMESTAMP ( <replaceable>precision</replaceable> )

</synopsis>

</para>

<para>

<function>CURRENT_TIME</function> and

<function>CURRENT_TIMESTAMP</function> deliver values with time zone;

<function>LOCALTIME</function> and

<function>LOCALTIMESTAMP</function> deliver values without time zone.

</para>

<para>

<function>CURRENT_TIME</function>,

<function>CURRENT_TIMESTAMP</function>,

<function>LOCALTIME</function>, and