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, 0 to 13
for the time 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.
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 type can be specified as time or
as time without time zone. The optional precision
p should be between 0 and 13, and
defaults to the precision of the input time literal.
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.
interval values can be written with the following syntax:
Quantity Unit [Quantity Unit...] [Direction]
@ Quantity Unit [Quantity Unit...] [Direction]
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.
Table 5-14. Special Date/Time Inputs
Input string
Description
epoch
1970-01-01 00:00:00+00 (Unix system time zero)
infinity
later than all other timestamps (not available for
type date)
-infinity
earlier than all other timestamps (not available for
type date)
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).
Refer to Appendix A for a list of
available time zones.
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.
