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.