Example

Teradata Vantage™ Data Types and Literals

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1143-162K

A single column table has three rows of type TIMESTAMP(0) WITH TIME ZONE.

A query that requests the field values and CASTs them as DATE is performed during a session that has its Local Time Zone defined as -’08:00’.

The results table is as follows.

TimeStampWithTimeZone     CastAsDate
-------------------------------------------------
1997-10-07 15:43:00+08:00 1997-10-06
1997-10-07 15:47:52-08:00 1997-10-07
1997-10-07 15:43:00-00:00 1997-10-07

Notice that the difference between the stored Time Zone and the Local Time Zone is 16 hours in the first row, but at the same time the TimeStamp value is 15:43, which is less than 16.

This puzzling result can be clarified using a similar query that casts TIMESTAMP(0) WITH TIME ZONE as TIMESTAMP(0), omitting the Time Zone information.

The results table for this query is as follows.

TimeStampWithTimeZone     CastAsTimeStamp
-------------------------------------------------
1997-10-07 15:43:00+08:00 1997-10-06 23:43:00
1997-10-07 15:47:52-08:00 1997-10-07 15:47:52
1997-10-07 15:43:00-00:00 1997-10-07 07:43:00

After the CAST, the values are all displayed at Local Time Zone, and the value in the first row indicates that the 16 hour adjustment rolled the date back 1, to a time near the end of that date.