Time Zone Sort Order - Advanced SQL Engine - Teradata Database

SQL Date and Time Functions and Expressions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
dzx1591742169550.ditamap
dita:ditavalPath
dzx1591742169550.ditaval
dita:id
B035-1211
lifecycle
previous
Product Category
Teradata Vantage™

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

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