Product DocumentationAppearance
TIMESTAMP WITH TIME ZONE
The TIMESTAMP WITH TIME ZONE (or TIMESTAMPTZ) data type stores 8-byte date values that include timestamp and time zone information in UTC format.
You cannot define a TIMESTAMPTZ column with a specific precision for fractional seconds other than 6. A valid timestamp with time zone value includes up to 6 digits of precision. If additional fractional digits exist in input values, the value is rounded up or down, as appropriate.
For example, the following CREATE TABLE statements are allowed and produce the same table:
premdb=# create table timestamptz6(c1 timestamptz(6));
CREATE TABLEpremdb=# create table timestamptz0(c1 timestamptz);
CREATE TABLEThe following CREATE TABLE statement is disallowed:
premdb=# create table timestamptz4(c1 timestamptz(4));
ERROR: TIMESTAMPTZ with precision 4 is not allowed, only 6 is supportedThe range of valid dates in timestamps is from 01-01-0001 to 12-31-9999.
The following examples show the behavior when values are inserted or loaded into a TIMESTAMPTZ column, and the current time zone for the database session is US/Pacific (or 07).
| Inserted Value | Stored Value | Notes |
|---|---|---|
2016-05-10 18:20:18.674 UTC | 2016-05-10 11:20:18.674-07 | The time zone identifier is returned as 07. A 7-hour offset is required for the timestamp itself (11:20:18.674). |
2016-05-10 18:20:18.674 +1:00 | 2016-05-10 10:20:18.674-07 | The time zone identifier is returned as 07 (the time zone of the current session). The timestamp is converted to UTC +1:00 (an 8-hour offset). |
Note: The ybload utility supports UTC time zones, as shown in these examples, and IANA time zone names, such as US/Pacific and Europe/Dublin. Yellowbrick recommends that you export timestamptz data in UTC format before running a bulk load. If you use a SQL INSERT command to load data, you can also use other standard time zone abbreviations, such as PST and PDT; ybload does not support these abbreviations.
Parent topic:SQL Data Types