TIMESTAMP WITH TIME ZONE data type

The TIMESTAMP WITH TIME ZONE data type stores a point in time with a time zone offset.

Syntax

TIMESTAMP WITH TIME ZONE

The TIMESTAMP WITH TIME ZONE value contains the year, month, day, hour, minute, second, fraction of a second, and number of minutes before or after Coordinated Universal Time (UTC). The fraction is stored to six decimal places.

The format in which TIMESTAMP WITH TIME ZONE values are retrieved as strings by applications is controlled by the timestamp_with_time_zone_format creation parameter. For example, the TIMESTAMP WITH TIME ZONE value 2010/04/01T23:59:59.999999-6:00 can be returned to an application as 2010/04/01 23:59:59 -06:00 or as April 1, 2010 23:59:59.999999 -06:00, depending on the timestamp_with_time_zone_format creation parameter.

A TIMESTAMP WITH TIME ZONE value requires 10 bytes of storage.

Although the range of possible dates for the TIMESTAMP WITH TIME ZONE data type is the same as the DATE type (covering years 0001 to 9999), the useful range of TIMESTAMP WITH TIME ZONE date types is from 1600-02-28 23:59:59 to 7911-01-01 00:00:00. Before and after this range, the hours and minutes portion of the TIMESTAMP WITH TIME ZONE value is not retained.

Two TIMESTAMP WITH TIME ZONE values are considered identical when they represent the same instant in UTC, regardless of the TIME ZONE offset applied. For example, the following statement returns Yes because the results are considered identical:

SELECT IF CAST('2009-07-15 08:00:00 -08:00' AS TIMESTAMP WITH TIME ZONE) = 
    CAST('2009-07-15 11:00:00 -05:00' AS TIMESTAMP WITH TIME ZONE)
    THEN 'Yes'
    ELSE 'No'
END IF;

If you omit the time zone offset from a TIMESTAMP WITH TIME ZONE value, it defaults to the current UTC offset of the client regardless of whether the timestamp represents a date and time in standard time or daylight time. For example, if the client is located in the Eastern Standard time zone and executes the following statement while daylight time is in effect, then a timestamp with a time zone appropriate for the Atlantic Standard time zone (-4 hours from UTC) is returned.

SELECT CAST('2009/01/30 12:34:55' AS TIMESTAMP WITH TIME ZONE)

Comparing TIMESTAMP WITH TIME ZONE with other data types The comparison of TIMESTAMP WITH TIME ZONE values with timestamps without time zones is not recommended because the default time zone offset of the client varies with the geographic location of the client and with the time of the year.
Converting to or from TIMESTAMP WITH TIME ZONE When a TIMESTAMP value is converted to TIMESTAMP WITH TIME ZONE, the client's time zone is used for the time zone offset in the result. In other words, the value is considered to be local to the connection. When a TIMESTAMP WITH TIME ZONE value is converted to TIMESTAMP, the offset is discarded. Conversions to or from types other than strings, date, or date-time types is not supported.

TIMESTAMP WITH TIME ZONE data type

Syntax

Remarks

See also