Time Zone Sort Order - Analytics Database - Teradata Vantage

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

Note how the values are displayed with the stored time zone information, but that the ordering is not immediately evident.

Now note how 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