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 (UTC) time.

The fraction is stored to 6 decimal places. A TIMESTAMP WITH TIME ZONE value requires 10 bytes of storage.

You can use a T between the date and time. You can use a Z to indicate a time zone offset of +00:00 (UTC).

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 time portion of the TIMESTAMP WITH TIME ZONE may be incomplete.

Do not use TIMESTAMP WITH TIME ZONE for computed columns or in materialized views because the value of the governing time_zone_adjustment option will vary between connections based on their location and the time of year.

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:

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 
SELECT  'Yes'
ELSE
SELECT '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) will be returned.

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

• timestamp_with_time_zone_format option
• DATE data type
• DATETIMEOFFSET data type
• TIME data type
• TIMESTAMP data type
• Date and time functions
• Comparing dates and times