|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>>>
18.104.22.168 Time Stamps
Valid input for the time stamp types consists of the concatenation
of a date and a time, followed by an optional time zone,
followed by an optional
BC can appear
before the time zone, but this is not the preferred ordering.)
1999-01-08 04:05:06 -8:00
are valid values, which follow the ISO 8601 standard. In addition, the common format:
January 8 04:05:06 1999 PST
The SQL standard differentiates
timestamp without time zone
timestamp with time zone literals by the presence of a
“+” or “-” symbol and time zone offset after
the time. Hence, according to the standard,
TIMESTAMP '2004-10-19 10:23:54'
timestamp without time zone, while
TIMESTAMP '2004-10-19 10:23:54+02'
timestamp with time zone.
PostgreSQL never examines the content of a
literal string before determining its type, and therefore will treat
both of the above as
timestamp without time zone. To
ensure that a literal is treated as
timestamp with time
zone, give it the correct explicit type:
TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
In a literal that has been determined to be
timestamp without time
zone, PostgreSQL will silently ignore
any time zone indication.
That is, the resulting value is derived from the date/time
fields in the input value, and is not adjusted for time zone.
timestamp with time zone, the internally stored
value is always in UTC (Universal
Coordinated Time, traditionally known as Greenwich Mean Time,
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
timestamp with time
zone value is output, it is always converted from UTC to the
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
AT TIME ZONE).
timestamp without time zone and
timestamp with time zone normally assume that the
timestamp without time zone value should be taken or given
timezone local time. A different time zone can
be specified for the conversion using
AT TIME ZONE.
|ISBN 9781906966041||The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference||See the print edition|