Unexpected Sort Order When Querying DATE Data Type Columns - Analytics Database - Teradata Vantage

SQL Data Manipulation Language
Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-12-13
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

Using the ORDER BY clause to query columns with the DATE data type may yield results in which the order of the returned values seem to be out of order.

The factors that contribute to this type of unexpected result are:
  • DATE time values are stored by the system on disk in UTC time, not local time.
  • UTC time values are used to sort the column values, not the local time values.
  • DATE time values are affected by the system time zone setting, which can be set using TimeZoneString or the DBSControl general field 16.
  • DATE time values that cross the date boundary are adjusted based on the time zone offset for the system time zone (the time zone offset varies depending on the time zone).

Example of Unexpected Sort Order

This example shows a result set of TIME data type column values that do not seem to be sorted in the order specified in the ORDER BY clause. The local time values inserted into the column are followed by the values returned by the query.

The time zone in this example is Pacific Time, which has a +8 hour time zone offset.

CREATE TABLE table_1
(column_1 INTEGER, 
column_2 TIME);
INSERT INTO table_1 VALUEs ( 0, TIME'00:00:00.000000'); 
INSERT INTO table_1 VALUEs ( 1, TIME'01:00:00.000000'); 
INSERT INTO table_1 VALUEs ( 2, TIME'02:00:00.000000'); 
INSERT INTO table_1 VALUEs ( 3, TIME'03:00:00.000000'); 
INSERT INTO table_1 VALUEs ( 4, TIME'04:00:00.000000'); 
INSERT INTO table_1 VALUEs ( 5, TIME'05:00:00.000000'); 
INSERT INTO table_1 VALUEs ( 6, TIME'06:00:00.000000'); 
INSERT INTO table_1 VALUEs ( 7, TIME'07:00:00.000000'); 
INSERT INTO table_1 VALUEs ( 8, TIME'08:00:00.000000'); 
INSERT INTO table_1 VALUEs ( 9, TIME'09:00:00.000000'); 
INSERT INTO table_1 VALUEs (10, TIME'10:00:00.000000'); 
INSERT INTO table_1 VALUEs (11, TIME'11:00:00.000000'); 
INSERT INTO table_1 VALUEs (12, TIME'12:00:00.000000'); 
INSERT INTO table_1 VALUEs (13, TIME'13:00:00.000000'); 
INSERT INTO table_1 VALUEs (14, TIME'14:00:00.000000'); 
INSERT INTO table_1 VALUEs (15, TIME'15:00:00.000000'); 
INSERT INTO table_1 VALUEs (16, TIME'16:00:00.000000'); 
INSERT INTO table_1 VALUEs (17, TIME'17:00:00.000000'); 
INSERT INTO table_1 VALUEs (18, TIME'18:00:00.000000'); 
INSERT INTO table_1 VALUEs (19, TIME'19:00:00.000000'); 
INSERT INTO table_1 VALUEs (20, TIME'20:00:00.000000'); 
INSERT INTO table_1 VALUEs (21, TIME'21:00:00.000000'); 
INSERT INTO table_1 VALUEs (22, TIME'22:00:00.000000');
INSERT INTO table_1 VALUEs (23, TIME'23:00:00.000000');

This SELECT statement is used to query the time values in the TIME data type column (column_2).

SELECT * FROM table_1
ORDER BY column_2;

Result:

column_1  column_2
--------  ---------------
16        16:00:00.000000
17        17:00:00.000000
18        18:00:00.000000
19        19:00:00.000000
20        20:00:00.000000
21        21:00:00.000000
22        22:00:00.000000
23        23:00:00.000000

0         00:00:00.000000
1         01:00:00.000000
2         02:00:00.000000
3         03:00:00.000000
4         04:00:00.000000
5         05:00:00.000000
6         06:00:00.000000
7         07:00:00.000000
8         08:00:00.000000
9         09:00:00.000000
10        10:00:00.000000
11        11:00:00.000000
12        12:00:00.000000
13        13:00:00.000000
14        14:00:00.000000
15        15:00:00.000000

Because the sort order is based on UTC time (which is not visible in the column), the time values do not agree with the resulting sort order. Rows 16 through 23 appear at the top of the list because these rows sort at the top of the list according to UTC time.

The UTC equivalents for the returned time values are:

Returned Values                                      UTC Equivalents (local time + 8 hours)

column_1  column_2
--------  -------------
16      16:00:00.000000           00:00:00.000000
17      17:00:00.000000           01:00:00.000000
18      18:00:00.000000           02:00:00.000000
19      19:00:00.000000           03:00:00.000000
20      20:00:00.000000           04:00:00.000000
21      21:00:00.000000           05:00:00.000000
22      22:00:00.000000           06:00:00.000000
23      23:00:00.000000           07:00:00.000000

