PostgreSQL supports the full
set of SQL date and time
types, shown in Table
5-9.

Table 5-9. Date/Time Types

Type

Description

Storage

Earliest

Latest

Resolution

timestamp [ (p) ] [ without time zone
]

both date and time

8 bytes

4713 BC

AD 1465001

1 microsecond / 14 digits

timestamp [ (p) ] with time zone

both date and time

8 bytes

4713 BC

AD 1465001

1 microsecond / 14 digits

interval [ (p) ]

time intervals

12 bytes

-178000000 years

178000000 years

1 microsecond

date

dates only

4 bytes

4713 BC

32767 AD

1 day

time [ (p) ] [ without time zone
]

times of day only

8 bytes

00:00:00.00

23:59:59.99

1 microsecond

time [ (p) ] with time zone

times of day only

12 bytes

00:00:00.00+12

23:59:59.99-12

1 microsecond

time, timestamp,
and interval accept an optional precision
value p which specifies the
number of fractional digits retained in the seconds field. By
default, there is no explicit bound on precision. The allowed
range of p is from 0 to 6 for the
timestamp and interval types.

Note: When timestamp values
are stored as double precision floating-point numbers
(currently the default), the effective limit of precision may
be less than 6, since timestamp values are stored as seconds
since 2000-01-01. Microsecond precision is achieved for dates
within a few years of 2000-01-01, but the precision degrades
for dates further away. When timestamps are stored as
eight-byte integers (a compile-time option), microsecond
precision is available over the full range of values.

For the time types, the allowed range of
p is from 0 to 6 when eight-byte
integer storage is used, or from 0 to 10 when floating-point
storage is used.

Time zones, and time-zone conventions, are influenced by
political decisions, not just earth geometry. Time zones around
the world became somewhat standardized during the 1900's, but
continue to be prone to arbitrary changes. PostgreSQL uses your operating system's
underlying features to provide output time-zone support, and
these systems usually contain information for only the time
period 1902 through 2038 (corresponding to the full range of
conventional Unix system time). timestamp with
time zone and time with time zone will
use time zone information only within that year range, and assume
that times outside that range are in UTC.

The type time with time zone is defined
by the SQL standard, but the definition exhibits properties which
lead to questionable usefulness. In most cases, a combination of
date, time,
timestamp without time zone and timestamp with time zone should provide a complete
range of date/time functionality required by any application.

The types abstime and reltime are lower precision types which are used
internally. You are discouraged from using these types in new
applications and are encouraged to move any old ones over when
appropriate. Any or all of these internal types might disappear
in a future release.

Date and time input is accepted in almost any reasonable
format, including ISO 8601,
SQL-compatible, traditional
PostgreSQL, and others. For
some formats, ordering of month and day in date input can be
ambiguous and there is support for specifying the expected
ordering of these fields. The command SET
DateStyle TO 'US' or SET DateStyle TO
'NonEuropean' specifies the variant "month before day", the command SET DateStyle TO 'European' sets the variant
"day before month".

PostgreSQL is more flexible
in handling date/time than the SQL standard requires. See Appendix A for the exact parsing
rules of date/time input and for the recognized text fields
including months, days of the week, and time zones.

Remember that any date or time literal input needs to be
enclosed in single quotes, like text strings. Refer to Section
1.1.2.4 for more information. SQL requires the following syntax

type [ (p) ] 'value'

where p in the optional
precision specification is an integer corresponding to the
number of fractional digits in the seconds field. Precision can
be specified for time, timestamp, and interval
types.

The time stamp types are timestamp [
(p) ] without time zone
and timestamp [ (p) ] with time zone. Writing just
timestamp is equivalent to timestamp without time zone.

Note: Prior to PostgreSQL 7.3, writing just
timestamp was equivalent to
timestamp with time zone. This was
changed for SQL spec compliance.

Valid input for the time stamp types consists of a
concatenation of a date and a time, followed by an optional
AD or BC,
followed by an optional time zone. (See Table
5-13.) Thus

1999-01-08 04:05:06

and

1999-01-08 04:05:06 -8:00

are valid values, which follow the ISO 8601 standard. In addition, the
wide-spread format

January 8 04:05:06 1999 PST

is supported.

The optional precision p
should be between 0 and 6, and defaults to the precision of
the input timestamp literal.

For timestamp without time zone, any
explicit time zone specified in the input is silently
ignored. 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.

For timestamp with time zone, 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 TimeZone parameter, and is converted to UTC
using the offset for the TimeZone zone.

When a timestamp with time zone
value is output, it is always converted from UTC to the
current TimeZone zone, and
displayed as local time in that zone. To see the time in
another time zone, either change TimeZone or use the AT
TIME ZONE construct (see Section
6.8.3).

