PostgreSQL - SQL Language Reference

PostgreSQL Reference Manual - Volume 1 - SQL Language Reference
by The PostgreSQL Global Development Group
Paperback (6"x9"), 716 pages
ISBN 0954612027
RRP £32.00 ($49.95)

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

PostgreSQL Reference Manual
Volume 1

Buy the book here! >>>
learn more

7.9.3 AT TIME ZONE

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

Table 7-27: 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
`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 may be specified in any of the ways described in section 6.5.3 Time Zones.

Examples (supposing that 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.

PostgreSQL Reference Manual - Volume 1 - SQL Language Reference

See the print edition