- publishing free software manuals
The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference
by The PostgreSQL Global Development Group
Paperback (6"x9"), 454 pages
ISBN 9781906966041
RRP £14.95 ($19.95)

Sales of this book support the PostgreSQL project! Get a printed copy>>>

7.9.3 AT TIME ZONE

The AT TIME ZONE construct allows conversions of time stamps to different time zones. Table 7-28 shows its variants.

Table 7-28: AT TIME ZONE Variants
Expression Return Type Description
timestamp without time zone AT TIME ZONE zone timestamp with time zone Treat given time stamp without time zone as located in the specified time zone
timestamp with time zone AT TIME ZONE zone timestamp without time zone Convert given time stamp with time zone to the new time zone, with no time zone designation
time with time zone AT TIME ZONE zone time with time zone Convert given time with time zone to the new time zone

In these expressions, the desired time zone zone can be specified either as a text string (e.g., 'PST') or as an interval (e.g., INTERVAL '-08:00'). In the text case, a time zone name can be specified in any of the ways described in section 6.5.3 Time Zones.

Examples (assuming the local time zone is PST8PDT):

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

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

The first example takes a time stamp without time zone and interprets it as MST time (UTC-7), which is then converted to PST (UTC-8) for display. The second example takes a time stamp specified in EST (UTC-5) and converts it to local time in MST (UTC-7).

The function timezone(zone, timestamp) is equivalent to the SQL-conforming construct timestamp AT TIME ZONE zone.

ISBN 9781906966041The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language ReferenceSee the print edition