Time zones are ordered chronologically, using the same time zone.
Examples
Consider the following examples using ordered SELECT statements on a table having a column with type TIMESTAMP(0) WITH TIME ZONE.
The identical ordering demonstrated in these ORDER BY SELECTs applies to all time zone comparison operations.
SELECT f1 TIMESTAMPFIELD FROM timestwz ORDER BY f1;
This statement returns the following results table.
TIMESTAMPFIELD ------------------------- 1997-10-07 15:43:00+08:00 1997-10-07 15:43:00-00:00 1997-10-07 15:47:52-08:00
The values are displayed with the stored time zone information, but that the ordering is not immediately evident.
Normalizing the time zones by means of a CAST function indicates chronological ordering explicitly.
SELECT CAST(f1 AS TIMESTAMP(0)) TIMESTAMP_NORMALIZED FROM timestwz ORDER BY f1;
This statement returns the following results table.
TIMESTAMP_NORMALIZED ------------------- 1997-10-06 23:43:00 1997-10-07 07:43:00 1997-10-07 15:45:52
While the ordering is the same as for the previous query, the display of TIMESTAMP values has been normalized to the time zone in effect for the session, which is '-08:00'.
A different treatment of the time zones, this time to reflect local time, indicates the same chronological ordering but from a different perspective.
SELECT f1 AT LOCAL LOCALIZED FROM timestwz ORDER BY f1;
This statement returns the following results table.
LOCALIZED ------------------------- 1997-10-06 23:43:00-08:00 1997-10-07 07:43:00-08:00 1997-10-07 15:45:52-08:00