Conversions between timestamp without
time zone and timestamp with time
zone normally assume that the timestamp
without time zone value should be taken or given as
TimeZone local time. A different
zone reference can be specified for the conversion using
AT TIME ZONE.

where: Quantity is a number
(possibly signed), Unit is
second, minute, hour,
day, week,
month, year, decade,
century, millennium, or abbreviations or plurals of
these units; Direction can be
ago or empty. The at sign
(@) is optional noise. The amounts
of different units are implicitly added up with appropriate
sign accounting.

Quantities of days, hours, minutes, and seconds can be
specified without explicit unit markings. For example,
'1 12:59:10' is read the same as
'1 day 12 hours 59 min 10 sec'.

The optional precision p
should be between 0 and 6, and defaults to the precision of
the input literal.

The following SQL-compatible functions can be used as
date or time values for the corresponding data type:
CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP. The latter two accept an
optional precision specification. (See also Section
6.8.4.)

PostgreSQL also supports
several special date/time input values for convenience, as
shown in Table
5-14. The values infinity and
-infinity 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.

Output formats can be set to one of the four styles ISO
8601, SQL (Ingres),
traditional PostgreSQL, and German, using the SET DateStyle. The default is the
ISO format. (The
SQL standard requires the
use of the ISO 8601 format. The name of the "SQL" output format is a historical accident.)
Table
5-15 shows examples of each output style. The output of the
date and time types
is of course only the date or time part in accordance with the
given examples.

Table 5-15. Date/Time Output Styles

Style Specification

Description

Example

ISO

ISO 8601/SQL standard

1997-12-17 07:37:16-08

SQL

traditional style

12/17/1997 07:37:16.00 PST

PostgreSQL

original style

Wed Dec 17 07:37:16 1997 PST

German

regional style

17.12.1997 07:37:16.00 PST

The SQL style has
European and non-European (U.S.) variants, which determines
whether month follows day or vice versa. (See Section
5.5.1 for how this setting also affects interpretation of
input values.) Table
5-16 shows an example.

Table 5-16. Date Order Conventions

Style Specification

Description

Example

European

day/month/year

17/12/1997 15:37:16.00 MET

US

month/day/year

12/17/1997 07:37:16.00 PST

interval output looks like the input
format, except that units like week or
century are converted to years and
days. In ISO mode the output looks like

[ Quantity Units [ ... ] ] [ Days ] Hours:Minutes [ ago ]

The date/time styles can be selected by the user using the
SET DATESTYLE command, the
datestyle parameter in the
postgresql.conf configuration file,
and the PGDATESTYLE environment variable
on the server or client. The formatting function to_char (see Section 6.7) is also available
as a more flexible way to format the date/time output.

PostgreSQL endeavors to be
compatible with the SQL
standard definitions for typical usage. However, the
SQL standard has an odd mix
of date and time types and capabilities. Two obvious problems
are:

Although the date type does not
have an associated time zone, the time type can. 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-saving time boundaries.

The default time zone is specified as a constant integer
offset from GMT/UTC. It is not possible to adapt to
daylight-saving time when doing date/time arithmetic across
DST boundaries.

To address these difficulties, we recommend using date/time
types that contain both date and time when using time zones. We
recommend not using
the type time with time zone (though it
is supported by PostgreSQL for
legacy applications and for compatibility with other
SQL implementations).
PostgreSQL assumes your local
time zone 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-saving time and other expected behavior.

PostgreSQL obtains
time-zone support from the underlying operating system for
dates between 1902 and 2038 (near the typical date limits for
Unix-style systems). Outside of this range, all dates are
assumed to be specified and used in Universal Coordinated Time
(UTC).

All dates and times are stored internally in UTC, traditionally 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.

There are several ways to select the time zone used by the
server:

The TZ environment variable on
the server host is used by the server as the default time
zone, if no other is specified.

The timezone configuration
parameter can be set in postgresql.conf.

The PGTZ environment variable, if
set at the client, is used by libpq applications to send a
SET TIME ZONE command to the
server upon connection.

The SQL command
SET TIME ZONE sets the time zone
for the session.

Note: If an invalid time zone is specified, the
time zone becomes UTC
(on most systems anyway).

PostgreSQL uses Julian
dates for all date/time calculations. They have the nice
property of correctly predicting/calculating any date more
recent than 4713 BC to far into the future, using the
assumption that the length of the year is 365.2425 days.

Date conventions before the 19th century make for
interesting reading, but are not consistent enough to warrant
coding into a date/time handler.