0       00:00:00.000000           08:00:00.000000
1       01:00:00.000000           09:00:00.000000
2       02:00:00.000000           10:00:00.000000
3       03:00:00.000000           11:00:00.000000
4       04:00:00.000000           12:00:00.000000
5       05:00:00.000000           13:00:00.000000
6       06:00:00.000000           14:00:00.000000
7       07:00:00.000000           15:00:00.000000
8       08:00:00.000000           16:00:00.000000
9       09:00:00.000000           17:00:00.000000
10      10:00:00.000000           18:00:00.000000
11      11:00:00.000000           19:00:00.000000
12      12:00:00.000000           20:00:00.000000
13      13:00:00.000000           21:00:00.000000
14      14:00:00.000000           22:00:00.000000
15      15:00:00.000000           23:00:00.000000

Workarounds for Unexpected Sort Order When Querying DATE

These workarounds can be used to get expected results:
  • Use INTERVAL
  • Use CAST to CHAR or VARCHAR
  • Use TIMESTAMP instead of TIME

Use INTERVAL

SELECT column_1, column_2, column_2 - interval '8' hour
FROM table_1
ORDER BY 3;

Result:

column_1     column_2            (column_2- 8)
--------     --------------      --------------
       0     00:00:00.000000     16:00:00.000000
       1     01:00:00.000000     17:00:00.000000
       2     02:00:00.000000     18:00:00.000000
       3     03:00:00.000000     19:00:00.000000
       4     04:00:00.000000     20:00:00.000000
       5     05:00:00.000000     21:00:00.000000
       6     06:00:00.000000     22:00:00.000000
       7     07:00:00.000000     23:00:00.000000
       8     08:00:00.000000     00:00:00.000000
       9     09:00:00.000000     01:00:00.000000
      10     10:00:00.000000     02:00:00.000000
      11     11:00:00.000000     03:00:00.000000
      12     12:00:00.000000     04:00:00.000000
      13     13:00:00.000000     05:00:00.000000
      14     14:00:00.000000     06:00:00.000000
      15     15:00:00.000000     07:00:00.000000
      16     16:00:00.000000     08:00:00.000000
      17     17:00:00.000000     09:00:00.000000
      18     18:00:00.000000     10:00:00.000000
      19     19:00:00.000000     11:00:00.000000
      20     20:00:00.000000     12:00:00.000000
      21     21:00:00.000000     13:00:00.000000
      22     22:00:00.000000     14:00:00.000000
      23     23:00:00.000000     15:00:00.000000

Use CAST to CHAR or VARCHAR

SELECT column_1, column_2, CAST(column_2 AS CHAR(15))
FROM table_1
ORDER BY 3;

Result:

column_1      column_2     column_2
--------  ------------     ---------------
       0  00:00:00.000000  00:00:00.000000
       1  01:00:00.000000  01:00:00.000000
       2  02:00:00.000000  02:00:00.000000
       3  03:00:00.000000  03:00:00.000000
       4  04:00:00.000000  04:00:00.000000
       5  05:00:00.000000  05:00:00.000000
       6  06:00:00.000000  06:00:00.000000
       7  07:00:00.000000  07:00:00.000000
       8  08:00:00.000000  08:00:00.000000
       9  09:00:00.000000  09:00:00.000000
      10  10:00:00.000000  10:00:00.000000
      11  11:00:00.000000  11:00:00.000000
      12  12:00:00.000000  12:00:00.000000
      13  13:00:00.000000  13:00:00.000000
      14  14:00:00.000000  14:00:00.000000
      15  15:00:00.000000  15:00:00.000000
      16  16:00:00.000000  16:00:00.000000
      17  17:00:00.000000  17:00:00.000000
      18  18:00:00.000000  18:00:00.000000
      19  19:00:00.000000  19:00:00.000000
      20  20:00:00.000000  20:00:00.000000
      21  21:00:00.000000  21:00:00.000000
      22  22:00:00.000000  22:00:00.000000
      23  23:00:00.000000  23:00:00.000000

Use TIMESTAMP instead of TIME

CREATE TABLE table_2
(column_1 INTEGER,
column_2 TIMESTAMP);

INSERT INTO table_2 SELECT * FROM table_1;

SELECT * FROM table_2
ORDER BY column_2;

Result:

column_1   column_2
--------   --------------------------
       0   2011-11-07 00:00:00.000000
       1   2011-11-07 01:00:00.000000
       2   2011-11-07 02:00:00.000000
       3   2011-11-07 03:00:00.000000
       4   2011-11-07 04:00:00.000000
       5   2011-11-07 05:00:00.000000
       6   2011-11-07 06:00:00.000000
       7   2011-11-07 07:00:00.000000
       8   2011-11-07 08:00:00.000000
       9   2011-11-07 09:00:00.000000
      10   2011-11-07 10:00:00.000000
      11   2011-11-07 11:00:00.000000
      12   2011-11-07 12:00:00.000000
      13   2011-11-07 13:00:00.000000
      14   2011-11-07 14:00:00.000000
      15   2011-11-07 15:00:00.000000
      16   2011-11-07 16:00:00.000000
      17   2011-11-07 17:00:00.000000
      18   2011-11-07 18:00:00.000000
      19   2011-11-07 19:00:00.000000
      20   2011-11-07 20:00:00.000000
      21   2011-11-07 21:00:00.000000
      22   2011-11-07 22:00:00.000000
      23   2011-11-07 23:00:00.000000