|The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference
by The PostgreSQL Global Development Group
Paperback (6"x9"), 454 pages
RRP £14.95 ($19.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
6.5.3 Time Zones
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, particularly with
respect to daylight-savings rules.
PostgreSQL uses the widely-used
zoneinfo time zone database for information about
historical time zone rules. For times in the future, the assumption
is that the latest known rules for a given time zone will
continue to be observed indefinitely far into the future.
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:
datetype cannot have an associated time zone, the
timetype can. Time zones in the real world have little meaning unless associated with a date as well as a time, since the offset can vary through the year with daylight-saving time boundaries.
- The default time zone is specified as a constant numeric offset from UTC. It is therefore impossible 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
do not recommend using the type
time zone (though it is supported by
PostgreSQL for legacy applications and
for compliance with the SQL standard).
your local time zone for any type containing only date or time.
All timezone-aware dates and times are stored internally in
UTC. They are converted to local time
in the zone specified by the
parameter before being displayed to the client.
PostgreSQL allows you to specify time zones in three different forms:
A full time zone name, for example
America/New_York. The recognized time zone names are listed in the
pg_timezone_namesview (see pg_timezone_names). PostgreSQL uses the widely-used
zoneinfotime zone data for this purpose, so the same names are also recognized by much other software.
A time zone abbreviation, for example
PST. Such a specification merely defines a particular offset from UTC, in contrast to full time zone names which can imply a set of daylight savings transition-date rules as well. The recognized abbreviations are listed in the
pg_timezone_abbrevsview (see pg_timezone_abbrevs). You cannot set the configuration parameters
log_timezoneto a time zone abbreviation, but you can use abbreviations in date/time input values and with the
AT TIME ZONEoperator.
In addition to the timezone names and abbreviations,
PostgreSQL will accept POSIX-style time zone
specifications of the form STDoffset or
STD is a zone abbreviation, offset is a
numeric offset in hours west from UTC, and DST is an
optional daylight-savings zone abbreviation, assumed to stand for one
hour ahead of the given offset. For example, if
EST5EDTwere not already a recognized zone name, it would be accepted and would be functionally equivalent to United States East Coast time. When a daylight-savings zone name is present, it is assumed to be used according to the same daylight-savings transition rules used in the
zoneinfotime zone database's ‘posixrules’ entry. In a standard PostgreSQL installation, ‘posixrules’ is the same as
US/Eastern, so that POSIX-style time zone specifications follow USA daylight-savings rules. If needed, you can adjust this behavior by replacing the ‘posixrules’ file.
In short, this is the difference between abbreviations and full names: abbreviations always represent a fixed offset from UTC, whereas most of the full names imply a local daylight-savings time rule, and so have two possible UTC offsets.
One should be wary that the POSIX-style time zone feature can
lead to silently accepting bogus input, since there is no check on the
reasonableness of the zone abbreviations. For example,
TIMEZONE TO FOOBAR0 will work, leaving the system effectively using
a rather peculiar abbreviation for UTC.
Another issue to keep in mind is that in POSIX time zone names,
positive offsets are used for locations west of Greenwich.
Everywhere else, PostgreSQL follows the
ISO-8601 convention that positive timezone offsets are east
In all cases, timezone names are recognized case-insensitively. (This is a change from PostgreSQL versions prior to 8.2, which were case-sensitive in some contexts but not others.)
Neither full names nor abbreviations are hard-wired into the server; they are obtained from configuration files stored under ‘.../share/timezone/’ and ‘.../share/timezonesets/’ of the installation directory (see section 12.8 Date/Time Configuration Files).
timezone configuration parameter can
be set in the file ‘postgresql.conf’, or in any of the
other standard ways described in Volume 3: Server Configuration.
There are also several special ways to set it:
timezoneis not specified in ‘postgresql.conf’ or as a server command-line option, the server attempts to use the value of the
TZenvironment variable as the default time zone. If
TZis not defined or is not any of the time zone names known to PostgreSQL, the server attempts to determine the operating system's default time zone by checking the behavior of the C library function
localtime(). The default time zone is selected as the closest match among PostgreSQL's known time zones. (These rules are also used to choose the default value of
log_timezone, if not specified.)
The SQL command
SET TIME ZONEsets the time zone for the session. This is an alternative spelling of
SET TIMEZONE TOwith a more SQL-spec-compatible syntax.
PGTZenvironment variable is used by libpq clients to send a
SET TIME ZONEcommand to the server upon connection.
|ISBN 9781906966041||The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference||See the print edition|