The PostgreSQL 9.0 Reference Manual - Volume 1A

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>>>

PostgreSQL 9.0 Reference Manual
Volume 1A

Buy the book here! >>>
support free software

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.

The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference

See the print edition