17.10 - Time Zone Sort Order - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Date and Time Functions and Expressions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1211-171K
Language
English (United States)

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