Product Documentation

Appearance

AT TIME ZONE ​

The AT TIME ZONE construct operates on timestamps (with and without time zones) and returns appropriate values based on standard UTC offsets. AT TIME ZONE does not support the TIME data type.

expression AT TIME ZONE zone

The behavior depends on the input type of the expression:

  • TIMESTAMP (without time zone): Interpret the given value as located in the named time zone, but return the corresponding value in the time zone that is set for the client session (or database). For example, if the client time zone is PST and AT TIME ZONE specifies MST, return the MST value -1 hour.
  • TIMESTAMPTZ: Convert the given value to its equivalent value in the named time zone, then return the converted value as a timestamp without a time zone designation. For example, if the client time zone is PST and AT TIME ZONE specifies MST, return the PST value +1 hour.

The expression must be a TIMESTAMP or TIMESTAMPTZ expression (such as a column name or a literal value). The zone must be a valid abbreviation, such as GMT or PST, or a long-form time zone name such as US/Pacific or Asia/Shanghai.

When the input type is TIMESTAMP, the return type is TIMESTAMPTZ. When the input type is TIMESTAMPTZ, the return type is TIMESTAMP.

You can specify the time zone either as a text string, such as 'GMT', or as an interval, such as INTERVAL '-07:00').

For example, assume the times table contains a TIMESTAMP column and a TIMESTAMPTZ column with the following values.

premdb=# \d times
                 Table "public.times"
   Column    |            Type             | Modifiers 
--------------+-----------------------------+-----------
 start_time   | timestamp without time zone | 
 start_timetz | timestamp with time zone    | 

Distribution: Hash (start_time)

premdb=# select * from times;
    start_time      |      start_timetz      
---------------------+------------------------
 2017-03-02 11:00:00 | 2017-03-02 11:00:00-08
(1 row)

The client time zone that is in effect for these examples is PST.

In the first example, the start_time column is treated as a GMT timestamp, then converted to PST for display (11AM GMT is equivalent to 3AM PST).

premdb=# select start_time, start_time at time zone 'GMT' from times;
    start_time      |        timezone        
---------------------+------------------------
 2017-03-02 11:00:00 | 2017-03-02 03:00:00-08
(1 row)

The second example takes a TIMESTAMPTZ value specified in PST and converts it to local time in GMT (11AM PST is equivalent to 7PM GMT).

premdb=# select start_timetz, start_timetz at time zone 'GMT' from times;
     start_timetz      |      timezone       
------------------------+---------------------
 2017-03-02 11:00:00-08 | 2017-03-02 19:00:00
(1 row)

The following example selects the matchday column from the match table. The timestamps are treated as GMT values and the equivalent PST values that are displayed subtract 7 or 8 hours, depending on the time of year:

premdb=# select matchday, matchday at time zone 'GMT' 
from match where htid=48 and atid=57 order by matchday;
     matchday       |        timezone        
---------------------+------------------------
 2004-04-12 00:00:00 | 2004-04-11 17:00:00-07
 2009-12-05 00:00:00 | 2009-12-04 16:00:00-08
 2010-11-13 00:00:00 | 2010-11-12 16:00:00-08
 2012-03-31 00:00:00 | 2012-03-30 17:00:00-07
(4 rows)

Parent topic:Datetime Functions